• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

MS Excel Visual Basic Macro Help needed

Hi Experts, Newbie to Visual Basic here:

I need help with a macro please.  Each day I will receive a spreadsheet and load it onto the second tab "From Vendor" of the attached workbook.  The amount of lines could vary anywhere from 2 to 50 so I need the macro to be dynamic.  I need to transfer data from the second tab to the appropriate columns on the first tab "Shipment".  The Header Row needs to stay hard coded and the cells highlighted in blue need to stay hard coded and need to be copied down x number of lines depending on how many rows are on the "From Vendor" tab.  I have recorded a macro to pull the information from the second tab to the first tab but I don't know how to get it to repeat for n line nor copy down the hard coded cells n line.  Can someone help?

Thanks,
Greg
0
faclogistics
Asked:
faclogistics
  • 2
  • 2
  • 2
1 Solution
 
SteveCommented:
Need attachment :)
If I had a dollar for each missed attachment :)
0
 
faclogisticsAuthor Commented:
Please try it now.
lits-template-2.xls
0
 
SteveCommented:
Please unprotect the shipments tab and repost.
I cannot be certain to replicate cells contents and formats without access to those cells.
Thanks.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tdlewisCommented:
It's a little hard to navigate your Shipment worksheet to find all the right locations for the data from the "From Vendor" worksheet. Here's the basic layout of the routine you need. Just add more statements to copy the other values. (Note that column A=1, B=2, etc., AA=27, AB=28, etc.)
Sub TransferFromVendorToShipment()
Dim fromRow As Long
Dim fromSheet As Worksheet
Dim rng As Range
Dim toRow As Long
Dim toSheet As Worksheet

    Set fromSheet = Sheets("From Vendor")
    Set toSheet = Sheets("Shipment")
    ' Find the first row in "Shipment" that does not have a value in column C.
    toRow = 2
    Do While Not IsEmpty(toSheet.Cells(toRow, 3).Value)
        toRow = toRow + 1
    Loop
    ' Loop through rows in "From Vendor" until there is not a value in column B.
    fromRow = 2
    Do While Not IsEmpty(fromSheet.Cells(fromRow, 2).Value)
        toSheet.Cells(toRow, 28).Value = fromSheet.Cells(fromRow, 2).Value
        ' Add more statements like the one above to copy all the fields to the correct column
        toRow = toRow + 1
        fromRow = fromRow + 1
    Loop
End Sub

Open in new window


Note that you can say things like "toSheet.Cells("C3"), but it's easier to use numeric values from the columns when you need to iterate over a bunch of rows.

Hope that helps.
0
 
faclogisticsAuthor Commented:
I am reposting the attachment with the cells unlocked.
lits-template-2.xls
0
 
tdlewisCommented:
Thanks for the unlocked workbook. I'm still having trouble matching column names on "From Vendor" to "Shipment". Double check the ones that I filled in below and match up the ones that were not obvious to me (marked with ***). Hope this helps.
Sub TransferFromVendorToShipment()
Dim fromRow As Long
Dim fromSheet As Worksheet
Dim rng As Range
Dim toRow As Long
Dim toSheet As Worksheet

    Set fromSheet = Sheets("From Vendor")
    Set toSheet = Sheets("Shipment")
    ' Find the first row in "Shipment" that does not have a value in column C.
    toRow = 2
    Do While Not IsEmpty(toSheet.Cells(toRow, 3).Value)
        toRow = toRow + 1
    Loop
    ' Loop through rows in "From Vendor" until there is not a value in column B.
    fromRow = 2
    Do While Not IsEmpty(fromSheet.Cells(fromRow, 2).Value)
        ' *** Transptn Plang Date
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 1).Value
        ' Name of the ship-to party => DEST_NAME
        toSheet.Cells(toRow, 28).Value = fromSheet.Cells(fromRow, 2).Value
        ' Customer PO => PO_PRI_REF
        toSheet.Cells(toRow, 8).Value = fromSheet.Cells(fromRow, 3).Value
        ' Location of the ship-to party => DEST_CITY
        toSheet.Cells(toRow, 31).Value = fromSheet.Cells(fromRow, 4).Value
        ' Ship-to State => DEST_STATE
        toSheet.Cells(toRow, 32).Value = fromSheet.Cells(fromRow, 5).Value
        ' Ship-to ZIP => DEST_POSTALCODE
        toSheet.Cells(toRow, 33).Value = fromSheet.Cells(fromRow, 6).Value
        ' *** Delivery
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 7).Value
        ' *** Deliv. date(From/to)
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 8).Value
        ' Total Weight => ITEM_WEIGHT
        toSheet.Cells(toRow, 56).Value = fromSheet.Cells(fromRow, 9).Value
        ' *** Full Pallets
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 10).Value
        ' *** FP Unit of Measure
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 11).Value
        ' *** Partial Pallets
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 12).Value
        ' *** PP Unit of Measure
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 13).Value
        ' *** Total Partials
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 14).Value
        ' *** Shipment
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 15).Value
        ' *** Sales Order No
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 16).Value
        ' *** Order Type
        toSheet.Cells(toRow, -1).Value = fromSheet.Cells(fromRow, 17).Value
        toRow = toRow + 1
        fromRow = fromRow + 1
    Loop
End Sub

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now