Copying a row [Closed]

Report
Posts
1
Registration date
Wednesday November 12, 2014
Status
Member
Last seen
November 12, 2014
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
I need help please. I have a workbook with several sheets. Each sheet contains similar data with a 'total' row indicating the total number of occurrences for each column (each sheet has different totals). I would like to copy that 'total' row from each sheet into a new sheet so that I can chart a histogram. Each row in each sheet may be located in a different location (i.e. one total line may be on row 132, another may be on row 25, etc.) Anybody have any help or suggestions on how I can do this?

Thank you so much in advance.

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
OPEN A NEW SHEET AND CALL LIT "sum" (without quotes)

presume that you have used formula for totalling either "autosum" or using formula with =sum(....)

now try this macro

Sub test()
Dim j As Integer
Dim tot As Range
For j = 1 To Worksheets.Count
If Worksheets(j).Name = "sum" Then GoTo nextj
With Worksheets(j)
Set tot = .Cells.Find(what:="sum", lookat:=xlPart)
'MsgBox tot.Address
tot.EntireRow.Copy
With Worksheets("sum")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End With
End With

nextj:
Next j


End Sub



Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!