VBA Script - Return to named file

[GMC The Macro Man]

Hi team.
I have successfully created a script that goes to a Folder Path and opens a file of your choosing from a multiple list which changes daily, so it won't be the same FNAME. The file has TWO Sheets, "SALES" and "BUDGET". For my report I copy all of the data from the selected sheet ("SALES") and then paste this in to the a chosen sheet within the MASTER file ("Current Sales").
Easy so far but now this is where I am stuck.

I have now been asked to copy the data from the other sheet ("BUDGET") from the above input file and copy all the data to another Sheet ("Current Budget") within the MASTER file.

The issue i have is after doing the 1st part successfully, i need to RETURN to the input file so I can do this task but as the input file will always be a different file, (it doesn't have a FIXED filename,) I don't know what command to write to tell it to go back the the input file.

The MASTER file is currently open and holds the MACRO which runs this script.
This is what's written for the 1st part


Sub Return_()
'Create Message Box
MsgBox "Select current Change Report", vbInformation

Fname = Application.GetOpenFilename(Title:="Select file", MultiSelect:=False)
'Debug.Print Fname

Dim wbTS As Workbook
Set wbTS = Workbooks.Open(Fname)

'Open worksheet tab Named "Sales" and copy all data
wbTS.Worksheets("Sales").Select
ActiveSheet.UsedRange.Copy
'
'Paste the data to the MASTER sheet in cell A1
Workbooks("Master file").Activate
Worksheets("Current Sales").Select
Range("A1").Select
ActiveSheet.Paste

From this point, I need to loop it back to the input file and do the same task again for the BUDGET but I don't know what command i need to write here.

Any help would be greatly appreciated.
Thanks
GMC

 

[rabsofty]

your statement below suggests that the BUDGET data is within the same workbook (Fname)
I have now been asked to copy the data from the other sheet ("BUDGET") from the above input file and copy all the data to another Sheet ("Current Budget") within the MASTER file.

but,
your statement below suggests that the BUDGET data is NOT within the same workbook (Fname)
The issue i have is after doing the 1st part successfully, i need to RETURN to the input file so I can do this task but as the input file will always be a different file, (it doesn't have a FIXED filename,) I don't know what command to write to tell it to go back the the input file.

if the BUDGET data is within the same workbook (Fname), and
since the workbook is already open (wbTS)
add this after your last line to get the data from the BUDGET sheet

'Open worksheet tab Named "Budget" and copy all data
wbTS.Worksheets("Budget").Select
ActiveSheet.UsedRange.Copy
'
'Paste the data to the MASTER sheet in cell A1
Workbooks("Master file").Activate
Worksheets("Current Budget").Select
Range("A1").Select
ActiveSheet.Paste

if the BUDGET data comes from another workbook (a different Fname), please clarify

 

[mumps]

Try:

Sub Return_()
    Application.ScreenUpdating = False
    MsgBox "Select current Change Report", vbInformation
    Dim Fname As String, wbTS As Workbook, wbMaster As Workbook
    Set wbMaster = ThisWorkbook
    Fname = Application.GetOpenFilename(Title:="Select file", MultiSelect:=False)
    Set wbTS = Workbooks.Open(Fname)
    With wbTS
        .Sheets("Sales").UsedRange.Copy wbMaster.Sheets("Current Sales").Range("A1")
        .Sheets("Budget").UsedRange.Copy wbMaster.Sheets("Current Budget").Range("A1")
    End With
    Application.ScreenUpdating = True
End Sub

 

[GMC The Macro Man]

your statement below suggests that the BUDGET data is within the same workbook (Fname)
I have now been asked to copy the data from the other sheet ("BUDGET") from the above input file and copy all the data to another Sheet ("Current Budget") within the MASTER file.

but,
your statement below suggests that the BUDGET data is NOT within the same workbook (Fname)
The issue i have is after doing the 1st part successfully, i need to RETURN to the input file so I can do this task but as the input file will always be a different file, (it doesn't have a FIXED filename,) I don't know what command to write to tell it to go back the the input file.

if the BUDGET data is within the same workbook (Fname), and
since the workbook is already open (wbTS)
add this after your last line to get the data from the BUDGET sheet

'Open worksheet tab Named "Budget" and copy all data
wbTS.Worksheets("Budget").Select
ActiveSheet.UsedRange.Copy
'
'Paste the data to the MASTER sheet in cell A1
Workbooks("Master file").Activate
Worksheets("Current Budget").Select
Range("A1").Select
ActiveSheet.Paste

if the BUDGET data comes from another workbook (a different Fname), please clarify

Hi rabsofty,
Many thanks for taking the time to reply to me and apologies for not explaining myself correctly.
To clarify, SALES and BUDGET are on the same workbook, however my script says this

'Open worksheet tab Named "Sales" and copy all data
wbTS.Worksheets("Sales").Select
ActiveSheet.UsedRange.Copy
'
'Paste the data to the MASTER sheet in cell A1
Workbooks("Master file").Activate
Worksheets("Current Sales").Select
Range("A1").Select
ActiveSheet.Paste

The final line in this script says the "ACTIVESHEET" is the "Current Sales" from the "Master File" Workbook. It's from here that I need to go back to the input Workbook and select the "Budget" sheet.
I've tried your suggestion but it Debugs at wbTS.Worksheets("Budget").Select

What do you think?
GMC

 

[GMC The Macro Man]

Try:

Sub Return_()
    Application.ScreenUpdating = False
    MsgBox "Select current Change Report", vbInformation
    Dim Fname As String, wbTS As Workbook, wbMaster As Workbook
    Set wbMaster = ThisWorkbook
    Fname = Application.GetOpenFilename(Title:="Select file", MultiSelect:=False)
    Set wbTS = Workbooks.Open(Fname)
    With wbTS
        .Sheets("Sales").UsedRange.Copy wbMaster.Sheets("Current Sales").Range("A1")
        .Sheets("Budget").UsedRange.Copy wbMaster.Sheets("Current Budget").Range("A1")
    End With
    Application.ScreenUpdating = True
End Sub

Thank you mumps for taking the time to look at this. Your script fixed this for me. Cheers

 

[rabsofty]

hi GMC,
i forgot the workbook activation.
however, @mumps solution if much more efficient

 

[mumps]

You are very welcome.