VBA question involving 2 different workbooks

[ummjay]

Hi!

I have a macro on 1 workbook, where I allow the user to browse the file they want to manipulate, then it should do some vlookups/pivot on that sheet directly, but seems its doing so on my macro workbook.

What's the best way to re-direct the macro to manipulate on the data workbook itself?

Sub START()
    Dim NewFFN  As Variant
     Dim LastRow As Long
    NewFFN = Application.GetOpenFilename(Title:="Please Select File")
    If NewFFN = False Then
        MsgBox "Macro Terminated Due to No File Selected"
        Exit Sub
    Else
        Workbooks.Open FileName:=NewFFN
    End If
    Application.Calculation = xlCalculationAutomatic   '<- may not be necessary
    
'insert column
With NewFFN

Dim Column As Range
Set Column = Application.Range("C:C")
Column.Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow

'add name to new column
Range("C1").Value = "Type"

'vlookup
 With ActiveSheet.Activate
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        ActiveSheet.Range("C2:C" & LastRow).Formula = "=VLOOKUP(B2,'[Mapping.xlsx]Sheet1'!A:E,5,FALSE)"
'copy/paste as values.
      ActiveSheet.Range("C2:C" & LastRow).Copy
      ActiveSheet.Range("C2:C" & LastRow).PasteSpecial Paste:=xlPasteValues
      ActiveSheet.Range("A1:J" & LastRow).EntireColumn.AutoFit
'Clear Clipboard
  Application.CutCopyMode = False
End With

'start vlookup function
'Dim ws As Worksheet
'    Dim LastRow As Long
'    Dim TargetRange As Range
'
'    On Error GoTo MyErrorHandler:
'
'    Set ws = Sheets("Sheet1")
'
'    LastRow = ws.Cells(Rows.Count, "X").End(xlUp).Row
'    Set TargetRange = ws.Range("A1:X" & LastRow)
'
'    result = Application.WorksheetFunction.VLookup(Sheets("Sheet1").Range("C2"), TargetRange, 5, False)
'
'    MsgBox result
'
'MyErrorHandler:
'    If Err.Number = 1004 Then
'      MsgBox "Value not found"
'    End If



'START pivot
 ActiveSheet.UsedRange.Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Sheets(1).UsedRange).CreatePivotTable TableDestination:="", _
        TableName:="Pivot Summary", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
'Insert PIVOT Fields
  With ActiveSheet.PivotTables("Pivot Summary")
    .PivotFields("CType").Orientation = xlRowField
    .PivotFields("CType").Position = 1
    .PivotFields("Product").Orientation = xlRowField
    .PivotFields("Product").Position = 2
    .PivotFields("Side").Orientation = xlRowField
    .PivotFields("Side").Position = 3
  End With
'Insert Qty column to the data field
  With ActiveSheet.PivotTables("Pivot Summary").PivotFields("Volume")
    .Orientation = xlDataField
    .Position = 1
  .NumberFormat = "#,##0;(#,##0)"

  ActiveWorkbook.RefreshAll
  End With
  End With
  
Application.DisplayAlerts = True
Application.ScreenUpdating = True
  
End Sub

 

[Joe4]

What I usually do is declare a few workbook variables, i.e.

Dim macroWB as Workbook
Dim fileWB as Workbook

Then, at the very top of my procedure after all my variable declarations, the first line of code I run "captures" my Macro Workbook in an object variable, i.e.

Set macroWB = ActiveWorkbook

Then, right after the line where I open the new workbook, I run a line of code to run to "capture" that Workbook in an object variable, i.e.

Workbooks.Open FileName:=NewFFN
Set fileWB = ActiveWorkbook

So now that I have captured each workbook in its own object variable, I can easily (and dynamically) bounce back between each workbook with code like:

macroWB.Activate

and

fileWB.Activate

 

[ummjay]

worked like a charm!