Selecting all cells
 

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

--
Regards,
Tom Ogilvy


"Deus DNE" wrote in message
...
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
--
Does Not Exist

Selecting all cells
 

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
--
Does Not Exist

Selecting all cells
 

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

--
Regards,
Tom Ogilvy

"Deus DNE" wrote in message
...
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 :-)

Selecting all cells
 

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

--
Don Guillett
SalesAid Software

"Deus DNE" wrote in message
...
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
--
Does Not Exist

Selecting all cells
 

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 :-)

Selecting all cells
 

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

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
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

--
Don Guillett
SalesAid Software

"Deus DNE" wrote in message
...
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
--
Does Not Exist

Selecting all cells
 

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

"Tom Ogilvy" wrote in message
...
Since cells is unqualified, this will paste your values on top of each
other
unless Total is the activesheet.

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
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

--
Don Guillett
SalesAid Software

"Deus DNE" wrote in message
...
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
--
Does Not Exist