VBA code to copy/paste worksheet to new workbook

[traub86]

Hi,

I am looking to copy a worksheet in workbook1 to a named workbook before all other tabs AND name that tab with today's date. Any help would be much appreciated. Thanks.

 

[Fluff]

Make sure both workbooks are open & try this

Sub Copysht()

    Workbooks("[COLOR=#ff0000]Book1.xlsm[/COLOR]").Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Copy before:=Workbooks("[COLOR=#ff0000]Book2.xlsm[/COLOR]").Sheets(1)
    Workbooks("[COLOR=#ff0000]Book2.xlsm[/COLOR]").Sheets(1).Name = Format(Date, "DD-MM-YYYY")

End Sub

Change the workbook names in red & the sheet name in blue to match your names.

 

[traub86]

I get an error that says "subscript out of range" and highlights the top line. Also is there a way I can save to workbook2 without it being open?

 

[Fluff]

Were both workbooks Open? & did you change the names of the workbooks & sheet?
As for

Also is there a way I can save to workbook2 without it being open?

Not to my knowledge.

 

[traub86]

Yes they were both open. here is the code with the named workbooks and sheets:

Sub Copysht()
    Workbooks("FINAL_JUMP MANIFEST_FINAL.xlsm").Sheets("DZ LOG").Copy before:=Workbooks("DO NOT DELETE_MASTER DZ LOG.xlsm").Sheets(1)
    Workbooks("DO NOT DELETE_MASTER DZ LOG.xlsm").Sheets(1).Name = Format(Date, "DD-MM-YYYY")
End Sub

 

[Fluff]

Are they both .xlsm macro enabled workbooks?

 

[traub86]

The destination workbook "DO NOT DELETE_MASTER DZ LOG" is NOT macro enabled...that might be it. Let me switch that over to see if that works...

 

[traub86]

That worked perfectly! If I wanted to delete some macro buttons in the destination workbook could I use a code for that? If so how?

 

[traub86]

Yes they were both open. here is the code with the named workbooks and sheets:

Sub Copysht()
    Workbooks("FINAL_JUMP MANIFEST_FINAL.xlsm").Sheets("DZ LOG").Copy before:=Workbooks("DO NOT DELETE_MASTER DZ LOG.xlsm").Sheets(1)
    Workbooks("DO NOT DELETE_MASTER DZ LOG.xlsm").Sheets(1).Name = Format(Date, "DD-MM-YYYY")
End Sub

If I want the name of the new sheet to have the date and a custom name suck as "Chalk 1" would I just put .Name = Format (Date, "DD-MM-YYYY") + Format (Text, "Chalk 1")?

 

[Fluff]

As it's just text you don't need the 2nd format

.Name = Format(Date, "DD-MM-YYYY") + " Chalk 1"