VBA: Set active workbook when macro is run as 'thisworkbook'

[Lefemmenikita]

Hi There

I have a workbook which is a newly opened excel workbook which is unsaved

A user will paste data into this blank workbook and run a macro called 'termination' (which is residing in their personal.xlsb project)

One of the actions of this macro is to open another workbook called 'source.'

I want data from 'source' to be pasted as a separate sheet in the blank workbook, which is the workbook that was active when the termination macro was run.

How do I do this?

So far, my code opens the file 'source' though cannot paste into the original unsaved workbook (the paste method fails)

My code so far

Sub Termination
--wb is referring to the blank workbook opened. This is not saved
Dim wb as workbook
-----wb_1 has the info that will be copied
Dim wb_1 as workbook
dim wb_sheet as worksheet
dim ws as worksheet

set workbook= thisworkbook
set wb_1= workbooks.open("M:\network_files\source.xlsx")

with wb
set wb_sheet=worksheets(1)
end with

with wb_1.sheets(1).select
activesheet.copy
wb_1.close
end with


with wb
set ws=sheets.add
ws.select
activesheet.paste

Sorry if the code is nonsensical. I don't know how to write VBA properly


Thanks

 

[Fluff]

How about

Sub Termination()
'wb is referring to the blank workbook opened. This is not saved
Dim wb As Workbook
'wb_1 has the info that will be copied
Dim wb_1 As Workbook
Dim wb_sheet As Worksheet
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set wb_1 = Workbooks.Open("M:\network_files\source.xlsx")


Set wb_sheet = wb.Worksheets(1)

wb_1.Sheets(1).Copy , wb.Sheets(1)
wb_1.Close
End Sub

 

[Lefemmenikita]

Thank you Fluff. I haven't had a chance to test this yet...I will try it in the next few days when I have access to the workbook in question and let you know how I go

 

[Fluff]

Ok, that's fine.