Need Macro to Copy Data to last row

JV0710

Active Member
Joined
Oct 26, 2006
Messages
434
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
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
 
Upvote 0
Thanks for the reply guys

I will try these suggestions and let you know.

JVN
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thank you

That one works

Thank you very much

JVN
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,485
Messages
6,130,938
Members
449,608
Latest member
jacobmudombe

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
Back
Top