When I tried to copy sheet1 of workbook1 to sheet2 of workbook2 it doesn't show anything on sheet2 except the copy selection frame. If I comment out Activeworkbook.Close. the workbook1 is open and the adequate sheet as well. But not only it is not copying, but when I press control paste it actually paste the sheet2 to itself. So I tried Range(cells(2,1), Cells(lastrow,lastcolumn)).Select without closing the workbook I want to copy to verify if it would at least select the range needed but it returns me a runt time error '1004' . If anyone see where my mistake is please help. thank you
Dim directory As String, FileName As String
Dim lastrow As Long, lastcolumn As Long
Dim erow As Long
Application.ScreenUpdating = False
'Application.DisplayAlerts = False
directory = "C:\Users\Documents\file1\"
FileName = Dir(directory & "*.xl??")
'Open the Excel file
Workbooks.Open (directory & FileName)
'Select the worksheet and range of the worksheet to be copied
Worksheets("Luminaire Summary").Select
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
'ActiveWorkbook.Close
erow = Sheet7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Range(Cells(2, 1), Cells(lastrow, lastcolumn))
I tried writing a shorter code after some advice but I'm still not getting any copied information from workbook1 sheet1 to workbook2 sheet2. Here is my last attempt with no success
Private Sub CommandButton1_Click()
Dim directory As String, FileName As String, total As Integer
Dim lastrow As Long, lastcolumn As Long
directory = "C:\Users\Documents\"
FileName = Dir(directory & "*.xl??")
Workbooks.Open (directory & FileName)
Worksheets("Luminaire Summary").Select
UsedRange.Copy
ActiveWorkbook.Close
Sheets("Sheet2").Range("A1").PasteSpecial
