Switch back to original workbook within macro

**theletterh** · 03-04-2013, 01:15 PM

Hello all,

Attempting to run a macro from workbook A ("Feedback_View_(Perspective)_2013-03-04-0950.csv") where data from workbook B ("Helper.xlsx") is copied and pasted into a new worksheet named "Helper" in workbook A. Here is my code so far:

Sub proGetHelper()

ThisWorkbook.Activate
Sheets.Add.Name = "Helper"
Sheets("Helper").Select

Workbooks.Open Filename:="C:\...\Helper.xlsx", ReadOnly:=True

Workbooks("Helper.xlsx").Activate
Sheets(1).Select
Range("A1:H46").Copy

Workbooks("Feedback_View_(Perspective)_2013-03-04-0950.csv").Activate
Sheets("Helper").Select

[a1].PasteSpecial Paste:=xlPasteValues
[a1].Select

Application.CutCopyMode = False

Workbooks("Helper.xlsx").Close

End Sub

This code works, however I would like it to work for other "destination" files, which will have a different date/time stamp.

Any help would be much appreciated!

**Norie** · 03-04-2013, 01:47 PM

You could change the code so it doesn't need to switch workbooks.

Sub proGetHelper()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsDst As Worksheet

    Set wbDst = ThisWorkbook

    Set wsDst = wbDst.Sheets.Add
    wsDst.Name = "Helper"

    Set wbSrc = Workbooks.Open(Filename:="C:\...\Helper.xlsx", ReadOnly:=True)

    wbSrc.Sheets(1).Range("A1:H46").Copy

    wsDst.Range("A1").PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False

    wbSrc.Close

End Sub

**xladept** · 03-04-2013, 03:19 PM

Something like this??

Sub proGetHelper()
Dim D As String
D = "Feedback_View_(Perspective)_2013-03-04-0950.csv"
Call GetHelper(D)

D = "Different Destination"
Call GetHelper(D)

End Sub

Sub GetHelper(Destination As String)

ThisWorkbook.Activate
Sheets.Add.Name = "Helper"
Sheets("Helper").Select

Workbooks.Open fileName:="C:\...\Helper.xlsx", ReadOnly:=True

Sheets(1).Range("A1:H46").Copy

Workbooks(Destination).Activate
Sheets("Helper").[A1].PasteSpecial Paste:=xlPasteValues

Workbooks("Helper.xlsx").Close

End Sub

**theletterh** · 03-04-2013, 03:39 PM

Thanks Norie,

That worked beautifully. As a follow up, if I save this macro in my hidden PERSONAL.XLSB, what adaptation to the macro ought I make such that the new "helper" tab gets created in the workbook that I have open?