Selecting all cells

T

Tom Ogilvy

Sub copyToTotal()
Dim sh as Worksheet
Dim x as Long
x = 100
for each sh in ActiveWorkbook.Worksheets
if lcase(sh.name) <> "total" then
Range("2:" & x).Copy Destination:= _
worksheets("Total").Cells(rows.count,1).end(xlup)(2)
end if
Next
End sub
 
D

Deus DNE

Hi,

I have a workbook with x number of sheets in it, each sheet contains x rows.
The top row being the column titles. I need to write a macro/VB to select
all rows containing data except the title row, and paste them into a total
sheet.

What I really need is something more complex, but this will do for starters
!

Hope you can help.

TIA

Deus
 
T

Tom Ogilvy

Left out the sh modifier

Sub copyToTotal()
Dim sh as Worksheet
Dim x as Long
x = 100
for each sh in ActiveWorkbook.Worksheets
if lcase(sh.name) <> "total" then
' modify next line
sh.Range("2:" & x).Copy Destination:= _
worksheets("Total").Cells(rows.count,1).end(xlup)(2)
end if
Next
End sub
 
D

Don Guillett

try this
Sub copytototal()
For Each ws In Sheets
x = ws.Cells(Rows.Count, "a").End(xlUp).Row
If UCase(ws.Name) <> "TOTAL" Then
ws.Rows("1:" & x).Copy Sheets("total") _
..Rows(Cells(Rows.Count, "a").End(xlUp).Row + 1)
End If
Next ws
End Sub
 
D

Deus DNE

Thanks for that Tom.

However, running this appear to do nothing.

Which part s require modding to get the right result.

Sheets are

Total
5AW
5CF etc etc

Need all rows except title copying into row 2 downwards on Total sheet

Apologies for not having a clue :)
 
T

Tom Ogilvy

Since cells is unqualified, this will paste your values on top of each other
unless Total is the activesheet.

--
Regards,
Tom Ogilvy

Don Guillett said:
try this
Sub copytototal()
For Each ws In Sheets
x = ws.Cells(Rows.Count, "a").End(xlUp).Row
If UCase(ws.Name) <> "TOTAL" Then
ws.Rows("1:" & x).Copy Sheets("total") _
.Rows(Cells(Rows.Count, "a").End(xlUp).Row + 1)
End If
Next ws
End Sub
Click to expand...
 
D

Don Guillett

I shouldn't have assumed OP might not be on the total sheet at the time
since I anticipated a button on that page. So,

Sub copytototal1()
For Each ws In Sheets
x = ws.Cells(Rows.Count, "a").End(xlUp).Row
If UCase(ws.Name) <> "TOTAL" Then
With Sheets("total")
ws.Rows("1:" & x).Copy _
..Rows (.Cells(Rows.Count, "a").End(xlUp).Row + 1)
End With
End If
Next ws
End Sub


--
Don Guillett
SalesAid Software
[email protected]
Tom Ogilvy said:
Since cells is unqualified, this will paste your values on top of each other
unless Total is the activesheet.
Click to expand...
 
Top