Copy worksheets to another workbook based on a range

[borkybork]

Hi! I have this code to copy multiple worksheets to another workbook :

Sub Copy_to_Another_Multiple()
Dim Source As String
Dim Destination As String

Source = “Filename1.xlsx”
Destination = “Filename2.xlsx”

Dim Worksheets As Variant
ReDim Worksheets (3)

Worksheet(1) = “ABC”
Worksheet(2) = “DEF”
Worksheet(3) = “GHI”

Dim i as Variant
For i = 1 To UBound (Worksheets)
     Workbooks(Source).Sheets(Worksheets(i)). Copy
           before:=Workbooks(Destination).Sheets(“JKL”)

Is there a way to make this more dynamic and allow you to list and select the worksheet names from a range? The conditions are:

1. The worksheet names change in every source file, and the order matters: ABC, DEF, then GHI for example. Was thinking having the list might help with this?
2. All destination files have a worksheet called “JKL” and the 3 copied worksheets need to come before it. JKL is the first worksheet in the destination file if that helps.
2. The source and destination file names always change.
3. This macro will be in a separate file where we can select the range from.

Thanks in advance!

 

[dmt32]

Hi,
see if this update to your code will do what you want

Sub Copy_to_Another_Multiple()
    Dim wbSource        As Workbook, wbDestination As Workbook
    Dim wsNames         As Worksheet
    Dim SheetNames      As Variant, wsName As Variant
   
   
    Set wbSource = Workbooks("Filename1.xlsx")
    Set wbDestination = Workbooks("Filename2.xlsx")
   
    Set wsNames = ThisWorkbook.Worksheets("Sheet Names")
   
    SheetNames = wsNames.Range("A1").CurrentRegion.Value
   
    For Each wsName In SheetNames
       wbSource.Worksheets(wsName).Copy before:=wbDestination.Worksheets("JKL")
    Next wsName
   
End Sub

Code dynamically reads from a worksheet named Sheet Names

02-09-2022.xls
	A	B	C
1	Sheet1
2	Sheet2
3	Sheet3
4	Sheet4
5	Sheet5
6	Sheet6
Sheet Names

Dave

 

[borkybork]

Hi,
see if this update to your code will do what you want

Sub Copy_to_Another_Multiple()
    Dim wbSource        As Workbook, wbDestination As Workbook
    Dim wsNames         As Worksheet
    Dim SheetNames      As Variant, wsName As Variant
  
  
    Set wbSource = Workbooks("Filename1.xlsx")
    Set wbDestination = Workbooks("Filename2.xlsx")
  
    Set wsNames = ThisWorkbook.Worksheets("Sheet Names")
  
    SheetNames = wsNames.Range("A1").CurrentRegion.Value
  
    For Each wsName In SheetNames
       wbSource.Worksheets(wsName).Copy before:=wbDestination.Worksheets("JKL")
    Next wsName
  
End Sub

Code dynamically reads from a worksheet named Sheet Names

02-09-2022.xls
	A	B	C
1	Sheet1
2	Sheet2
3	Sheet3
4	Sheet4
5	Sheet5
6	Sheet6
Sheet Names

Dave

Hi! Thanks for your response! It works perfectly!!

 

[dmt32]

Hi! Thanks for your response! It works perfectly!!

most welcome & appreciate your feedback

Dave