Switching Between Open Workbooks (windows) using Filenames as Variables

0

I have a Macro that opens (2) Workbooks.

The 1st is an Input data Workbook from a .tsv file.

The Macro then Copys a Series of Cells and Colums into the 2nd.

This process is repeated switching between workbooks until completed.

I need the 1st Workbook to be User Selected,(I am currently using the Application.GetOpenFilename) to do this. 

When the Macro has completed I need the Second Workbookds to be saved to a User Defined Filename and Folder. The Application.GetSaveAsFilename should work fine fo this part. I just haven't gotten things to work that far yet to confirm it.

I am having difficulities assiging the 1st File to a Variable that can be referenced throughout the Macro.

Browsing to and Opening the File is easy it is the referencing it in the statement that switches between the Open workbooks:

 Windows("FileToOpen").activate

That is giving me troubles. How do I set the file I opened to a variable to be referenced in the statement above?

Here is a Sample of the Code that switched back and forth:
   

    Windows("FileToOpen").Activate
    Range("D7:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("MT_PresetTemplate_880.xlsx").Activate
    Range("U65").Select
    ActiveSheet.Paste
    
    Windows("FileToOpen").Activate
    Range("F7:F41").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("MT_PresetTemplate_880.xlsx").Activate
    Range("W65").Select
    ActiveSheet.Paste
    
    Windows("FileToOpen").Activate
    Range("C42").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    Windows("MT_PresetTemplate_880.xlsx").Activate
    Range("W103").Select
    ActiveSheet.Paste

Any help with this would be great   

Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

If an Answer worked for you! Click the Select Answer button at the bottom of it and then click Yes to verify it.
don (rep: 1482) Jul 12, '16 at 10:13 am
Add to Discussion

Answers

0

You just need to setup the basic syntax for referencing workbooks.

Here is a template for that:

Sub reference_workbooks()

Dim Wb1 As Workbook
Dim Wb2 As Workbook

'Makes it run faster.
Application.ScreenUpdating = False

'Use the full path of the file to open it, including the files extension.
Set Wb1 = Workbooks.Open("C:\sample_1.xlsx")
Set Wb2 = Workbooks.Open("C:\sample_2.xlsx")

'Do stuff with the files
'Wb1 and Wb2 references the workbook and the rest is regular syntax for doing stuff in the workbook
'Reference them like this:
wb1_value = Wb1.Sheets("Sheet1").Range("A1").Value
wb2_value = Wb2.Sheets("Sheet2").Range("A1").Value

'When you are done, close the files.
'False means don't save changes. Change to true if you want.
'Do this for each workbook you want to close.
Wb1.Close SaveChanges:=False
Wb2.Close SaveChanges:=False

'Makes Excel work like normal again.
Application.ScreenUpdating = True

End Sub

I commented it pretty heavily so most of it should be pretty easy to understand but ask if anything isn't clear.

You should try to get this working with a simple set of data on your end and then work it into the larger macro; that will make it easier to troubleshoot any issues that might occur.

Discuss

Discussion

I get a RunTime 91 Error after the Set Wb1 = Workbooks.Open statement
Rod Jul 12, '16 at 9:33 pm
Did you change the file path to point to the correct file on your computer?
don (rep: 1482) Jul 12, '16 at 10:02 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login