I have the following macro below when allows me to select the source file and to copy the data into sheet1 of the destination sheet
I would like the macro amended so that if sheet1 has data in it, then the data must be pasted into the next blank sheet (if no sheet then to insert a sheet)
I would like the macro amended so that if sheet1 has data in it, then the data must be pasted into the next blank sheet (if no sheet then to insert a sheet)
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("sheet1")
Set sourceBook = Workbooks.Open(fileSource)
Set sourceSheet = sourceBook.Sheets(1)
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
With destinationSheet
.Range("a" & destRow & ":z" & destRow + sourceRowCount - 1).Value = sourceSheet.Range("a1:z" & 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