Need Macro to Copy Data to last row

[JV0710]

Good Day

I have a spreadsheet with Data in row 4, from Column A:H.
Column A is data entry and the rest are formula ( either vlookup or calculations )

After I have entered data in column A ( let's say about 10 rows down - It could be more),
I want to run a macro that will copy the formula from B4:H4 down to where the entries in column A stop. Then it must select all the data below row 4 and "copy_paste_Special_Values".


Please can anyone help me to do this

thanks

JVN

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B4:H" & LR)
    .Value = .Value
End With
End Sub

 

[texasalynn]

something like this?

Sub PasteValue()
Dim LR As Long
LR = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Range("B4:H" & LR).Copy
Range("B4:H" & LR).PasteSpecial Paste:=xlPasteValues
End Sub

 

[JV0710]

Thanks for the reply guys

I will try these suggestions and let you know.

JVN

 

[JV0710]

Hi VOG, Texaslynn

I'm sorry . . . I may not have explained clearly.

In Column A (from A4 ) I can enter data.

The vlookup and calcs are in range B4:H4.

If I enter data in A5, there will be nothing in B5:H5

I want to then enter data in column A ( let's say A5:A10 ).

I want to run a macro then, to: select the vlookup and calcs ( everything in range B4:H4) and copy down to the last row ( In this case row 10 )

After copying B4:H4 down to row 10, It must then select the range B5:H(the last row ) and copy_past_special.

Row 4 must still have the original vlookup and calcs in range B4:H4

I hope that is clearer . . . If I write any more I will confuse myself

Thanks again for your help


JVN

 

[texasalynn]

try this

Sub PasteValue()
Dim LR As Long
LR = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Range("B4:H4").Copy Destination:=Range("B4:H" & LR)
Range("B5:H" & LR).PasteSpecial Paste:=xlPasteValues
End Sub

 

[JV0710]

Hi Texaslynn

That does not work . . .

I entered values in cloumn A up to A11.

When I run the Macro I get an error ( Just a big X and the number 400 ) and the range B4:H5 is highlighted, but nothing has been copied.

Thanks

JVN

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("B4:H4").AutoFill Destination:=Range("B4:H" & LR)
With Range("B5:H" & LR)
    .Value = .Value
End With
End Sub

 

[JV0710]

Thank you

That one works

Thank you very much

JVN

 

[JV0710]

Hi VoG

Thank you very much for your help yesterday - I really do appreciate it.

I now want to have two other variations of this macro.

First: Variation -
In Cell A1 I enter a number ( This will be the row that I want the Data in row 4 to copy to )
I do not enter any data on the spreadsheet
The Macro must : select the range A4: H4 and copy to the row number shown in cell A1.
Then Select all rows below row 4 ( up to row number shown in cell A1 ) and copy_paste_special


Second: Variation -
In row 4:
Column A - has formula,
Column B - I enter data,
Columns C:F - have formula
Column G - I enter data.
Columns H: K - Have formula
I will enter data in column A and column G ( let’s say up to row 15 )
The macro must now use column B to determine the last row,
Then select only the cells with formula in row 4 and copy down to the last row.
Then select all data from row 5 to last row and copy_paste_special.
Then sort the data from row 5 to last row (sort on column B - ascending).

Thank you for your help

JVN