Save New Worksheet as String using names of information from sourcesheet

**vlr516** · 10-02-2017, 10:28 AM

Hi,

I am trying to save a new worksheet using a value in my sourcesheet and the tab name of the sourcesheet. My code works well if I just use the value, but as soon as I add the sourcesheet name code, I get an error. Can you help?

[code]
Sub NameNewWorksheet

Dim Name as String
Dim sourcesheet As Variant

activesheet.copy
Name = sourcesheet.range("A1").Value & " " & sourcesheet.Name 'sourcesheet.Name is where my code isn't working. If I just do the A1 value it works
ActiveWorkbook.SaveAs Filename:=Name

End Sub
[\code]

**Arkadi** · 10-02-2017, 10:40 AM

"sourcesheet" isn't a sheet, it is a variant with no value assigned to it...

I assume sourcesheet is supposed to be the original worksheet you are copying from?

**Arkadi** · 10-02-2017, 10:43 AM

Since it is the activesheet prior to the copy, no need for the extra variable, just set the Name variable before you copy?

Please Login or Register  to view this content.

**Logit** · 10-02-2017, 10:45 AM

.
Another :

Please Login or Register  to view this content.

**DarkF1ame** · 10-02-2017, 10:46 AM

Hello vlr516,

If you are just looking to save the same file as a different name utilizing cell data then I would try this adaptation of your current code:

Please Login or Register  to view this content.

It doesn't have the copy function in there, so I don't know if you are wanting to keep each version of it open simultaneously or not. If so you could make the workbook have a rename function built off the Workbook Open trigger. Just a thought.

Hope this helps,

DarkF1ame

**vlr516** · 10-02-2017, 11:21 AM

DarkF1ame,

I am trying to save a version within the original workbook and have a copy of just that tab because it will need to be uploaded. Is my code appropriate for what I want to do?

Thanks so much.

**DarkF1ame** · 10-02-2017, 06:54 PM

vlr516,

This should address that. In my version of the test workbook it only copies the current sheet and uses the sheet name and the value in A1 for the name of the new macro-enabled workbook. When you run the macro it won't check for another sheet with the same name, so you can either add that or deal with overwriting issues. Hope it helps:

Please Login or Register  to view this content.

DarkF1ame

Save New Worksheet as String using names of information from sourcesheet

LinkBack

Thread Tools

Rate This Thread

Display

Save New Worksheet as String using names of information from sourcesheet

Re: Save New Worksheet as String using names of information from sourcesheet

Re: Save New Worksheet as String using names of information from sourcesheet

Re: Save New Worksheet as String using names of information from sourcesheet

Re: Save New Worksheet as String using names of information from sourcesheet

Re: Save New Worksheet as String using names of information from sourcesheet

Re: Save New Worksheet as String using names of information from sourcesheet

Thread Information

Users Browsing this Thread

Similar Threads

Pulling information from one main worksheet and moving certain information to multiple

[SOLVED] Output in same sourcesheet

[SOLVED] Attachments : Save and Rename using Sublect string or File name string

[SOLVED] Worksheet Names macro that pastes names as hyperlinks

Pulling out single names from a string of names

Find string and save...insert another string

How to declare variable names depending on worksheet names

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions