Need variable for sheet name 1

[renigar]

I'm very new to VBA but have gotten some good info from this forum. Thanks to all. My problem is in this section of code. I'm creating a single sheet workbook and naming it today's date. Then I copy a single sheet from an open workbook and want to paste it in the new workbook. I can't figure out how to activate the new workbook to facilitate the paste. See the line with asterisks. I need a variable to refer to the new workbook sheet since the file name will change every day. A book I have says you can use Workbook.Name to return the file name of a workbook but, I can't seem to figure it out. I'd appreciate any help. Thanks



' Copies DataLog sheet into new workbook and saves it with
' todays date as it's name
'Sheets("DataLog&quot.Select
Workbooks.Add(xlWBATWorksheet).Activate ' Create a Workbook with 1 sheet
ActiveWorkbook.SaveAs ("C:\Data\TankChlor\" & Format(TodaysDate, "d-mmm-yy&quot & ".Xls&quot
' Go to original sheet and copy
Windows("TankChlor_Test1.xls&quot.Activate
'Range("B6&quot.Select
ActiveSheet.Unprotect ("unprotect&quot
Cells.Select
Selection.Copy
' How do I activate the new workbook to paste copied sheet?
*** Windows(new sheet name).Activate ***
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'Range("B6&quot.Select
Application.CutCopyMode = False

 

[ilses]

How about:

Dim strNewName As String

strNewName = "C:\Data\TankChlor\" & Format(TodaysDate, "d-mmm-yy") & ".Xls"

etc

Workbooks(strNewName).Activate

ilses

 

[renigar]

Thanks for your reply. I had tried that already and got this error message - Subscript out of range (Error 9) - I don't know what it means. The variable works fine on this line:

ActiveWorkbook.SaveAs (strNewName)

But not on the line in question. I also tried:

Dim strNewSheetName As String

strNewSheetName = Format(TodaysDate, "d-mmm-yy&quot

This returns the same error. For the moment I'm stuck.
renigar

 

[renigar]

I found a way that works for me by the following:


' I forgot to DIM this but it worked anyway
strNewWBName = Format(TodaysDate, "d-mmm-yy&quot & ".Xls"

Workbooks(strNewWBName).Activate

renigar

 

[acron]

If you select the sheet you want to copy in the original workbook (TankChlor_Test1.xls), and use the following code it should do what I think you want :

Sub CoypToNewBook
Sheets("DataLog&quot.Copy
ActiveWorkbook.SaveAs ("C:\Data\TankChlor\" & Format(Now(), "d-mmm-yy&quot & ".Xls&quot
End Sub

What that code does is to make a copy of the activesheet in a NEW workbook and saves it with the days date. That way you do not need to copy and paste any data.

 

[renigar]

Thanks acron,
That saved me a few lines. I liked it alot.

renigar