3

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
Luuklag
  • 3,897
  • 11
  • 38
  • 57
Bradley
  • 55
  • 7
  • 3
    Google `how to find last row` and `for loop`. – findwindow Jun 21 '16 at 16:05
  • 1
    Also, I ***highly*** suggest reading through, and applying, [How to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – BruceWayne Jun 21 '16 at 16:24
  • Thank you @BruceWayne, I shall save this thread and once I start to understand the basics and get some sort of understanding i shall return to this. Do you have any links to more basic understanding which will come in useful for a complete novice. I have just found about macro and thought I could make use on them in order to make my job easier = early finish ;) thanks – Bradley Jun 22 '16 at 14:05

1 Answers1

1

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

Vbasic4now
  • 579
  • 3
  • 6
  • 33
  • Thank you for your help @Vbasic4now. I am just getting my head around the above macro. Would it be possible to explain it slightly to help me understand it better? so that I can edit it to the reordered macro. Thanks – Bradley Jun 22 '16 at 14:00
  • @Bradley the 1st 2 lines define which sheet the data is being pulled form (sourceSheet) and where is is being pasted (destSheet). the third line is definefining a variable named lMaxRows as the last used row in the desination sheet. the last line is then saying make the cell in column A in the row after the last used row on the destination have the same value as the cell M1 in the source sheet. – Vbasic4now Jun 22 '16 at 18:29