Creating Working Archive Document

[red_jon]

Hi, im pretty new to excel and vba so please bare with me.

I want to create an archive workbook where I can keep adding tabs every month for reporting purposes.

I only want to copy one particular tab every month from my master workbook into the archive document. This being named as the current month i.e. "January" "February" "March" and so on....

Can anybody guide me to a way of achieving this? I been searching everywhere for the solution.

Note: Having both documents open at the same time is not a problem but would prefer it not to be.

Your help would be much appreciated.

Thanks,</SPAN>

 

[Andrew Poulsom]

Welcome to MrExcel.

To copy a worksheet into another workbook both source and target workbooks must be open. With the master workbook open, turn on the macro recorder, open the archive workbook and copy the worksheet. That should give you some VBA code that you can adapt.

 

[red_jon]

Hi Andrew,

Thankyou for your rapid response.

I have tried this method, however it overwrites the workbook everytime. I want the ability to keep adding worksheets to the archive workbook month on month without deleting. I have tried many methods and hit a brick wall..my minds gone blank.

Thanks,

 

[Andrew Poulsom]

What do you mean by "overwrites the workbook everytime"? Copying a worksheet doesn't overwrite anything.

 

[red_jon]

Example:<o></o>
<o> </o>
Its month January.....My master workbook has a sheet called "Summary Report". I want to then copy this particular sheet into a saved workbook called "Archive.xls" in 'My Documents' directory. The macro method works the first time but when it comes to February and I run the macro it removes January's report and replaces it with February's.<o></o>
<o> </o>
I want to keep January's report and then add February's report in the next sheet and so on...<o></o>
<o> </o>
I hope this has made things clearer.<o></o>

 

[Andrew Poulsom]

If you copy a worksheet whose name already exists in the target workbook Excel appends a number to it. For example if you copy Sheet1 to a new workbook then copy it again to that workbook the second copy will be named Sheet1 (2). Isn't that what you are finding?

 

[red_jon]

No, for some reason it doesn't continuously keep adding new sheets which is what I need it to do. Do you have any code that is similar to what im looking for?<o></o>

 

[Andrew Poulsom]

What VBA code do you currently have?

 

[red_jon]

Hi Andrew,

With a bit of playing around this is what I currently have. This seems to be doing what I intended but I would like not to have both workbooks open at the same time.

Sub copy()
Application.ScreenUpdating = False
    Sheets("Summary Report").Select
    Sheets("Summary Report").copy Before:=Workbooks("archive.xlsm").Sheets(1)
    ActiveSheet.Buttons.Delete
    ActiveSheet.Name = Format(Date, "MMMM")
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
End Sub

Is it possible to open the archive.xlsm in the background, save the intended sheet and then close by using Application.ScreenUpdating? If there is, could you point me in the correct direction?

Thanks,

 

[Andrew Poulsom]

As i said before you can't do it without having both workbooks open. You could open and close the archive workbook in your current code.

By the way it's not a good idea to give a procedure the same name as a VBA property/method (ie copy).