CODEWITHSUNDEEP
vbaexcel
christina
christina

Reputation: 11

Filling cells based on values in range

I need help writing a sub routine for my program in excel. What I am trying to do is take information from one sheet (Bar code master) and fill it in to another sheet(Template). I recorded a macro and that got me to the point of copying and pasting info. On the bar code master sheet I have the range of bar codes listed with the beginning number in a cell in column A, and the ending number in a cell in column B. I want to take the starting number and copy it into cell A4 on the template and fill it down until it equals the ending number of the bar codes. Also on the bar code master the location of where it takes the starting number I want it to always be the last populated cell in Row A and B. Here is what I have so far. please help. Thanks

Sub Macro9()

// Macro9 Macro
// Macro recorded 7/30/2010 by Christina 



    Sheets("barcode master").Select
    Range("D648").Select
    Selection.Copy
    Sheets("barcode template").Select
    Range("F1").Select
    ActiveSheet.Paste
    Sheets("barcode master").Select
    Range("E648").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("barcode template").Select
    Range("J1").Select
    ActiveSheet.Paste
    Sheets("barcode master").Select
    Range("F648").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("barcode template").Select
    Range("B1").Select
    ActiveSheet.Paste
    Sheets("barcode master").Select
    Range("A648").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("barcode template").Select
    Range("A4").Select
    ActiveSheet.Paste
    Range("a5").Value = Range("a4") + 1

End Sub

Upvotes: 1

Views: 1382

Answers (1)

Alex P
Alex P

Reputation: 12489

It is not entirely clear from your post what you are trying to achieve so I'll have to make some assumptions...

  • In a workbook you have two sheets named Master and Template
  • In Master column A has the starting number of a bar code and column B has the end number
  • You would like to select the last row in Master column A, paste that value to Template cell A4, and then fill down values until it matches the final cell in column B of Master

Example: Cell A10 has start bar code value of 100 and Cell B10 has an end bar code value of 110. On Template you want cell A4 to start with 100 and the fill down to 110 (i.e. 100, 101, 102, 103 ... 110)

Assuming I have your requirements correct then this code will work:

Sub CopyBarCodes()
'First clear Template sheet of previous values
Worksheets("Template").Range("A:A").ClearContents

'Now select last entry in Master sheet and get start and end bar code numbers
Dim startBarCode As Long, endBarCode As Long
Dim lastRow As Long

With Worksheets("Master")
    lastRow = .Range("A1").End(xlDown).Row
    startBarCode = .Range("A" & lastRow)
    endBarCode = .Range("B" & lastRow)
End With

'Update Template with barcode
With Worksheets("Template")
    .Range("A4").Value = startBarCode
    .Range("A4").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, Stop:=endBarCode, Trend:=False
End With

End Sub

Hope this helps...

Upvotes: 4

Related Questions