VBA Copy sheets from one Variable sheet to another

[mkpippin]

Hello,

I'm trying to copy two worksheets from one workbook to another. This seems to be fairly straight forward, but for some reason, I can't seem to accomplish this with a variable sheet name.

I have set one of the workbooks to "Report" and the other "Edit"

The "Edit" workbook is currently active and I'm looking to copy the "Lists" worksheet from "Edit" to "Report".

Sheets("Lists").Select
Sheets("Lists").Copy Before:=Report.Sheets("Summary")


Any idea where the break is in this code? Is it a simple Syntax error that I'm overlooking?

 

[DanteAmor]

Try this

Sub Test_copy()
  Dim wb1 As Workbook, wb2 As Workbook
  Dim sh1 As Worksheet
  
  Set wb1 = Workbooks("[COLOR=#ff0000]Edit.xlsx[/COLOR]")    'source book
  Set wb2 = Workbooks("[COLOR=#ff0000]Report.xlsx[/COLOR]")  'destination book
  Set sh1 = wb1.Sheets("Lists")       'sheet to copy from source book
  
  sh1.Copy before:=wb2.Sheets("Summary")
End Sub

These elements must exist:
- "Edit.xlsx" must be open
- "Report.xlsx" must be open
- The sheet "Lists" must exist in the book Edit
- The "Summary" sheet must exist in the Report book

Change
"Edit.xlsx" and "Report.xlsx" by the names of your books.