Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting multiple cells, I can't see the cells highlighted | Excel Discussion (Misc queries) | |||
How to change shade of cells when selecting multiple cells | Excel Discussion (Misc queries) | |||
By selecting cells adjacent to cells tally sheet | Excel Worksheet Functions | |||
selecting cells | Excel Discussion (Misc queries) | |||
selecting cells | Excel Discussion (Misc queries) |