I have written code to open a workbook and copy data from A2 onwards on sheet5 on the source data to H2 on the destination sheet
when running the macro the data from A2 onwards on the source data (Sheet5) on not being copied
I also need to the macro to allow must to select another file or exit if not more files to be selected
Your assistance in this regard is most appreciated
when running the macro the data from A2 onwards on the source data (Sheet5) on not being copied
I also need to the macro to allow must to select another file or exit if not more files to be selected
Your assistance in this regard is most appreciated
Code:
Sub copyDataFromSource()
Dim sourceBook As Workbook
Dim destinationBook As Workbook
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim fileSource, sourceRow%, sourceRowCount&, destRow%
With Application
.ScreenUpdating = False
End With
fileSource = Application.GetOpenFilename
If fileSource = False Or IsEmpty(fileSource) Then Exit Sub
Set destinationBook = ThisWorkbook
Set destinationSheet = destinationBook.Sheets("Query Sheet")
Set sourceBook = Workbooks.Open(fileSource)
Set sourceSheet = sourceBook.Sheets(5)
sourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
sourceRowCount = sourceRow - 1
destRow = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row
destinationSheet.Rows(destRow + 1).Resize(sourceRowCount).Insert
destRow = destRow + 1
With destinationSheet
.Range("H4" & destRow + sourceRowCount - 1).Value = sourceSheet.Range("a2:a" & sourceRow).Value
End With
sourceBook.Close False
With Application
.ScreenUpdating = True
End With
Set sourceBook = Nothing
Set destinationBook = Nothing
Set sourceSheet = Nothing
Set destinationSheet = Nothing
End Sub
