I have VBA Code to Sum Data from in Cells J1 from sheets Laz NE Sales to last sheet and paste in Col J1 on Sheet Consolidated. I also need to sum J2 on sheets LAZ NE to Last sheet and this data to be pasted in J2 on sheet Consolidated. I also need to copy the text in I1 and I2 on sheet Laz NE Sales and paste this in cells I1 and I2 on sheet consolidated
The text in I1 and I2 from sheets Naz NE Sales to the last sheet is not being copied and the Values in J1 and J2 are not beind summed and pasted into J1 and J2 on sheet consolidated
The text per my requirement above is not being copied nor is the data is being summed
It would be appreciated if someone could amend my code
The text in I1 and I2 from sheets Naz NE Sales to the last sheet is not being copied and the Values in J1 and J2 are not beind summed and pasted into J1 and J2 on sheet consolidated
The text per my requirement above is not being copied nor is the data is being summed
It would be appreciated if someone could amend my code
Code:
Sub ExtractDataColsIandJ()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim sumJ1 As Double
Dim sumJ2 As Double
Dim textI1 As String
Dim textI2 As String
'Initialize sum variables and text variables
sumJ1 = 0
sumJ2 = 0
textI1 = ""
textI2 = ""
'Loop through each sheet
For Each ws In ThisWorkbook.Worksheets
'Check if the sheet name starts with "Laz NE Sales"
If Left(ws.Name, 13) = "Laz NE Sales " Then
'Find the last row in column J
lastRow = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
'Add the value in J1 to sumJ1 and the value in J2 to sumJ2
sumJ1 = sumJ1 + ws.Range("J1").Value
sumJ2 = sumJ2 + ws.Range("J2").Value
'Copy the text in I1 and I2 to the text variables
textI1 = ws.Range("I1").Value
textI2 = ws.Range("I2").Value
'Exit the loop since we only want to sum and copy the text from the first matching sheet
Exit For
End If
Next ws
'Paste the sum of J1 to Consolidated sheet in column J and row 1
ThisWorkbook.Worksheets("Consolidated").Range("J1").Value = sumJ1
'Paste the sum of J2 to Consolidated sheet in column J and row 2
ThisWorkbook.Worksheets("Consolidated").Range("J2").Value = sumJ2
'Paste the text from I1 to Consolidated sheet in column I and row 1
ThisWorkbook.Worksheets("Consolidated").Range("I1").Value = textI1
'Paste the text from I2 to Consolidated sheet in column I and row 2
ThisWorkbook.Worksheets("Consolidated").Range("I2").Value = textI2
End Sub