copy sheets from one workbook to another

Jebacmakro

New Member
Joined
Jun 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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?
 
Last edited by a moderator:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,136,909
Messages
5,678,514
Members
419,768
Latest member
eguechi09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top