Macro to pull selected rows from multiple sheets to a summary sheet

[Hanford]

I'm definitely not a programmer and would rate my Excel skills as average at best.

That said, I'm looking for help creating a macro to pulled selected rows from multiple worksheets and paste them all in a summary worksheet. For example, if column A has "Strategy" or "Distribution" in it, I want to pull that into Sheet 5. I want to do this for Sheets 1-3.

I want columns A - H to be pulled into the summary sheet only. The number of rows on sheets 1-3 will vary and continue to grow.

Anything anyone can do to help would be greatly appreciated.

Thanks!

 

[schmidt1962]

Do you mean if a cell (in column a) has the word strategy or distribution?

if so try the Instr function.

 

[Hanford]

not familiar with that function but I'm not sure it will resolve my issue completely. I want to combine 3 different sheets into one summary sheet but only pull certain rows based on what is in a certain column. It definitely needs to be a macro as the ultimate end-user of this spreadsheet only has a very basic knowledge of Excel.

 

[schmidt1962]

Any way you can attach a workbook so I can look at it? It is hard to visualize your goal

 

[Hanford]

It won't let me attach the spreadsheet. I'm willing to share it though. Let me see if I can explain it better.

In my Workbook, I have 4 sheets - Central, Eastern, Western, and All Geo Zones. Each of the sheets have the exact same headers:

Column A - Inititive Type
B - Customer
C - Project
D - Description
E - Approach
F - Timing
G - Priority
H - Project Status
I - Contact
J - Next steps

Each sheet has 2 header rows that I don't want to have pulled over so the data rows start on Row 3

In Column A - Initiative Type, there are several different options to select from a drop down list:

Customer - Distribution
Customer - Event
Customer - Strategy
Capability
Other
Complete

My goal is to take all rows from the Central, Eastern, and Western Sheets that have Customer* (any of the customer options listed above) or Capability in Column A and insert those rows into the All Geo Zones sheet. Ideally, I would have only columns A - H be pulled into the All Geo Zone sheet but that's secondary to main goal of consolidation.

Just like the region sheets, the All Geo Zone sheet has two header rows so I would want the data to start on row 3, (A3).

Hopefully this clears things up. Thank you for your help.

 

[schmidt1962]

From the central eastern and western sheets, do you just want to copy those rows, or do you want to cut them (and place in all geo zones)

 

[Hanford]

I want to copy only

 

[schmidt1962]

Sub Hanford()'
'
'
 
Dim i, Totrows As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Central")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Eastern")
Dim ws3 As Worksheet: Set ws3 = ThisWorkbook.Sheets("Western")
Dim ws4 As Worksheet: Set ws4 = ThisWorkbook.Sheets("All Geo Zones")
 
 
Application.ScreenUpdating = False
 
ws1.Activate
Totrows = Range(Cells(3, 1), Cells(3, 1).End(xlDown).Address).Rows.Count + 2
 
For i = 3 To Totrows
If Cells(i, 1) = "Customer - Distribution" Or Cells(i, 1) = "Customer - Event" Or Cells(i, 1) = "Customer - Strategy" Or Cells(i, 1) = "Capability" Or Cells(i, 1) = "Other" Or Cells(i, 1) = "Complete" Then
Rows(i).Copy
'activates All Geo Zones worksheet
 
ws4.Activate
Rows(3).Select
Selection.Insert Shift:=xlDown
ws1.Activate
'Copy row and paste it to the other worksheet
End If
 
Next i
 
 
ws2.Activate
Totrows = Range(Cells(3, 1), Cells(3, 1).End(xlDown).Address).Rows.Count + 2
 
For i = 3 To Totrows
If Cells(i, 1) = "Customer - Distribution" Or Cells(i, 1) = "Customer - Event" Or Cells(i, 1) = "Customer - Strategy" Or Cells(i, 1) = "Capability" Or Cells(i, 1) = "Other" Or Cells(i, 1) = "Complete" Then
Rows(i).Copy
'activates All Geo Zones worksheet
 
ws4.Activate
Rows(3).Select
Selection.Insert Shift:=xlDown
ws2.Activate
'Copy row and paste it to the other worksheet
End If
 
Next i
 
 
ws3.Activate
Totrows = Range(Cells(3, 1), Cells(3, 1).End(xlDown).Address).Rows.Count + 2
 
For i = 3 To Totrows
If Cells(i, 1) = "Customer - Distribution" Or Cells(i, 1) = "Customer - Event" Or Cells(i, 1) = "Customer - Strategy" Or Cells(i, 1) = "Capability" Or Cells(i, 1) = "Other" Or Cells(i, 1) = "Complete" Then
Rows(i).Copy
'activates All Geo Zones worksheet
 
ws4.Activate
Rows(3).Select
Selection.Insert Shift:=xlDown
ws3.Activate
'Copy row and paste it to the other worksheet
 
End If
 
Next i
 
Application.ScreenUpdating = True
 
'
End Sub

 

[Hanford]

You're amazing! I can't thank you enough. It works great. Thank you!

 

[schmidt1962]

You're welcome! Cheers