Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2007
    Location
    Malaysia
    Posts
    3

    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?

    Any help is MUCH appreciated. Thanks!

    fwawergurl16

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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
    Last edited by shades; 07-20-07 at 11:55.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jul 2007
    Location
    Malaysia
    Posts
    3

    Red face Some samples for testing

    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
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    I have worked in the Media dept for seven years. I know the feeling. I will try to look at it today.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    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)?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Jul 2007
    Location
    Malaysia
    Posts
    3
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •