Close workbooks and move in and out of workbooks in VBA

[John Vieren]

Dear all,

Thank you for reading my post.

My macro is very simple. Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops.

My macro is as follows and I have 3 question in capital letters.

Sorry i am a beginner and appreciate your help, regards John

Sub Import()

Dim CurrentLoop As Long


Range("PV_Destination_EB").Clear
Range("PV_Destination_NB").Clear
Range("Clear_FPOF").Clear


For CurrentLoop = 1 To [Maximum_Loops]
    Range("Current_Loop") = CurrentLoop
    Sheets("Sheet1").Calculate
    
    Workbooks.Open Filename:=[Current_EB_FPOF_Location]
    Windows("FPOF Summary 1.1.xlsm").Activate ' NOT SURE WHY I NEED THIS BUT DOES NOT WORK WITHOUT? HOW NOT TO USE THE FILE NAME?
    
    Workbooks.Open Filename:=[Current_NB_FPOF_Location]
    Windows("FPOF Summary 1.1.xlsm").Activate ' NOT SURE WHY I NEED THIS BUT DOES NOT WORK WITHOUT? HOW NOT TO USE THE FILE NAME?
    
    Sheets("Sheet1").Calculate
        
    Range("Formula_FPOF").Copy
    Range([Destination_Range1]).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
              
    Range("Formula_FPOF").Copy
    Range([Destination_Range1]).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
           
    Range("PV_Source_EB").Copy
    Range("PV_Destination_EB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("PV_Source_EB").Copy
    Range("PV_Destination_EB").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
            
    Range("PV_Source_NB").Copy
    Range("PV_Destination_NB").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("PV_Source_NB").Copy
    Range("PV_Destination_NB").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
       
    Workbooks.Close Filename:=[Current_NB_FPOF_Location]
    Workbooks.Close Filename:=[Current_EB_FPOF_Location]
    Windows("FPOF Summary 1.1.xlsm").Activate
    
    ' HOW TO CLOSE THE TWO WORKBOOKS OPENED ABOVE?
    
    If Range("Current_Loop") > Range("Maximum_Loops") Then Exit Sub
Next_Data:    Next
           
End Sub

[davesexcel]

Try to utilize this code to fit in with your existing code
http://support.microsoft.com/kb/147742

[John Vieren]

Hi

Thanks for your response but that did not help.

Regards John

[John Vieren]

Hi I marked it as solved so that you dont delete my othe post.

But honestly its not solved.

Regards John



Moderator note: discussion continues in this thread