I have a macro below to copy the data from the source workbook where the sheets are the same.
I would like it amended on to copy the data from the source workbook where the sheets are he same from row 16 onwards from Col A:U up to the last row containing data in Col A
the data must be pasted as values
Your assistance is most appreciated
I would like it amended on to copy the data from the source workbook where the sheets are he same from row 16 onwards from Col A:U up to the last row containing data in Col A
the data must be pasted as values
Your assistance is most appreciated
Code:
Sub CopySheetData()
Dim flder As FileDialog, FileName As String, FileChosen As Integer, desWB As Workbook, ws As Worksheet
Set desWB = ThisWorkbook
Set flder = Application.FileDialog(msoFileDialogFilePicker)
flder.Title = "Please Select an Excel File."
With flder
.Filters.Clear
.Filters.Add "Excel files", "*.xlsx"
.InitialFileName = "C:\My Documents\*Sales*.*" 'change to suit one's needs
.Show
End With
FileChosen = flder.Show
FileName = flder.SelectedItems(1)
Set srcWB = Workbooks.Open(FileName)
For Each ws In Sheets
ws.UsedRange.Copy desWB.Sheets(ws.Name).Range("A1")
Next ws
ActiveWorkbook.Close False
End Sub