Macro to Extract text in I1 and I2 and sum J1 and J2 from specific sheet to last sheet

[howard]

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

 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

 

[DanteAmor]

hi @howard:
Verify that there are sheets where the name begins with this text: "Laz NE Sales "

Try this:

 Sub ExtractDataColsIandJ()
    Dim ws As Worksheet
    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 LCase(Left(ws.Name, 13)) = LCase("Laz NE Sales ") Then
            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
        End If
    Next ws
    
    With ThisWorkbook.Worksheets("Consolidated")
      .Range("J1").Value = sumJ1
      .Range("J2").Value = sumJ2
      .Range("I1").Value = textI1
      .Range("I2").Value = textI2
    End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[howard]

Hi Dante

Thanks for the Help. The Text in I1 & I2 on sheet Naz NE Sales is not being copied and pasted on sheet consolidated in I1 and & I2

the values in J1 on sheets Naz NE Sales to last sheet is not being summed and pasted in J1 on sheet consolidated nor is J2 being summed and pasted in J2 on sheet Consolidated

 

[DanteAmor]

In my tests my code works, it copies the sum and the texts.
Something is not right in your sheets.
Could you put several examples of what you have in your sheets.
Use the XL2BB tool to put examples here.

For example:

Dante Amor
	I	J
1	i1 a	12
2	i2 a	13
Laz NE Sales a

Dante Amor
	I	J
1	i1 b	22
2	i2 b	23
Laz NE Sales b

Results of the sum of the two previous sheets:

Dante Amor
	I	J
1	i1 b	34
2	i2 b	36
Consolidated

 

[howard]

Hi Dante

See sample data per link below

Copy and Sum Data.xlsx

Shared with Dropbox

www.dropbox.com

 

[howard]

For some unknown reason I get an error when activating XL2BB -The file type is not supported in protected view

I sent you a link to drop box so you can identify the issue

 

[DanteAmor]

Verify that there are sheets where the name begins with this text: "Laz NE Sales "

In your book: None of your sheets begin with the name "Laz NE Sales "

------------------------------------------------------------------
This is your original post:

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.

------------------------------------------------------------------

In order for me to better understand the problem, I would like to ask a few questions:

These are the sheets you have in your book: "Naz NE Sales", "Soth GT Sales", "Naz F1 Sales", "Soth N1 Sales".
1. So which sheets do you want to sum?
2. Do you want to add all the ones that have the word "salts" in the name of the sheet?
3. Or why did you put this condition in your macro?

If Left(ws.Name, 13) = "Laz NE Sales " Then

4. You didn't put the result in the "Consolidated" sheet so it's hard for me to guess which result you want.

So, if you explain in detail each of the 4 points, it will be easier to help you.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[howard]

Hi Dante
Sorry for any confusion caused

1) I want to Extract the Text from cells I1 and I2 from sheet Naz NE Sales to the last sheet and paste these in Cell I1 on sheet Consolidated
2) I want to sum the Values in J1 for Sheet Naz NE Sales to the last sheet and paste this in J1 on sheet consolidated
3) I want to sum the Values in J2 for Sheet Naz NE Sales to the last sheet and paste this in J2 on sheet consolidated

I have shown the result on sheet consolidated -see link below

Copy and Sum Data Manual Result.xlsm

Shared with Dropbox

www.dropbox.com

 

[DanteAmor]

3. Or why did you put this condition in your macro?

You didn't explain why you put that condition, no sheet has that name.

But I'm trying to understand what you need, probably the following condition will check all sheets that contain the word "sales":

Sub ExtractDataColsIandJ()
  Dim ws As Worksheet
  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 has "Sales"
    If InStr(1, ws.Name, "Sales", vbTextCompare) Then
      sumJ1 = sumJ1 + ws.Range("J1").Value
      sumJ2 = sumJ2 + ws.Range("J2").Value
      'Copy the text in I1 and I2 to the text variables
      If ws.Range("I1").Value <> "" Then textI1 = ws.Range("I1").Value
      If ws.Range("I2").Value <> "" Then textI2 = ws.Range("I2").Value
    End If
  Next ws
 
  With ThisWorkbook.Worksheets("Consolidated")
    .Range("J1").Value = sumJ1
    .Range("J2").Value = sumJ2
    .Range("I1").Value = textI1
    .Range("I2").Value = textI2
  End With
End Sub

Or if we read the sheets from sheet number 2 to the last sheet of the book:

Sub ExtractDataColsIandJ_2()
  Dim ws As Worksheet
  Dim sumJ1 As Double
  Dim sumJ2 As Double
  Dim textI1 As String
  Dim textI2 As String
  Dim i As Long
 
  'Initialize sum variables and text variables
  sumJ1 = 0
  sumJ2 = 0
  textI1 = ""
  textI2 = ""
 
  'Loop through each sheet
  For i = 2 To Sheets.Count
    Set ws = Sheets(i)
    sumJ1 = sumJ1 + ws.Range("J1").Value
    sumJ2 = sumJ2 + ws.Range("J2").Value
    'Copy the text in I1 and I2 to the text variables
    If ws.Range("I1").Value <> "" Then textI1 = ws.Range("I1").Value
    If ws.Range("I2").Value <> "" Then textI2 = ws.Range("I2").Value
  Next
 
  With ThisWorkbook.Worksheets("Consolidated")
    .Range("J1").Value = sumJ1
    .Range("J2").Value = sumJ2
    .Range("I1").Value = textI1
    .Range("I2").Value = textI2
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[howard]

Hi Dante


Many Thanks for the help. I used your second option. Have run your code and it works 100%