CODEWITHSUNDEEP
vbaexcel
Bradley
Bradley

Reputation: 55

Pulling information from one tab to another

I need to populate specific columns in one tab from the original data held in the first tab. I have done a quick reordered VBA code to help anybody understand what I am trying to do in the hope they can refine. I also need the VBA code to stop at the line where the data ends. currently the recorded VBA code pulls down to row 159 yet the data in the original tab stops at row 72. I would have stopped my VBA code at the line where the information ends but this changes from day to day and could be anywhere from row 1 to 158. (If I populate a cell past where the data ends in the original it makes the sheet unusable for other purposes I require it for, even if it is a zero.)

There is data in column A and B, so I was thinking if the macro could say if data is in cell A15 then pull though info from G15 etc?? No data no pull. I know this may seem basic to some but as a novice I am still getting my head around VBA.

Sub Populate_Order()
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: 3

Views: 189

Answers (1)

Vbasic4now
Vbasic4now

Reputation: 589

to find the next empty row try something like this

Dim sourceSheet As Worksheet: Set sourceSheet = ThisWorkbook.Worksheets("sheet1")
Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("sheet2")
lMaxRows = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row

destSheet.range("A" & lMaxRows + 1).Value = sourceSheet.range("M1").Value

this is a simple example of finding the next empty row then setting column A in that row to the value from cell M1 in another sheet

Upvotes: 1

Related Questions