himperson1
New Member
- Joined
- Jun 23, 2016
- Messages
- 33
Good day,
I am looking to take a specific line of data from 2 separate worksheets within a workbook and then paste it into a master file for further use. I then wish to repeat the process for all files within the folder (~200).
I did a bit of searching and found the following code:
Unfortunately, I do not understand what any of this means and don't know where to begin. I'm currently stuck on Set shTarget = ThisWorkbook.Sheets("Summary") in the debugger after getting run-time error 9.
What do I change to:
point at the correct worksheet?
point at the location of my folder?
point to the master file I wish to paste into?
Can anybody point me in the right direction?
Thanks
I am looking to take a specific line of data from 2 separate worksheets within a workbook and then paste it into a master file for further use. I then wish to repeat the process for all files within the folder (~200).
I did a bit of searching and found the following code:
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("Summary")
strPath = GetPath
' Make sure a folder was picked.
If Not strPath = vbNullString Then
' Get all the files from the folder
strfile = Dir$(strPath & "*.xls", vbNormal)
Do While Not strfile = vbNullString
' Open the file and get the source sheet
Set wbSource = Workbooks.Open(strPath & strfile)
Set shSource = wbSource.Sheets("Dashboard")
'Copy the data
Call CopyData(shSource, shTarget)
'Close the workbook and move to the next file.
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 = "AY17:AZ35"
Dim lCol As Long
'Determine the last column.
lCol = shTarget.Cells(8, shTarget.Columns.Count).End(xlToLeft).Column + 1
'Copy the data.
shSource.Range(strRANGE_ADDRESS).Copy
shTarget.Cells(8, lCol).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
Unfortunately, I do not understand what any of this means and don't know where to begin. I'm currently stuck on Set shTarget = ThisWorkbook.Sheets("Summary") in the debugger after getting run-time error 9.
What do I change to:
point at the correct worksheet?
point at the location of my folder?
point to the master file I wish to paste into?
Can anybody point me in the right direction?
Thanks