|
Macro Help !!!!
Thanks for your help, howver ... It falls over at : If LCase(sh.Name) < LCase(sh1) Then saying "object doesnt support this property or method" ???????? Tom Ogilvy Wrote: Dim sh1 as worksheet, sh as worksheet Dim rng as range set sh1 = worksheets("Sheet_Src") for each sh in Worksheets if lcase(sh.name) < lcase(sh1) then set rng = sh.Cells(rows.count,1).End(xlup)(3) sh1.rows(1).Resize(6).copy destination:=rng end if Next -- Regards, Tom Ogilvy "GazMo" wrote in message ... Thanks Tom ... what would I need to enter for the range if i wante to copy rows 1-6 on another worksheet to ALL worksheets ? I would also like to put it one row under each of the last rows ! Tom Ogilvy Wrote: Dim sh1 as worksheet, sh as worksheet Dim rng as range set sh1 = worksheets("Sheet_Src") for each sh in Worksheets if lcase(sh.name) < lcase(sh1) then set rng = sh.Cells(rows.count,1).End(xlup)(2) sh1.rows(6).copy destination:=rng end if Next Without specifics on which sheets get populated and what is being copied and where below the line, the above represents a general approach. It copies a row (row 6) from a sheet named Sheet_Src and places it (copies it to the cell/row below the last filled cell on each other worksheet in the workbook (except Sheet_Src). -- Regards, Tom Ogilvy "GazMo" wrote in message ... Hi, I have a series of worksheets in one workbook - I now nee to put the same information (from another worksheet within the same workbook) in each worksheet under the last line (varying length in each worksheet) ... can anyone help me with this please ?? -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=271181 -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread http://www.excelforum.com/showthread...hreadid=271181 -- GazM ----------------------------------------------------------------------- GazMo's Profile: http://www.excelforum.com/member.php...fo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=27118 |
Macro Help !!!!
If LCase(sh.Name) < LCase(sh1.Name) Then
GazMo wrote: Thanks for your help, howver ... It falls over at : If LCase(sh.Name) < LCase(sh1) Then saying "object doesnt support this property or method" ???????? Tom Ogilvy Wrote: Dim sh1 as worksheet, sh as worksheet Dim rng as range set sh1 = worksheets("Sheet_Src") for each sh in Worksheets if lcase(sh.name) < lcase(sh1) then set rng = sh.Cells(rows.count,1).End(xlup)(3) sh1.rows(1).Resize(6).copy destination:=rng end if Next -- Regards, Tom Ogilvy "GazMo" wrote in message ... Thanks Tom ... what would I need to enter for the range if i wanted to copy rows 1-6 on another worksheet to ALL worksheets ? I would also like to put it one row under each of the last rows ! Tom Ogilvy Wrote: Dim sh1 as worksheet, sh as worksheet Dim rng as range set sh1 = worksheets("Sheet_Src") for each sh in Worksheets if lcase(sh.name) < lcase(sh1) then set rng = sh.Cells(rows.count,1).End(xlup)(2) sh1.rows(6).copy destination:=rng end if Next Without specifics on which sheets get populated and what is being copied and where below the line, the above represents a general approach. It copies a row (row 6) from a sheet named Sheet_Src and places it (copies it) to the cell/row below the last filled cell on each other worksheet in the workbook (except Sheet_Src). -- Regards, Tom Ogilvy "GazMo" wrote in message ... Hi, I have a series of worksheets in one workbook - I now need to put the same information (from another worksheet within the same workbook) in each worksheet under the last line (varying length in each worksheet) ... can anyone help me with this please ?? -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=271181 -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=271181 -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=271181 -- Dave Peterson |
Macro Help !!!!
That was one of those ill advised last minute changes
As Dave advised, add name to the end of Sh1 If LCase(sh.Name) < LCase(sh1.Name) Then -- Regards, Tom Ogilvy "GazMo" wrote in message ... Thanks for your help, howver ... It falls over at : If LCase(sh.Name) < LCase(sh1) Then saying "object doesnt support this property or method" ???????? Tom Ogilvy Wrote: Dim sh1 as worksheet, sh as worksheet Dim rng as range set sh1 = worksheets("Sheet_Src") for each sh in Worksheets if lcase(sh.name) < lcase(sh1) then set rng = sh.Cells(rows.count,1).End(xlup)(3) sh1.rows(1).Resize(6).copy destination:=rng end if Next -- Regards, Tom Ogilvy "GazMo" wrote in message ... Thanks Tom ... what would I need to enter for the range if i wanted to copy rows 1-6 on another worksheet to ALL worksheets ? I would also like to put it one row under each of the last rows ! Tom Ogilvy Wrote: Dim sh1 as worksheet, sh as worksheet Dim rng as range set sh1 = worksheets("Sheet_Src") for each sh in Worksheets if lcase(sh.name) < lcase(sh1) then set rng = sh.Cells(rows.count,1).End(xlup)(2) sh1.rows(6).copy destination:=rng end if Next Without specifics on which sheets get populated and what is being copied and where below the line, the above represents a general approach. It copies a row (row 6) from a sheet named Sheet_Src and places it (copies it) to the cell/row below the last filled cell on each other worksheet in the workbook (except Sheet_Src). -- Regards, Tom Ogilvy "GazMo" wrote in message ... Hi, I have a series of worksheets in one workbook - I now need to put the same information (from another worksheet within the same workbook) in each worksheet under the last line (varying length in each worksheet) ... can anyone help me with this please ?? -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=271181 -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=271181 -- GazMo ------------------------------------------------------------------------ GazMo's Profile: http://www.excelforum.com/member.php...o&userid=14610 View this thread: http://www.excelforum.com/showthread...hreadid=271181 |
| All times are GMT +1. The time now is 04:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com