ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy to row xx (https://www.excelbanter.com/excel-programming/433042-copy-row-xx.html)

LiAD
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


Jacob Skaria
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


LiAD
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


LiAD
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


Jacob Skaria
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


LiAD
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



All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com