Iterate Through Workbook; Copy & Paste Data to new workbook

Question

Background

I receive a bunch of excel workbooks (AUTHORIZED EXPENSES) from various BUSINESS UNITS throughout the world. My objective is to create a macro that opens each BUSINESS UNIT'S workbook, copy their expense data, and paste it into a master file for easy comparison.

PROCESS

Create a tab for each BUSINESS UNIT in the TARGET_WORKBOOK (this is done outside of the macro)
For each tab in TARGET_WORKBOOK, have some meta data that helps the macro in navigating to the BUSINESS UNIT's proper file path (SOURCE_WORKBOOK)
Open correct SOURCE_WORKBOOK and navigate to "Auth Expense Data Entry" tab within the SOURCE_WORKBOOK
Copy data from SOURCE_WORKBOOK to TARGET_WORKBOOK, clear clipboard cache, close SOURCE_BOOK
ISSUE - Move to next tab in SOUCE_WORKBOOK and repeat Step 1

CODE

Sub AllUnits()

Dim Current As Worksheet

'For every worksheet in workbook, call AuthExpense function
For Each Current In ThisWorkbook.Worksheets
Call AuthExpense(Current)
Next Current

End Sub


Sub AuthExpense(Current As Worksheet)

Dim Target_Workbook As Workbook
Dim Source_Workbook As Workbook
Dim Source_Path As String


'Configure macro for business-specific unit
BusinessUnit = ActiveSheet.Name
BusinessName = ActiveSheet.Cells(2, 2)

'Declare Target & Source workbooks w/ relative paths
Set Target_Workbook = ThisWorkbook
Source_Path = ThisWorkbook.Path & "\Business Unit Monthly Reporting Template_" & BusinessName & ".xlsx"
Set Source_Workbook = Workbooks.Open(Source_Path)

'Copy Source Workbook to Target Workbook
Source_Workbook.Sheets("Auth Expense Data Entry").Range("A1:H150").Copy

'Paste Special Source data to Target workbook
Target_Workbook.Sheets(BusinessUnit).Range("A5").PasteSpecial Paste:=xlPasteValues

'Clear clipboard cache and close
Application.CutCopyMode = False
Source_Workbook.Close (False)


End Sub

NOTE

I can successfully open, copy, paste, clear clipboard cache, and close a BUSINESS UNIT's workbook.

ISSUES

My issues occurs in the looping/iteration function ("AllUnits()"). When the macro runs, the Master Excel file copy/pastes the same business unit's data 10 times (on the same worksheet, overwriting itself). I believe my issue occurs when I try to move over to the next tab on the Master File. Any suggestions?

In your sub AuthExpense, change all references to ActiveSheet to Current and it should work. — Kyle
When I change "Active" to "Current", the macro only loops through one iteration. Why would this occur? — jonplaca

Kyle Kyle · Accepted Answer · 2016-08-30T21:21:00

Your AllUnits() sub has no problems. It should just loop through the sheets in the workbook it is housed in. You must change you sub AuthExpense to not reference the ActiveSheet. You never activate the sheet in your AllUnits() sub, so the next sheet isn't the active one. Use the below.

Sub AllUnits()

Dim Current As Worksheet

'For every worksheet in workbook, call AuthExpense function
For Each Current In ThisWorkbook.Worksheets
Call AuthExpense(Current)
Next Current

End Sub


Sub AuthExpense(Current As Worksheet)

Dim Target_Workbook As Workbook
Dim Source_Workbook As Workbook
Dim Source_Path As String


'Configure macro for business-specific unit
BusinessUnit = Current.Name
BusinessName = Current.Cells(2, 2)

'Declare Target & Source workbooks w/ relative paths
Set Target_Workbook = ThisWorkbook
Source_Path = ThisWorkbook.Path & "\Business Unit Monthly Reporting Template_" & BusinessName & ".xlsx"
Set Source_Workbook = Workbooks.Open(Source_Path)

'Copy Source Workbook to Target Workbook
Source_Workbook.Sheets("Auth Expense Data Entry").Range("A1:H150").Copy

'Paste Special Source data to Target workbook
Target_Workbook.Sheets(BusinessUnit).Range("A5").PasteSpecial Paste:=xlPasteValues

'Clear clipboard cache and close
Application.CutCopyMode = False
Source_Workbook.Close (False)


End Sub

Iterate Through Workbook; Copy & Paste Data to new workbook

1 Answers