CODEWITHSUNDEEP
excelvba
SVTFX4
SVTFX4

Reputation: 49

Trying to combine multiple workbooks in different folders into a master one

I'm trying to pull data from multiple workbooks, and combine the data into one dataset.

I started pulling data from one, and it worked fine. When I tried to add the 2nd workbook it's pulling the data but just writing over the first set of data.

I'll eventually be doing this 6 different times for files located in different file paths and not in the same folder.

Each work book has the same named tabs, and headers are exactly the same.

A1:AA1 are the headers. - Exactly the same on source files and master file.

I'm trying to pull the data and paste below the header on the master worksheet, and keep pasting below as i pull data from each workbook.

I'm looking for a solution to change the "31" to paste to next unused row as the 31 will change as data is entered on the source files.

wbPrior2.Sheets("wsPrior2").Range("A2:AA" & Prior2LastRow).copy Destination:=ThisWorkbook.Sheets("wsCurrent").Cells(31, 1)

Option Explicit

Sub RectangleRoundedCorners3_Click()
    ' clear current data
    Sheets("wsCurrent").Rows("2:" & Sheets("wsCurrent").Rows.Count).ClearContents
    
    ' open First File to Combine
    Dim fileNameFullPath As String
    fileNameFullPath = "C:\Filelocationpath\wbPrior.xlsx"
    Workbooks.Open Filename:=fileNameFullPath, ReadOnly:=True
    ' ----- copy file. after opening workbook, it becomes an active workbook
    Dim wbPrior As Workbook
    Set wbPrior = ActiveWorkbook
    ' --- get LastRow
    Dim PriorLastRow As Integer
    ' -- wsPrior
    PriorLastRow = wbPrior.Sheets("wsPrior").Cells(Rows.Count, 1).End(xlUp).Row
    ' --- copy wsPrior to wsCurrent
    wbPrior.Sheets("wsPrior").Range("A2:AA" & PriorLastRow).copy Destination:=ThisWorkbook.Sheets("wsCurrent").Cells(2, 1)
    ' --- close wbPrior
    wbPrior.Close
    
    'Second Source File Data Pull
    ' --- open "wbPrior2.xlsx"
    Dim fileNameFullPath2 As String
    fileNameFullPath2 = "C:\Filelocationpath2\wbPrior2.xlsx"
    Workbooks.Open Filename:=fileNameFullPath2, ReadOnly:=True
    ' ----- copy file. after opening workbook, it becomes an active workbook
    Dim wbPrior2 As Workbook
    Set wbPrior2 = ActiveWorkbook
    ' --- get LastRow
    Dim Prior2LastRow As Integer
    ' -- wsPrior2
    Prior2LastRow = wbPrior2.Sheets("wsPrior2").Cells(Rows.Count, 1).End(xlUp).Row
    ' --- copy wsPrior to wsCurrent
    wbPrior2.Sheets("wsPrior2").Range("A2:AA" & Prior2LastRow).copy Destination:=ThisWorkbook.Sheets("wsCurrent").Cells(31, 1)
    ' --- close wbPrior
    wbPrior2.Close
    
    
End Sub

Upvotes: 1

Views: 75

Answers (1)

Seven Up
Seven Up

Reputation: 98

If there is a column that is always filled with a value (e.g. an ID-column; in the example I use column "A") then you could use nextRow instead of 31.

dim next Row as long
nextRow = ThisWorkbook.Sheets("wsCurrent").Cells(1,1).End(xlDown).Row +1

or

dim next Row as long
nextRow = ThisWorkbook.Sheets("wsCurrent").Cells(Rows.Count, 1).End(xlUp).Row + 1

this would be very similar to the way you're already doing it with e.g.

Prior2LastRow = wbPrior2.Sheets("wsPrior2").Cells(Rows.Count, 1).End(xlUp).Row

Furthermore, if you want to avoid to hard-code all 6 files you could also use this function to sequentially pick the files.

Public Function f_FiledialogChooseData() As Variant
Dim fd As FileDialog
f_FiledialogChooseData= 0
Set fd = Application.FileDialog(msoFileDialogFilePicker)
If fd.Show = True Then
    f_FiledialogChooseData= fd.SelectedItems(1)
Else
    Debug.Print "The user pressed >>cancel<<"
End If
Set fd = Nothing 
End Function

combined with

fileNameFullPath = f_FiledialogChooseData()

Upvotes: 1

Related Questions