# Macro VBA Switching Between Workbooks



## benp106 (Dec 30, 2022)

How do I...
-  (in current workbook) select 5 sheets
 - Copy to New Workbook
 - Do some stuff in the new workbook
 - Then get back to the original workbook and Deselect the 5 sheets to just activate a single sheet 
- Do some stuff in the original workbook 
- End Sub
 - using VBA?

I'm new to VBA, thanks for any help.


----------



## rollis13 (Dec 31, 2022)

Most of what you need can be done with Excel's Macro Recoder. Then you can come back with new threads for the 'smaller' issues you may find in you freshly created macros.


----------



## benp106 (Dec 31, 2022)

rollis13 said:


> Most of what you need can be done with Excel's Macro Recoder. Then you can come back with new threads for the 'smaller' issues you may find in you freshly created macros.


The problem with just the macro recorder is  when I copy the sheets and then go back to the original document.  

It will work sometimes, but the workbook will have to be saved with the same name. The file name will be changing all the time.

Basically in VBA I would like to know how to work between two Workbooks. This will help me through many other scenarios.

I need to (name Current Workbook)
name (new workbook I’m going to)

Then how do I go back and forth between the two workbooks.


----------



## rollis13 (Dec 31, 2022)

To go back and forth you can use the property `.Activate` for a workbook Link.


----------



## B-Man (Dec 31, 2022)

here is some code that may help. I use it to copy to a sheet.
this names the current workbook wsSource and the and the new workbook you open wsDest.

add your recorded macro into this and adjust to suit. it might not be exactly what you want but should be a start.
maybe post you code up so we can help tweak the things you need.



```
Public Sub Copy_toSheet()

    Dim wsSource    As Worksheet
    Dim wsDest      As Worksheet


   
Application.ScreenUpdating = False

    On Error Resume Next
   
'set current worksheet as source
    Set wsSource = ThisWorkbook


'Opens Destination Workbook
        Range("url1").Select ' named range with url of sheet in cell
        Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

'Set open workbook as destination
Workbooks("workbook2").Activate 'this is the name of the second workbook you want to open
    Set wsDest = ActiveWorkbook.Sheets("sheet2")


'add your copy stuff in here



Application.ScreenUpdating = True
End Sub
```


----------



## HaHoBe (Dec 31, 2022)

Hi benp106,

either set objects on the workbooks or store the names in cells on a hidden sheet. Sample for objects may be


```
Public Sub MrE_1225745_1616D15()
' https://www.mrexcel.com/board/threads/macro-vba-switching-between-workbooks.1225745/
' Created: 20221231
' By:      HaHoBe

Dim wbOld As Workbook
Dim wbNew As Workbook
Dim strSheets As String

strSheets = "1,3,7"   '<-- name of worksheets to copy
Set wbOld = ActiveWorkbook

Worksheets(Split(strSheets, ",")).Copy
Set wbNew = ActiveWorkbook

'....

wbNew.SaveAs Application.DefaultFilePath & "\test " & Format(Now, "yymmdd_hhmmss") & ".xlsm", FileFormat:=52
wbNew.Close

'the workbook you started from will by default become the active workbook again
wbOld.Sheets(1).Select
'...

Set wbNew = Nothing
Set wbOld = Nothing
End Sub
```

Ciao,
Holger


----------



## benp106 (Jan 2, 2023)

Thanks for the InSite.  This will be very useful going forward.


----------

