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
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