We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel 2007 macro to copy tab to another workbook

TelMaco
TelMaco asked
on
Medium Priority
342 Views
Last Modified: 2012-05-11
Hi,

I have a semi-automated report which I'm soooo close to making fully automated, it's just down to saving the file correctly, and here's where I need some advice.

I have a file called Template.xlsm, which is opened daily via task manager.  It uses a macro to auto-update data from a few sources, & format a report.  Then it renames the report tab to the day number of the month.

What I want to do now is the following:
Copy the report tab to the end of an existing monthly file located on a remote server \\host\folder ( I have this mapped as well x:\folder)
The report folder already contains the base information and monthly file, I just need the tab added at the end.

The existing reports are named "Report YYYY_MM.xlsm"

How can I get the macro to copy that tab to the correct monthly file?

The idea is that I'll end up with a file for each month that has 28-31 numbered tabs, plus some summary stuff at the beginning of the workbook that was added ahead of time

Thanks!
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I'll have to open the destination workbook 1st then.  That should be ok.

The destination file will change depending on the current month, if it was static the command would be:

    Workbooks.Open Filename:= _
        "I:\Daily\Orders 2011_04.xlsx" _
        , UpdateLinks:=3

But the 2011_04 will change next month to 2011_05

How can I write the VBA to go open the correct file depending on the date?

I'll need the same thing when copying the tab.  Your suggestion:
Sheets("report").Copy After:=Workbooks("Report YYYY_MM").Sheets(Sheets.Count)
won't quite work, b/c the YYYY_MM will change.

Thanks!

Author

Commented:
Ok, I got it to open the correct workbook, but I still can't get the tab copied over.

I've tested this:
 Sheets("" & dy).Select
And it works on it's own
Also tested this:  
 Workbooks.Open Filename:= _
        "I:\Daily\2011_" & mnth & ".xlsx"
Which works on it's own too

But when I try to use them together to copy the tab, it fails
Sheets("" & dy).Copy After:=Workbooks("2011_" & mnth & ".xlsx").Sheets(Sheets.Count)

Suggestions?

'renames the tab
Dim dy
dy = Day(Now())
Worksheets("report").Name = dy

'finds the monthly report workbook
Dim mnth
If Month(Now()) < 10 Then mnth = "0" & Month(Now()) Else mnth = Month(Now())
    Workbooks.Open Filename:= _
        "I:\Daily\2011_" & mnth & ".xlsx"
        
'swtiches back to the correct workbook
    Windows("Daily Template.xlsm").Activate

'copies the tab
'THIS IS WHERE IT FAILS:
Sheets("" & dy).Copy After:=Workbooks("2011_" & mnth & ".xlsx").Sheets(Sheets.Count)

Open in new window

Author

Commented:
nailed it

Thanks for getting me on the right track though!

I changed your suggestion of Sheets(Sheets.Count) to Sheets(Workbooks("filename").Worksheets.Count)

This fixed the error, then I added in the bit I wrote to calculate the actual file name based on the date.
I only wrote code to figure out the day and month, it'll be 2011 for a while ( ;
But I think I can adjust the year when needed


See below:


Dim dy
dy = Day(Now())
Worksheets("report").Name = dy

'finds the monthly report workbook
Dim mnth
If Month(Now()) < 10 Then mnth = "0" & Month(Now()) Else mnth = Month(Now())
    Workbooks.Open Filename:= _
        "I:\Daily\2011_" & mnth & ".xlsx"
        
'copies the tab

    Sheets("" & dy).Copy After:=Workbooks("2011_" & mnth & ".xlsx"). _
        Sheets(Workbooks("2011_" & mnth & ".xlsx").Worksheets.Count)

Open in new window

Kool :)

So is it Sorted?

Sid

Author

Commented:
Yup all good thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.