Macro to prevent switching ActiveBook

Hi All,

I have the following code in a UserForm to copy a range of cells and paste it into a new workbook.

    
Sheets("YoYMats").Select
    Range("a1:s25").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste

Unfrotunately, when I do this it makes the new workbook the Active Workbook and the above code fails when it is run a second time. I should also note, I do not want the user to see the workbook behind the UserForm. My current solution to the above problem was to add this code after the paste:

Windows("GHG Calculator NM v3.xlsm").Activate

This works well until the user saves the file as another name, then the code fails and i need to manually go in and update it. Is there a way for me to set the workbook with the macros as always the ActiveWorkbook?

Thanks in advance for your help.

Hi

You can use ThisWorkbook, rather than ActiveWorkbook

This is also a little bit neater, faster and avoids Activating altogether

Sub copyData()

Application.ScreenUpdating = False
   
Workbooks.Add.Sheets("Sheet1").Range("A1:S25").Value = _
         ThisWorkbook.Sheets("Sheet1").Range("A1:S25").Value

Application.ScreenUpdating = True
End Sub

Or maybe:

Sub copyData()

Application.ScreenUpdating = False
   
         ThisWorkbook.Sheets("Sheet1").Range("A1:S25").Copy _
                Workbooks.Add.Sheets("Sheet1").Range("A1")
                

Application.ScreenUpdating = True
End Sub

If you want the formatting

You need in this case to refer to the sheet by it's name/

Option Explicit
Sub copyData()

Application.ScreenUpdating = False
   
Workbooks.Add
ThisWorkbook.Sheets("Year to Year").Range("A1:S25").Copy _
    ActiveWorkbook.Sheets("Sheet1").Range("A1:S25")

Application.ScreenUpdating = True
End Sub

Or

Option Explicit
Sub copyData()

Application.ScreenUpdating = False
   
Workbooks.Add
ThisWorkbook.Sheet1.Range("A1:S25").Copy _
    ActiveWorkbook.Sheet1.Range("A1:S25")

Application.ScreenUpdating = True
End Sub

Read this for a fuller explanation.