Hello,
I have a mastersheet that needs to pull data from about 10 spreadsheets and insert the rows underneath each other. However, it is not working out as I think it should. It is running the code, but it is pasting the results on top of each other and starting from column S instead of column B.
the code is as following:
I hope someone can help me with what i'm doing wrong.
Thanks,
/Calinora
I have a mastersheet that needs to pull data from about 10 spreadsheets and insert the rows underneath each other. However, it is not working out as I think it should. It is running the code, but it is pasting the results on top of each other and starting from column S instead of column B.
the code is as following:
Code:
Sub CopyDataBetweenWorkbooks()
Dim wbSource As Workbook
Dim shTarget As Worksheet
Dim shSource As Worksheet
Dim strFilePath As String
Dim strPath As String
' Initialize some variables and
' get the folder path that has the files
Set shTarget = ThisWorkbook.Sheets("Master")
strPath = GetPath
If Not strPath = vbNullString Then
' Get all the files from the folder
strfile = Dir$(strPath & "*.xlsm", vbNormal)
Do While Not strfile = vbNullString
' Open the file and get the source sheet
Set wbSource = Workbooks.Open(strPath & strfile)
Set shSource = wbSource.Sheets("Data")
'Copy the data
Call CopyData(shSource, shTarget)
'Close the workbook and move to the next file.
wbSource.Save
wbSource.Close False
strfile = Dir$()
Loop
End If
End Sub
' Procedure to copy the data.
Sub CopyData(ByRef shSource As Worksheet, shTarget As Worksheet)
Const strRANGE_ADDRESS As String = "B18:N32"
Dim lRow As Long
'Determine the last Row.
lRow = shTarget.Cells.Find("18", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
'Copy the data.
shSource.Range(strRANGE_ADDRESS).Copy
shTarget.Cells(18, lRow).PasteSpecial xlPasteValuesAndNumberFormats
' Reset the clipboard.
Application.CutCopyMode = xlCopy
End Sub
' Fucntion to get the folder path
Function GetPath() As String
With Application.FileDialog(msoFileDialogFolderPicker)
.ButtonName = "Select a folder"
.Title = "Folder Picker"
.AllowMultiSelect = False
'Get the folder if the user does not hot cancel
If .Show Then GetPath = .SelectedItems(1) & "\"
End With
End Function
I hope someone can help me with what i'm doing wrong.
Thanks,
/Calinora