PDA

View Full Version : [SOLVED] Send data from multiple worksheet to a summary worksheet



mleblanc001
05-10-2017, 07:42 PM
Hello VBA Gurus,
I'm stumped. I'm trying to find a way to copy a whole row to the next available row in a summary worksheet based on a specific word. If you look at my attached worksheet and VBA code below, I am able to copy data to the summary worksheet "result" but I would like to make it a bit more dynamic by not having to name the worksheets in my code (i.e. sheets("sheet1"), sheets("sheet2"), etc.) but code is so it automatically copies the data from all worksheets to the result summary worksheet. Can anyone help me. Thank you.


Option Explicit

Sub Workbook_open()
Dim i, LastRow
Dim Sht As Worksheet

For Each Sht In Sheets
'If Sheets(Sht.Name).Visible = True Then
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Result").Range("A2:C10000").ClearContents

For i = 2 To LastRow
If Sheets("Sheet1").Cells(i, "C").Value = "Male" Then
Sheets("Sheet1").Cells(i, "C").EntireRow.Copy Destination:=Sheets("Result").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If

If Sheets("Sheet2").Cells(i, "C").Value = "Male" Then
Sheets("Sheet2").Cells(i, "C").EntireRow.Copy Destination:=Sheets("Result").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If

Next i
Next Sht
End Sub

SamT
05-10-2017, 08:19 PM
Sub Workbook_open()
Dim i, LastRow
Dim Sht As Worksheet

Sheets("Result").Range(Cells(2, 1), Cells(Rows.Count, Columns.Count)).ClearContents

For Each Sht In Sheets
With Sht
If .Name <> "Results" Then
LastRow = .Range("A" & Rows.Count).End(xlUp).Row


For i = 2 To LastRow
If .Cells(i, "C").Value = "Male" Then _
.Rows(i).Copy Destination:=Sheets("Result").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End If
End With
Next Sht
End Sub

mleblanc001
05-10-2017, 08:42 PM
Hi Sam,
Thank you so much for the quick reply. When I ran the procedure, I was getting an error message but quickly was able to see that I just had to change Results to Result and it worked like a charm (see below).
I.E. (If .Name <> "Results" Then to If .Name <> "Result" Then)

Thanks again, you are a lifesaver.

SamT
05-10-2017, 09:16 PM
:thumb