Summarising tables data

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Hi i have about 10 sheets and in each sheet there is lot of info but from Row
17 to 48 there is a table (columns B till M)

row 17 and 18 are headers and 19 to 48 are the values to be filled. in all
sheets its the same case
i want all these data to be clubbed into one sheet (summary sheet). how can
i do this, just want data to be placed
 
Something like this

Sub consolidateem()
For Each sh In Sheets
If sh.Name <> "Sheet1" Then
With Sheets("sheet1")
dlr = .Cells(Rows.Count, "B").End(xlUp).Row + 1
sh.Range("b19:m48").Copy .Cells(dlr, "b")
End With
End If
Next
End Sub
 
Thanks a lot Don, its working perfect. I got just one issue.

i created a button and assigned a macro to it. Whats happening is for the
first time i click button it gives me values, second time i click button it
should refresh but instead places the values as a copy as i ahve obtained in
first click

well workaround i have is that will delete before clicking summary table.
any light you can throw if i need to avoid this

TIA
max
 
Sub deleteold_consolidateem()

with sheets("sheet1")
lr=.cells(rows.count,"b").end(xlup).row
..range("a2:a"&lr).entirerow.delete
end with

For Each sh In Sheets
If sh.Name <> "Sheet1" Then
With Sheets("sheet1")
dlr = .Cells(Rows.Count, "B").End(xlUp).Row + 1
sh.Range("b19:m48").Copy .Cells(dlr, "b")
End With
End If
Next
End Sub
 
Thanks a ton - Don.

It did work fine

Don Guillett said:
Sub deleteold_consolidateem()

with sheets("sheet1")
lr=.cells(rows.count,"b").end(xlup).row
..range("a2:a"&lr).entirerow.delete
end with

For Each sh In Sheets
If sh.Name <> "Sheet1" Then
With Sheets("sheet1")
dlr = .Cells(Rows.Count, "B").End(xlUp).Row + 1
sh.Range("b19:m48").Copy .Cells(dlr, "b")
End With
End If
Next
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
Back
Top