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]

First variation: try

Sub test2()
Range("B4:H4").AutoFill Destination:=Range("B4:H" & Range("A1").Value)
With Range("B5:H" & Range("A1").Value)
    .Value = .Value
End With
End Sub

Second variation: have a go at this yourself. Recording a macro should get you started.

 

[JV0710]

Thank you sir

I will give it a try

Originally I was doing this by recording a macro.I was getting stuck in 2 places.
First was how to determine the last row - so I just copied everything down to row 2000 ( That's as far as I think the rows will go for this task )

and second was how to select ONLY cells that had formula ( The reason is that I want to use this macro on different sheets, but their layouts are slightly different - I did not want manually select the cells and record a different macro for every sheet ).

But what you have given me is very valuable and I will use it to work on a solution myself.

Thank you once again for your help.

JVN

 

[JV0710]

Hi VoG

Just to let you know . . .

I have used what you have given and applied it to my spreadsheet.

I still do not know how to automatically select only cells that have formula, so I selected each range separately - and it worked out just fine.

Here is what I ended up with:

Sub RefreshAndSortRollup()
'
' RefreshAndSortRollup Macro
' This Refreshes formulae and sorts data on the Supplier Roll-up page. the sort is based on column E
'

'
    ActiveSheet.Unprotect
    Dim LR As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Range("A6:D6").AutoFill Destination:=Range("A6:D" & LR)
Range("F6:Q6").AutoFill Destination:=Range("F6:Q" & LR)
Range("T6:X6").AutoFill Destination:=Range("T6:X" & LR)
Range("AA6:AE6").AutoFill Destination:=Range("AA6:AE" & LR)
Range("AH6:AL6").AutoFill Destination:=Range("AH6:AL" & LR)
With Range("A7:AL" & LR)
    .Value = .Value
End With
    Range("A8:AL" & LR).Select
    ActiveWorkbook.Worksheets("Supplier Roll-up").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Supplier Roll-up").Sort.SortFields.Add Key:=Range( _
        "E8:E" & LR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Supplier Roll-up").Sort
        .SetRange Range("A8:AL" & LR)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFiltering:=True
    End Sub

Thank you

JVN

 

[JV0710]

Hi

Running the above Macro in excel 2007 works fine, bt if I use excel 2003 I get the following error:

 Run-time error '438'
Object doesn't support this property or method

when I debug the following area is highlighted:

    ActiveWorkbook.Worksheets("Supplier Roll-up").Sort.SortFields.Clear

I do not know enough about VB to understand what is wrong . . .

Please help me to correct this error, so that it works in Both excel 2003 AND excel 2007.

Thanks

JVN

 

[VoG]

Try this

Sub RefreshAndSortRollup()
'
' RefreshAndSortRollup Macro
' This Refreshes formulae and sorts data on the Supplier Roll-up page. the sort is based on column E
'

'
Dim LR As Long
ActiveSheet.Unprotect
LR = Range("E" & Rows.Count).End(xlUp).Row
Range("A6:D6").AutoFill Destination:=Range("A6:D" & LR)
Range("F6:Q6").AutoFill Destination:=Range("F6:Q" & LR)
Range("T6:X6").AutoFill Destination:=Range("T6:X" & LR)
Range("AA6:AE6").AutoFill Destination:=Range("AA6:AE" & LR)
Range("AH6:AL6").AutoFill Destination:=Range("AH6:AL" & LR)
With Range("A7:AL" & LR)
    .Value = .Value
End With
Range("A8:AL" & LR).Sort Key1:=Range("E8"), Order1:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
    False, AllowFiltering:=True
End Sub

 

[JV0710]

Thank you Peter


It works now . . .

I really need to read up and get more practice on VB.

It's very powerful when used right.

Thanks again

Joe