CODEWITHSUNDEEP
vbaexcelspreadsheet
Bradley
Bradley

Reputation: 55

Macro loop to pull data from sheet to sheet stopping when no data resent and not entering 0

I am trying to get the data from rows G,H, and I from row 15 down (stopping when there is no data present)from "order sheet", to be pasted in a sheet called “Data” in columns M,Q and R from row 8 down.

I have tried recording a macro, however as this data changes from day to day, my recorded macro stays the same and I don’t have the know how to place an IF formula in macro to stop it when there is no data in origin cell. One day the data will be from row 15 to 50 in the order sheet and the next 15 to 71 (always starts at 15). When I do the macro for say 200 rows so it does cover all rows no matter how many rows it may be, itll then places a zero in the data sheet when nothing present in order sheet cell. What I would like is for it to leave blank if there is no number in the order sheet cell.

e.g. “Order sheet” column paste to “Data sheet” (starting from row 15 down on order sheet and row 8 down on data sheet) G to M / H to Q / I to R

e.g. G15 to M8 / H16 to Q9 / I17 to R10

See the basic recorder macro as an idea.

Thanks in advance

ActiveWindow.SmallScroll Down:=-15
Range("M8").Select
ActiveCell.FormulaR1C1 = "='Order Sheet'!R[7]C[-6]"
Range("M8").Select
Selection.AutoFill Destination:=Range("M8:M159"), Type:=xlFillDefault
Range("M8:M159").Select
ActiveWindow.SmallScroll Down:=-135
Range("Q8").Select
ActiveCell.FormulaR1C1 = "='Order Sheet'!R[7]C[-9]"
Range("Q8").Select
Selection.AutoFill Destination:=Range("Q8:Q159"), Type:=xlFillDefault
Range("Q8:Q159").Select
ActiveWindow.SmallScroll Down:=-132
Range("R8").Select
ActiveCell.FormulaR1C1 = "='Order Sheet'!R[7]C[-9]"
Range("R8").Select
Selection.AutoFill Destination:=Range("R8:R159"), Type:=xlFillDefault
Range("R8:R159").Select
ActiveWindow.SmallScroll Down:=-123
Range("I3").Select

Upvotes: 0

Views: 160

Answers (1)

arcadeprecinct
arcadeprecinct

Reputation: 3777

Here is a way to copy all values from G15 downwards to M8 downwards

Dim sourceSht As Worksheet 'sheet you want to copy from 
Dim targetSht As Worksheet 'sheet you want to copy too
Dim sourceRng As Range 'what you want to copy
Dim targetRng As Range 'where you want to copy to
Dim lastRow As Long 'the last row that holds data

Set sourceSht = Sheets("Order Sheet")
Set targetSht = Sheets("Data")

With sourceSht
    lastRow = .Cells(.Rows.Count,7).End(xlUp).Row '7 is the column number
End With

Set sourceRng = sourceSht.Range("G15:G" & lastRow)
Set targetRng = targetSht.Range("M8").Resize(sourceRng.Rows.Count, 1) ' Range("M8:M"& lastRow - 7) would also be possible
targetRng.Value = sourceRng.Value

You might want to introduce variables to hold the column indices and row numbers (like 15 and 8). You could also have the ranges contain more than one column to copy more data at once.

Upvotes: 1

Related Questions