Grok is a Beast...

quote:

---

I just noticed something, I need an column for "previous date" in column B, do not worry about filling it with data, as discussed earlier I can just use the excel EDATE formula to calculate those start dates based off the given report data in the workbook.

Got it! You want to modify the ConsolidateProductionData macro to add a "Previous Date" column in column B of the "ConsolidatedData" sheet, shifting the existing "Date" column to column C, "Oil Production" to column D, and "Gas Production" to column E. You’ll handle populating the "Previous Date" column using Excel’s EDATE formula later, so the macro just needs to include the header for "Previous Date" in column B and copy the data as before (starting from row 9, with sheet name in column A, raw dates in column C, etc.).Here’s the updated macro:vba

Sub ConsolidateProductionData()
Dim wb As Workbook
Dim ws As Worksheet
Dim wsConsolidated As Worksheet
Dim rowOutput As Long
Dim lastRow As Long
Dim i As Long

' Set reference to the active workbook
Set wb = ThisWorkbook

' Check if ConsolidatedData sheet exists, delete if it does
On Error Resume Next
Set wsConsolidated = wb.Sheets("ConsolidatedData")
If Not wsConsolidated Is Nothing Then
Application.DisplayAlerts = False
wsConsolidated.Delete
Application.DisplayAlerts = True
End If
On Error GoTo 0

' Create new ConsolidatedData sheet
Set wsConsolidated = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
wsConsolidated.Name = "ConsolidatedData"

' Add headers
wsConsolidated.Cells(1, 1).Value = "Sheet Name"
wsConsolidated.Cells(1, 2).Value = "Previous Date"
wsConsolidated.Cells(1, 3).Value = "Date"
wsConsolidated.Cells(1, 4).Value = "Oil Production"
wsConsolidated.Cells(1, 5).Value = "Gas Production"
' Add more headers as needed based on your data structure

' Initialize output row
rowOutput = 2

' Loop through each worksheet
For Each ws In wb.Worksheets
' Skip the consolidated sheet
If ws.Name <> "ConsolidatedData" Then
' Find last row in the sheet (starting from column A)
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

' Loop through rows starting from row 9
For i = 9 To lastRow
' Check if the row has valid data (e.g., non-empty date in column A)
If Not IsEmpty(ws.Cells(i, 1)) Then
' Copy data to consolidated sheet
wsConsolidated.Cells(rowOutput, 1).Value = ws.Name
' Leave column B (Previous Date) empty
wsConsolidated.Cells(rowOutput, 3).Value = ws.Cells(i, 1).Value ' Raw date
wsConsolidated.Cells(rowOutput, 4).Value = ws.Cells(i, 2).Value ' Oil production
wsConsolidated.Cells(rowOutput, 5).Value = ws.Cells(i, 3).Value ' Gas production
' Add more columns as needed
rowOutput = rowOutput + 1
End If
Next i
End If
Next ws

' Auto-fit columns for readability
wsConsolidated.Columns("A:E").AutoFit

MsgBox "Consolidated data sheet created successfully!", vbInformation
End Sub

---