help with reporting writing macro

[kssrb]

Hello,

I am very new to vba and I am trying to create a report from a workbook with 9 tabs. All 9 tabs have data with blank rows in between so I have the below macro running to delete the blank rows. However, now I want to insert a new sheet called "Summary" and consolidate all the rows from each of the 9 sheets with only columns A, B, C, G, H, I and J. Row 1 is the title for each of the columns so I do not want to repeat that one from each tab. I have searched and googled for this one but with no result. I will appreciate your time and help with this report. Thanks a lot in advance.


Sub deleterows()
Dim i As Long, ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Application.ScreenUpdating = False
With ws.UsedRange
For i = .Rows.Count To 1 Step -1
If .Cells(i, "C") = "PIF" Or .Cells(i, "C") = "pif" Then
.Rows(i).EntireRow.Delete
ElseIf .Cells(i, "C") = "" And .Cells(i, "G") = "" Then
.Rows(i).EntireRow.Delete
ElseIf Application.CountA(Range(.Cells(i, "A"), .Cells(i, "J"))) = 0 Then
.Rows(i).EntireRow.Delete
End If
Next i
End With
Next ws
Application.ScreenUpdating = True
End Sub

 

[dermie_72]

Duplicate post. http://www.mrexcel.com/forum/excel-questions/801235-help-macro.html