Combining Excel workbooks

February 13, 2011 at 04:54:21
Specs: Windows XP
I have 2 Excel workbooks formatted exactly the same: wb1 is weekly data and wb 2 is YTD data. I want a macro that will add the weekly data (without line 1 headers) to the end of wb2. Should be simple but I can't figure it out. The workbook names and sheet names are fixed and do not change.

Thanks for any help.


See More: Combining Excel workbooks

Report •


#1
February 13, 2011 at 20:08:47
You didn't provide any information related to workbook names or sheet names/numbers, but in concept it should look something like the code below.

Keep in mind that YTD Data.xls (in my example) must be open because you can't write to a closed workbook via VBA.


Sub AddWeekly()
'Determine Last Row in Weekly Data.xls
 last_SrcRow = _
  Workbooks("Weekly Data.xls").Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Determine Last Row in YTD Data.xls
 last_DstRow = _
  Workbooks("YTD Data.xls").Sheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Range
  Workbooks("Weekly Data.xls").Sheets(1).Range("A2:A" & last_SrcRow).EntireRow.Copy _
    Destination:=Workbooks("YTD Data.xls").Sheets(1).Range("A" & last_DstRow)
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 15, 2011 at 03:56:38
Thanks DerbyDad03. This is just what I needed to get over the hump. I have spent a lot of time recently in getting my weekly data to process correctly before combining into a YTD file: I perform 5 different VLOOKUPs of each line of input data to build a detail record and then use a CONDITIONAL FORMAT to color code the line based upon certain criteria. I then have to split the data back up into 15 different spreadsheets to send out to various people (FERPA Regulations prevent me from just using a Filter and sending all data to everyone). I found the VLOOKUP did not always find a matching record in my source data - until I moved the source workbooks to a separate worksheet WITHIN the same workbook. It did not matter if I had the source files (workbooks) open or closed. As soon as I moved them all into the same workbook, the formulas worked perfectly. (I didn't mention I am doing all of this on a MAC.) Because of all the touch points and potential for error in building the weekly data, when I inherited the report it would take over 8 hours to build - lots of manual entry and corrections. I now have the whole process reduced to less than 1 hour - this backend piece of building the weekly data, combining it with the YTD data, formatting and splitting it back up to the 15 individual spreadsheets now takes about 2 minutes. Of course I still take another hour going back to verify the data and formulas are correct! So far, I am very satisfied - and the users are now getting their data on Mondays instead of Tuesday or Wednesday!

Again, thanks for your Help!

Rick


Report •

#3
February 15, 2011 at 06:14:11
I'm glad I could be of assitance.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question