Code: Select all
Option Explicit
Sub CopyData()
Dim pathSource As String, pathDest As String
Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet
Dim rowSourceLast As Long, colSourceLast As Long, rowDestLast As Long
pathSource = "C:UsersWolfieeeStyleDesktop1.xls"
pathDest = "C:UsersWolfieeeStyleDesktopBook1.xlsx"
Set wbSource = Workbooks.Open(pathSource, ReadOnly:=True)
Set wsSource = wbSource.Worksheets(1)
Set wbDest = Workbooks.Open(pathDest)
Set wsDest = wbDest.Worksheets(1)
rowSourceLast = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
colSourceLast = wsSource.Cells(1, wsSource.Columns.Count).End(xlToLeft).Column
If wsDest.Range("A1").Value = "" Then
rowDestLast = 0
Else
rowDestLast = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row
End If
wsDest.Range(wsDest.Cells(rowDestLast + 1, 1), wsDest.Cells(rowDestLast + rowSourceLast - 1, colSourceLast)).Value = wsSource.Range(wsSource.Cells(2, 1), wsSource.Cells(rowSourceLast, colSourceLast)).Value
wbDest.Save
wbSource.Close
wbDest.Close
End Sub
Error Details -run time error 1004
Application defined error
If sheet doesn't have data to copy & paste then do nothing simply close all the file & do Nothing