Unanswered: Copy Paste using macro involving 6 different workbooks
Hi, im an intern with a media company, but im dealing with the IT side of things. Im asked to combined all the data from different Excel workbooks using macro into ONE workbook, but Im VERY new to VBA.
This is what I'm thinking that I need to do (after opening all workbooks involved using macro as well- able to do this part d):
1. select all info from non-active
2. copy all selected
3. find last line in active workbook
4. paste copied onto active workbook after the workbook's last line (last line +1)
5. save non-active & close saved workbook (active workbook remains opened)
6. loop process
Are there any VBA experts who would know how to get this done? Esp for Step 1,3,4, & 6?
Without any other information, you could at least get started with this. The following copies from one worksheet to another and pastes at the bottom of the list. All you need to do is add the referenced workbook at appropriate places.
Dim NextRow As Long
Dim myRange As Range
Application.ScreenUpdating = False
'Copies list of questions below current list (column F)
Set myRange = Sheets("List").Range("K3:K10")
'Finds last row in column F
NextRow = Sheets("Work").Range("F65536").End(xlUp).Row
'Offsets down one row for next empty cell
Cells(NextRow + 1, 6).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
'Goes back to original worksheet
Application.ScreenUpdating = True
Last edited by shades; 07-20-07 at 11:55.
old, slow, and confused
but at least I'm inconsistent!
Thank u very much for the help. I'm still a little confused with the codes . So sorry to bother u . I've tried the codes & altered wherever needed, but somehow, I still couldn't get the results as wanted.
Attached with this reply are the sample copies of the work tht I'm to copy & paste. 'Cinema' data will be copied & pasted into the 'Magazine' sheet after the last line , A37, of the 'Magazine' workbook. All data will be stored in different workbooks & the aim is to combine all the workbooks into 1 so that it'll be easier to pivot tables for all media types. Each data has different amount of rows, but same amount of columns (all from A to AA).
I hope u can shed some light...it's pretty frustrating when there's no one who can help me in this office (since none does programming in VBA..all media ppl) . Thank u very much for all the help.
Well, no, there aint any 'master' workbook so to say. Each time, there will definitely be 6 workbooks of different media. As long as all 6 are combined into 1 for easier pivoting of tables, then it's good. The main point is to make all 6 come into ONE doc.
So each time when the data from all 6 workbook are combined, it just forms 1 doc of the most recent. No master involved with new data coming in every time.