Save Worksheet To Master Workbook

[Bobzter100]

Hi
Can I create a macro to save a weekly Worksheet to a Master Workbook? The
Master is held on a Network Drive and the weekly Worksheet has a unique tab
name, e.g each week the new worksheet will be named Week 1, Week 2..etc for
52 weeks of the year so at the end of the year there will be 52 individual
worsheets within the Master Workbook.
Thanks in advance
Bobzter

 

[GSnyder]

I'm not 100% sure I understand exactly what you need, but here's what I'm
thinking. You want some VBA which will open up a master workbook, copy the
current worksheet into that workbook, and then save and close the master
workbook. So, for example, if you're working on the Week 17 sheet, it will
automatically copy Week 17 into the master workbook.

Let me know if I didn't understand correctly, but the following code does
that. You'll need to change the name and location of your Master workbook.

Sub CopyActiveSheet()

Dim strWeekly As String

strWeekly = ActiveWorkbook.Name

Workbooks.Open Filename:="D:\my documents\Master.xlsb"
Windows(strWeekly).Activate
ActiveSheet.Copy
After:=Workbooks("Master.xlsb").Sheets(Workbooks("Master.xlsb").Sheets.Count)
Windows("Master.xlsb").Activate
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

 

[Bobzter100]

Hi
thanks for your reply.
I'm getting a syntax error on the following line (error highlighted on
After:=Workbooks)

After:=Workbooks("Master.xlsb").Sheets(Workbooks("Master.xlsb").Sheets.Count)

This is the altered code I'm using

Sub CopyActiveSheet()

Dim strWeekly As String

strWeekly = ActiveWorkbook.Name

Workbooks.Open Filename:="D:\my documents\Master.xlsb"
Windows(strWeekly).Activate
ActiveSheet.Copy
After:=Workbooks("Master.xlsb").Sheets(Workbooks("Master.xlsb").Sheets.Count)
Windows("Master.xlsb").Activate
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Best Regards

bobzter

 

[Bobzter100]

Hi

Messed around with the code and have copied my final below. Sorry - i still
get the same failure. I'm testing this on Excel 2007 but will run it on Excel
2003. Please note that the same failure comes up on both 2003 and 2007.

Sub SaveTester()
'
' SaveTester Macro
Dim strWeekly As String

strWeekly = ActiveWorkbook.Name

Workbooks.Open Filename:="L:\Testbook.xlsx"
Windows(strWeekly).Activate
ActiveSheet.Copy
After =
Workbooks("Testbook.xlsx").Sheets(Workbooks("Testbook.xlsx").Sheets.Count)
Windows("Testbook.xlsx").Activate
ActiveWorkbook.Save
ActiveWindow.Close


End Sub

thank you for the time, again.

Regards
Bobzter

 

[Bobzter100]

Hi

Of course, you'll be asking what fool am I!
I corrected the code by moving Activesheet.Copy After... onto the same line.
I had taken a copy from your post and it had split the single line into 2.
Many thanks - the code works perfectly!
Cheers
Bobzter

 

[GSnyder]

Bob,

Glad you got it to work. Actually, when I pasted it, I thought about that
and wondered if the word wrap would get you. Sorry about that!

Enjoy! If you could hit "Yes" on the post to mark this one as answered that
would be helpful as well.

Have a great one!