Opening two files and copying information

[mayhu372]

Hi!
I cant seem to fix this one... but its been a while since i do this.

i am trying to:
1. Ask user to select two files to open via a prompt.
2. Clear the data in the "AP Template" file (file 1)
3. Copy Data from "HL Data Sheet" workbook (file 2)
4. Paste into predefined cell in file 1 "AP Template"
5. Save as "AP Template" as new file

Issue: it gets stuck in step 4. activating the "AP template" file again. hence i cannot copy the data back into the first file.

Any advice on what I am missing? Here is the code. (any other advice also welcomed!)

-----------------------------

Sub Step1()
'Create AP file from Hub light Step 1

Application.ScreenUpdating = False

'on error exit
   
'name the version of file to create

    
'ask for file path for AP Template and open

    Dim APTemplate As Variant
    
    APTemplate = Application.GetOpenFilename(fileFilter:="Excel Files (*.XLSx), *.XLSx", Title:="Select AP Template")
    If APTemplate = False Then Exit Sub
    Set APWB = Workbooks.Open(APTemplate)

 
        
    ' Clear old data in AP Template
    
       Sheets("input").Activate 'Activate sheet to be set to Input
       
        Range("B12:BL12").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        Range("BN2:Bo12").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        Range("BQ2:DL12").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        Range("DN2:EF12").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        Range("EX2:EY12").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
        Range("GA2:GB12").Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.ClearContents
            
            
'open HL Data Sheet and copy to AP Template

    Dim HLData As Variant
    
    HLData = Application.GetOpenFilename(fileFilter:="Excel Files (*.XLSx), *.XLSx", Title:="Select HubLite Data")
    If HLData = False Then Exit Sub
    Set HLWB = Workbooks.Open(HLData)
    
    On Error Resume Next
       ActiveSheet.ShowAllData 'only one sheet available in the HL extract, remove filters if any
    On Error GoTo 0
    
    'copy columns
        Range("D2:D7000").Select
           Selection.Copy
           
 'Paste in APWB
 
  Workbook(APWB).Activate
  Sheets("input").Activate
   Range("B12").PasteSpecial Paste:=xlPasteFormats
 
    
'Save AP Template as a new version using the name on cell C2

ThisFile = Range("C2").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
    
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

APWB.Activate

 

[mayhu372]

fantastic! thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.