Copy Paste using macro involving 6 different workbooks

fwawergurl16 · #1 (**permalink**) 07-19-07, 23:45

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?

Any help is MUCH appreciated. Thanks!

fwawergurl16

shades · #2 (**permalink**) 07-20-07, 10:50

Howdy, and welcome to the board.

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.

Code:

Sub PasteList()
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")
    myRange.Copy
    
    '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 _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'Goes back to original worksheet
    Sheets("Work").Range("G65536").End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
Application.ScreenUpdating = True

End Sub

fwawergurl16 · #3 (**permalink**) 07-22-07, 22:29

Hi Rich,

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.

Regards,
fwawergurl16

shades · #4 (**permalink**) 07-23-07, 09:12

I have worked in the Media dept for seven years. I know the feeling. I will try to look at it today.

shades · #5 (**permalink**) 07-23-07, 09:16

Will you always copy all data from each workbook into the master workbook? In other words, will it always be new data (from the last time you copied)?

fwawergurl16 · #6 (**permalink**) 07-23-07, 22:09

Hi Rich,

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.

Thanks loads!

Internet Services

Web Development

Digital Marketing

Consumer Tech