Excel VBA - Pull data from external books and paste into the next blank row in multiple tabs

[aanthony16]

I'm working on automating a workbook in Excel, and I'm running into a few issues with my VBA code. I'd paste it here, but I've been through so many iterations, it's pretty unusable.
The goal is to have the active book 'grab' the data out of several workbooks containing raw data when it is opened, and put the copied data into a few tabs that can be used to populate various charts on a dashboard tab. Each workbook containing raw data should go into its own separate tab within the active workbook. Broken into steps, I am thinking I need the below process to occur:

• Open Active Book
• Open hidden tab 'Sheet1'
• Open raw data book 1(e.g. c:\Raw Data.xls)
• Copy data from specified location (e.g. [Raw Data.xls]Sheet1!$A$3:$AE$64) in the raw data book 1
• Paste copied data into Active Book, into specific worksheet, into first empty row (e.g. [Active Book.xls]Sheet1!first empty row)
• Hide tab 'Sheet1' in Active Book
• Close raw data book 1
• Repeat process using raw data book 2 and sheet2 of Active Book
• Repeat process using raw data book 3 and sheet3 of Active Book
• Only after data is populated into the destination tabs (Sheet1, Sheet2, Sheet3 in the Active Book), can the user interact (click into cells, change tabs, etc) with the workbook

I know this is simple - I'm getting frustrated, as I'm a newbie and the syntax (and multiple variations of syntax) is really throwing me for a loop. Any help would be greatly appreciated. Many thanks in advance!!

 

[Smitty]

Welcome to the Board!

I'd record a macro doing what you want. You can post the resulting code back here and someone will help you clean it up.

You can also post your existing code for us to look at, just point out where it's working/not.

 

[aanthony16]

Thanks! Following is a recording as requested. Sorry for how terrible I am at this, hoping to learn with help from the experts here.

Again, I need it to open the other book (which I did, but do not see recorded) and select a full range of data which will be a variable array, and then also look for a blank row as the insertion point in Book1, sheet2. Will also need it to loop through this process to do the same using a second source of data to be pasted into Book1, sheet3.

I'm decent at modifying existing code, its just the joining of these three different actions (open and close a new book, select a variable range to copy/paste, loop process) that I'm having trouble with, and can't find anything existing that meets my needs (I have looked through MANY posts in several forums). I know this is basic stuff, so should be a good place for me to learn.

Thanks again!!

Sub Macro1()'
' Macro1 Macro
'


'
    Sheets("Sheet1").Select
    Sheets("Sheet2").Visible = True
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Book1").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Visible = False
End Sub

 

[Smitty]

The recorder should have captured you opening the other workbook, so I'd try again.

As for defining the last row in the destination workbook you can use a variable:

Dim lr as long
Dim ws as Worksheet

  set ws = Sheets("Sheet1")

  lr = ws.Cells(Rows.Count,"A").End(xlUp).Row

  ws.Cells(lr,"A").PasteSpecial xlPasteValues