Creating a Macro that Copies Data While Moving Down Spreadsheet

[r0ckstarck]

I'm very new to VBA and can't figure out how to build this. I need a macro that will copy data from a table, paste it to another location, run three already made macros, then move onto the next cell. I have been able to get a macro to work for just one line of the table but haven't been able to make it cascade down the table. An example for row 2 of the table is this

-Take Cells A9-E9 and copy them to A2-E2

-Run 3 macros that are saved in the workbook. These macros sort tables that I have on sheets 2-4 based on the data A1-M2 (applyGreater, applyLesser, applyPush).

-Copy Cells G6-K6 and paste them as values to G9-K9 (G6-K6 are aggregate functions that update based on the data in the Sheet2-4 Tables.

-Move down to row 3 of the table and repeat the process using data in that row. I need it to keep doing this until the table ends (Row 3609).

I hope I was clear with my instructions. Thank you for any and all help!

Here is a link to the spreadsheet: https://www.dropbox.com/s/ukfpgjul8swwmea/Copy%20of%20Testing%20Data%20OU.xlsm?dl=0

 

[Fluff]

Hi & welcome to MrExcel
Try this

Sub CopyData()

    Dim Cl As Range
    
    With Sheet1
        For Each Cl In .Range("A8", .Range("A" & Rows.Count).End(xlUp))
            .Range("A2:M2").Value = Cl.Resize(, 13).Value
            Call applyGreater
            Call applyLesser
            Call applyPush
            .Range("G6:K6").Copy Cl.Offset(, 7).Resize(, 5)
        Next Cl
    End With
    
End Sub

I also suspect that your "apply" macros could give false results as you haven't specified, what sheet the ranges are on. Try

Sub applyGreater()

    Dim rngTable As Range
    Set rngTable = Sheet2.ListObjects("Table2").Range

    With Sheet1
        rngTable.AutoFilter Field:=1, Criteria1:=">" & .Range("G2").Value, Operator:=xlAnd, Criteria2:="<" & .Range("F2").Value
        rngTable.AutoFilter Field:=2, Criteria1:=">" & .Range("I2").Value, Operator:=xlAnd, Criteria2:="<" & .Range("H2").Value
        rngTable.AutoFilter Field:=3, Criteria1:=">" & .Range("K2").Value, Operator:=xlAnd, Criteria2:="<" & .Range("J2").Value
        rngTable.AutoFilter Field:=4, Criteria1:=">" & .Range("M2").Value, Operator:=xlAnd, Criteria2:="<" & .Range("L2").Value
        rngTable.AutoFilter Field:=5, Criteria1:=">" & .Range("E2").Value, Operator:=xlAnd, Criteria2:=">" & .Range("A3").Value
        rngTable.AutoFilter Field:=6, Criteria1:="<>" & .Range("B3").Value, Operator:=xlAnd, Criteria2:=">" & .Range("A3").Value
    End With

    Set rngTable = Nothing
End Sub

 

[sairampappu]

Hi,

I am new to VBA and trying to build a macro. I need to select a particular entire column which has some units for example say distance 72.4 Km/h (this value may varied and is applicable for single cell). I need to strip off the units and get only 72.4 and that should be copied to another workbook.

1. If there is a column R1C1 has a heading "distance", in R2C1 has some value 72.34 Km/h. Now I need to get take out the units and get 72.34, paste the same value in another work book.and this should happen for entire column.

Can Someone help me out.

Thanks,
Sai

 

[Fluff]

@sairampappu
Welcome to the board.
As your post has nothing to do the the original question, could you please start your own thread.

 

[sairampappu]

Hi Fluff,

I have created a thread on the name " [h=2]"How to strip of the characters in a cell and copy the filtered data into another workbook"[/h]

 

[Fluff]

Cheers