[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2007 macro to copy tab to another workbook

Posted on 2011-04-20
6
Medium Priority
?
307 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!
0
Comment
Question by:TelMaco
  • 4
  • 2
6 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35438162
If the Destination workbook is opened then use this

Sheets("report").Copy After:=Workbooks("Report YYYY_MM").Sheets(Sheets.Count)

Open in new window


Sid
0
 

Author Comment

by:TelMaco
ID: 35486115
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!

0
 

Author Comment

by:TelMaco
ID: 35486504
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

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:TelMaco
ID: 35486666
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

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35489964
Kool :)

So is it Sorted?

Sid
0
 

Author Comment

by:TelMaco
ID: 35492224
Yup all good thanks!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Screencast - Getting to Know the Pipeline

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question