Sheetname

[andrewrgd]

I am copying data from workbook A into a new sheet in workbook B. As part of this process, I need some code to copy the name of the new sheet (ie. sheet1) into a list in workbook B sheet2 column A which is labelled as (sheetlist). There will be other sheetnames in this list so I need the sheetname to be inserted at the next available point.


Any help would be appreciated!

 

[philR]

How you get the name of the sheet will depend on how you create / reference it in your code. But, if you have the name of your new sheet in temp for example, the this code should work:

Set sh1 = Sheets("SheetList")
mylastrow = sh1.Range("A65536").End(xlUp).Row + 1
sh1.Cells(mylastrow, 1) = temp

If you are not sure how to get the name of the new sheet into temp, then post the code you have so far.

Hope this helps

 

[andrewrgd]

From workbookA I'm using:

Sheets.Add 'added
ActiveSheet.Name = "workbookA " & Format(Now(), "dd mm yyyy")

I then need this sheetname to be added to the sheetlist.

Thanks for your help!

 

[philR]

Well, you could use:

Sheets.Add 'added
temp= "workbookA " & Format(Now(), "dd mm yyyy")

ActiveSheet.Name = temp
Sheets("SheetList").Cells(Sheets("SheetList").Range("A65536").End(xlUp).Row + 1 , 1) = temp

 

[andrewrgd]

Hi PhilR,

I'm getting the following error:

Runtime 1004
Application defined or object defined error.

Doesn't look like anything is being stored in temp?

Any ideas

 

[philR]

The only reason for the error would be if your macro is trying to create more than one sheet with ths same name. You have used today's date as the unique part of the new sheet name. Therefore if you run it twice without deleting the created sheet you will get an error. To get around this you could use a count variable or the time as well in the sheet name.

 

[andrewrgd]

Hi PhilR,

Even when I delete the sheet that's been created it doesn't seem to like it?

I've included the module below:

Sub CopyToMasterFile()
Dim Wb As Workbook
Application.ScreenUpdating = False

On Error Resume Next
Set Wb = Workbooks("e:\MasterFile.xls")
If Wb Is Nothing Then '(MasterFile is not open so open it.)
Workbooks.Open Filename:="e:\MasterFile.xls"
Set Wb = Nothing
On Error GoTo 0
Else '(MasterFile is already open so activate it.)
Windows("e:\MasterFile").Activate
Set Wb = Nothing
On Error GoTo 0
End If


Windows("Test").Activate

Range("A1:M26").Select
Selection.Copy
Windows("MasterFile.xls").Activate

Sheets.Add 'added
ActiveSheet.Name = "Greenwich " & Format(Now(), "dd mm yyyy")
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveSheet.Name = Temp
Sheets("SheetList").Cells(Sheets("SheetList").Range("A65536").End(xlUp).Row + 1, 1) = Temp

ActiveWindow.Close
Range("A1").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[philR]

Is your worksheet called "sheetlist" or "sheet list"

 

[andrewrgd]

Hi PhilR

When I copy the data over the new sheet that is added = Greenwich ddmmyyyy. The SheetList is a labelled range listing those sheets.

Thanks

 

[philR]

Sorry, I have mis-read your original post. I thought you had a worksheet called "SheetList". OK, the code should be:

Sheets.Add 'added

temp = "Greenwichy " & Format(Now(), "dd mm yyyy")
ActiveSheet.Name = temp

mylastrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1
Sheets("Sheet2").Cells(mylastrow, 1) = temp
'ActiveWorkbook.Names.Add Name:="SheetList", RefersToR1C1:="=Sheet2!R1C1:R" & mylastrow & "C1"

Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.Save
If you really want to keep the range name "SheetList" then un-comment the 6th line.