| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy to row xx
Hi,
I had hoped the following code would copy the formulas from row 11 down as far as there is something in the equilavent row in col E. So for example if E100 contains a number (such as 283095) and E101 is empty then the P11-AC100 would be filled with formulas. At the moment this code always copies down to row 109 irrespective of whats in col E. Any ideas on how to fix this? Thanks LiAD LastRow = Range("E" & Rows.Count).End(xlUp).Row If LastRow 109 then LastRow = 109 End If Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & LastRow), Type:=xlFillDefault |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy to row xx
Check whether one of the below would work for you
Sub Macro1() Dim lnglastrow As Long lnglastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub Sub Macro2() Dim lnglastrow As Long lnglastrow = ActiveSheet.Range("E11").End(xlDown).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, I had hoped the following code would copy the formulas from row 11 down as far as there is something in the equilavent row in col E. So for example if E100 contains a number (such as 283095) and E101 is empty then the P11-AC100 would be filled with formulas. At the moment this code always copies down to row 109 irrespective of whats in col E. Any ideas on how to fix this? Thanks LiAD LastRow = Range("E" & Rows.Count).End(xlUp).Row If LastRow 109 then LastRow = 109 End If Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & LastRow), Type:=xlFillDefault |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy to row xx
Hi,
Thanks for helping. Neither of these have stops at row 109, which I need at the moment. It should only fill to row 109 because after that I have data in E but the other cells will use a different formula so hence a different copy mode. Is it possible to incorporate a stop at row 109 into this code? "Jacob Skaria" wrote: Check whether one of the below would work for you Sub Macro1() Dim lnglastrow As Long lnglastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub Sub Macro2() Dim lnglastrow As Long lnglastrow = ActiveSheet.Range("E11").End(xlDown).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, I had hoped the following code would copy the formulas from row 11 down as far as there is something in the equilavent row in col E. So for example if E100 contains a number (such as 283095) and E101 is empty then the P11-AC100 would be filled with formulas. At the moment this code always copies down to row 109 irrespective of whats in col E. Any ideas on how to fix this? Thanks LiAD LastRow = Range("E" & Rows.Count).End(xlUp).Row If LastRow 109 then LastRow = 109 End If Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & LastRow), Type:=xlFillDefault |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy to row xx
Hi,
Thanks for helping. Neither of these have stops at row 109, which I need at the moment. It should only fill to row 109 because after that I have data in E but the other cells will use a different formula so hence a different copy mode. Is it possible to incorporate that into this code? "Jacob Skaria" wrote: Check whether one of the below would work for you Sub Macro1() Dim lnglastrow As Long lnglastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub Sub Macro2() Dim lnglastrow As Long lnglastrow = ActiveSheet.Range("E11").End(xlDown).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, I had hoped the following code would copy the formulas from row 11 down as far as there is something in the equilavent row in col E. So for example if E100 contains a number (such as 283095) and E101 is empty then the P11-AC100 would be filled with formulas. At the moment this code always copies down to row 109 irrespective of whats in col E. Any ideas on how to fix this? Thanks LiAD LastRow = Range("E" & Rows.Count).End(xlUp).Row If LastRow 109 then LastRow = 109 End If Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & LastRow), Type:=xlFillDefault |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy to row xx
Do you mean
Sub Macro1() Dim lnglastRow As Long lnglastRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row If lnglastRow 109 Then lnglastRow = 109 Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastRow) End Sub Sub Macro2() Dim lnglastRow As Long lnglastRow = ActiveSheet.Range("E11").End(xlDown).Row If lnglastRow 109 Then lnglastRow = 109 Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastRow) End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, Thanks for helping. Neither of these have stops at row 109, which I need at the moment. It should only fill to row 109 because after that I have data in E but the other cells will use a different formula so hence a different copy mode. Is it possible to incorporate a stop at row 109 into this code? "Jacob Skaria" wrote: Check whether one of the below would work for you Sub Macro1() Dim lnglastrow As Long lnglastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub Sub Macro2() Dim lnglastrow As Long lnglastrow = ActiveSheet.Range("E11").End(xlDown).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, I had hoped the following code would copy the formulas from row 11 down as far as there is something in the equilavent row in col E. So for example if E100 contains a number (such as 283095) and E101 is empty then the P11-AC100 would be filled with formulas. At the moment this code always copies down to row 109 irrespective of whats in col E. Any ideas on how to fix this? Thanks LiAD LastRow = Range("E" & Rows.Count).End(xlUp).Row If LastRow 109 then LastRow = 109 End If Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & LastRow), Type:=xlFillDefault |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy to row xx
First one doesn't work.
Second one is perfect. Thanks a lot "Jacob Skaria" wrote: Do you mean Sub Macro1() Dim lnglastRow As Long lnglastRow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row If lnglastRow 109 Then lnglastRow = 109 Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastRow) End Sub Sub Macro2() Dim lnglastRow As Long lnglastRow = ActiveSheet.Range("E11").End(xlDown).Row If lnglastRow 109 Then lnglastRow = 109 Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastRow) End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, Thanks for helping. Neither of these have stops at row 109, which I need at the moment. It should only fill to row 109 because after that I have data in E but the other cells will use a different formula so hence a different copy mode. Is it possible to incorporate a stop at row 109 into this code? "Jacob Skaria" wrote: Check whether one of the below would work for you Sub Macro1() Dim lnglastrow As Long lnglastrow = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub Sub Macro2() Dim lnglastrow As Long lnglastrow = ActiveSheet.Range("E11").End(xlDown).Row Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & lnglastrow) End Sub If this post helps click Yes --------------- Jacob Skaria "LiAD" wrote: Hi, I had hoped the following code would copy the formulas from row 11 down as far as there is something in the equilavent row in col E. So for example if E100 contains a number (such as 283095) and E101 is empty then the P11-AC100 would be filled with formulas. At the moment this code always copies down to row 109 irrespective of whats in col E. Any ideas on how to fix this? Thanks LiAD LastRow = Range("E" & Rows.Count).End(xlUp).Row If LastRow 109 then LastRow = 109 End If Range("P11:AC11").AutoFill Destination:=Range("P11:AC" & LastRow), Type:=xlFillDefault |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy method of worksheet class failed: trying to copy a hidden she | Excel Programming | |||
| copy method of worksheet class failed: trying to copy a hidden she | Excel Programming | |||
| Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
| copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
| EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
Linear Mode
