Excel Help - Suggestions

quote:

---

In Microsoft Excel, I have 52 workbooks containing 7 tabs. Each workbook represents a week of the year and each tab has the utilization data for a division of our company for that week. The Utilization data is in the same column structure in every tab, the only thing that varies is the number of rows of data. The number of rows of data is dynamic every week on every tab. Write a VBA script that will read the data from each of the tabs in each weeks workbook and combine the data into a single consolidated data table in a separate spreadsheet.

---

quote:

---

Sub ConsolidateData()
Dim ws As Worksheet
Dim masterWorkbook As Workbook
Dim dataWorkbook As Workbook
Dim targetSheet As Worksheet
Dim lastRow As Long, lastColumn As Long
Dim dataLastRow As Long
Dim sourceRange As Range
Dim targetRange As Range
Dim folderPath As String
Dim filename As String
Dim weekNum As Integer

' Set the folder path where the weekly workbooks are stored
folderPath = "C:\Path\To\Your\Weekly\Workbooks\" ' Change this to the path where your files are stored

' Create a new workbook
Set masterWorkbook = Workbooks.Add
Set targetSheet = masterWorkbook.Sheets(1)
targetSheet.Name = "Consolidated Data"

' Loop through each workbook
For weekNum = 1 To 52
filename = folderPath & "Week" & weekNum & ".xlsx" ' Adjust the file naming convention as needed

' Open each workbook
Set dataWorkbook = Workbooks.Open(filename)

' Loop through each sheet
For Each ws In dataWorkbook.Sheets
With ws
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set sourceRange = .Range(.Cells(1, 1), .Cells(lastRow, lastColumn)) ' Adjust the range as needed

' Find the next empty row in the master sheet
dataLastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row + 1

' Set the target range
Set targetRange = targetSheet.Cells(dataLastRow, 1)

' Copy data from source to target
sourceRange.Copy Destination:=targetRange
End With
Next ws

' Close the data workbook without saving
dataWorkbook.Close SaveChanges:=False
Next weekNum

' Save the master workbook
masterWorkbook.SaveAs folderPath & "ConsolidatedData.xlsx"
masterWorkbook.Close SaveChanges:=False

' Inform user
MsgBox "Data consolidation complete!"
End Sub

---