copy sheets from one workbook to another

[Jebacmakro]

Hi,

i have a huge problem with what appeared to my first as simple copying sheets routine BUT NOPE.

I have a two workbooks open - each one has a different name as a workbook itself and also i have set a name1 and name4 for both of them - i am pasting code below my post.
i need to copy a sheet from file name4 to file name1 - but it is not working in any way. i am assuming that it is a case related to their variable name?
the problem is that i cannot use its excel workbook name because each time it has different date in name so i had to set the variable for each one.
can you help me please?

Sub new ()



Dim name1, name2, name3 As String
Dim range1, range2, range3 As Variant



MsgBox "Choose 1st file"
On Error GoTo errorhandler1
filetoOpen1 = Application _
 .GetOpenFilename("Excel files (*.xlsx), *.xls")
If filetoOpen1 <> False Then
End If



range1 = filetoOpen1
Workbooks.Open (range1)
name1 = ActiveWorkbook.Name
errorhandler1:
   
MsgBox "Choose 2nd file"
On Error GoTo errorhandler2
filetoOpen2 = Application _
 .GetOpenFilename("Excel files (*.xlsx), *.xls")
If filetoOpen2 <> False Then
End If

range2 = filetoOpen2
Workbooks.Open (range2)
name2 = ActiveWorkbook.Name
errorhandler2:

MsgBox "Choose 3rd file"
On Error GoTo errorhandler3
fileToOpen3 = Application _
 .GetOpenFilename("Excel files (*.xlsx), *.xls")
If fileToOpen3 <> False Then
End If

range3 = fileToOpen3
Workbooks.Open (range3)
name3 = ActiveWorkbook.Name
errorhandler3:


'copy file 2 and 3 to file 1

For Each Sheet In Workbooks(name2).Sheets
Sheet.copy After:=Workbooks(name1).Sheets(Workbooks(name1).Sheets.Count)
Next Sheet
For Each Sheet In Workbooks(name3).Sheets
Sheet.copy After:=Workbooks(name1).Sheets(Workbooks(name1).Sheets.Count)
Next Sheet
Workbooks(name2).Close
Workbooks(name3).Close

'choose sheets name

Dim a As String

a = Format(Date, "DD.MM.YY")

Workbooks(name1).Sheets("1").Name = "1" & a
Workbooks(name1).Sheets("2").Name = "2" & a
Workbooks(name1).Sheets("3").Name = "3" & a


'choose file

Dim name4  As String
Dim range4 As Variant

MsgBox "Choose Old Report file"

filetoOpen4 = Application _
 .GetOpenFilename
If filetoOpen4 <> False Then
End If

range4 = filetoOpen4
Workbooks.Open (range4)
name4 = ActiveWorkbook.Name

End Sub

Sub copy() - not working

ActiveWorkbook.Sheets("Sheet4").copy After:=Workbooks(name1).Sheets(Sheets1)

End Sub

Edit: Or can i do it another way? using userform to choose which sheets i need to copy? or it cannot be done at all because of the names?

 

[Jebacmakro]

nevermind I made it :D