Need Macro to Copy Data to last row

JV0710

Active Member
Joined
Oct 26, 2006
Messages
358
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
something like this?

Code:
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

Active Member
Joined
Oct 26, 2006
Messages
358
Thanks for the reply guys

I will try these suggestions and let you know.

JVN
 

JV0710

Active Member
Joined
Oct 26, 2006
Messages
358

ADVERTISEMENT

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

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this

Code:
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

Active Member
Joined
Oct 26, 2006
Messages
358

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Active Member
Joined
Oct 26, 2006
Messages
358
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top