VBA: Copy an Excel worksheet into another workbook

Question

I'm trying to automate a process in VBA but there's one point I've been stuck on for a long time now.

I've got 2 Excel workbooks - let's call them:

2018_W78_Workbook &
20180913_Z28 2018

These workbooks do have variable names.
By this I mean that the first workbook changes its name every year.
For example, 2019_W78_Workbook becomes 2020_W78_Workbook and so on.

The second workbook changes its name every month.

The first 8 letters change every month,
the next 4 letters are always the same and
the last 4 change every year.

The worksheets of each workbook have constant titles.

I'm saying this because the always differing titles make it impossible to do this macro via recording.

The task is now to copy the worksheet of 20180913_Z28 2018 (Sheet A) into 2018_W78_Workbook?

These two workbooks would be the only open ones at the time of executing the macro.

Welcome to Stack Overflow! To give you a great answer, it might help us if you have a glance at How to Ask if you haven't already. It might be also useful if you could provide a minimal reproducible example. — Mat

Badja Badja · Accepted Answer · 2018-11-30T11:49:13

I've used this before in a similar setting

Have a go with:

Sub sheetCopyTransfer()

   Dim wbS As Workbook, wbT As Workbook
   Dim wsS As Worksheet, wsT As Worksheet
   Dim chgTitle As String
'<<This will help you work with the underscores and numericals in the name

   chgTitle = ThisWorkbook.Worksheets("IR General Info").Range("B2").Text

   Set wsS = ThisWorkbook.Worksheets("Bulk Upload")

   wsS.Copy
   ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & title & ".xlsx" 'This will save it where you current workbook is with the new saved title (of the worksheet)
   Set wbT = excel.workbooks(title & ".xlsx") 'assign reference 

   Set wsT = wbT.Worksheets("Bulk Upload")
   wsT.Name = "Exported_BulkUpload"


End Sub

Full credit goes to user @whytheq for when I used his code about 2 years ago

VBA: Copy an Excel worksheet into another workbook

1 Answers