Assigning cell value from another workbook to my code

Question

Basically what I want to achieve is assigning the cell value from the other workbook into my code.

Customers send in a spreadsheet of goods they want to order and this spreadsheet is used for more than one purpose so it has many columns which are not needed for my purpose.
So I created the macro to look up and give me the value from certain columns and spit out a CSV file.
I then created a spreadsheet of my own with the column values all mapped out, I am trying to get the coding to lookup the cell value in my spreadsheet and knows which columns to be looking up on the customer spreadsheet.
I want it so I can just go in and change the values in the cells on my spreadsheet instead of having to go into the coding and change the columns value in my code.

ShipToSiteID = Application.WorksheetFunction.Trim(Range("Q" & counter))
AltShipTo1 = Application.WorksheetFunction.Trim(Range("G" & counter))
AltShipTo2 = Application.WorksheetFunction.Trim(Range("H" & counter))
AltShipToCity = Application.WorksheetFunction.Trim(Range("I" & counter))
'AltShipToState = Application.WorksheetFunction.Trim(Range("I" & counter))
 AltShipToZip = Application.WorksheetFunction.Trim(Range("J" & counter))
AltShipToCountry = "UNITED KINGDOM"
RefNumber = counter - 1
    UserId = LCase(Environ("username"))

ShipToSiteID = Replace(ShipToSiteID + AltShipToZip, " ", "")

Sheets(2).Select

    Range("A" & counter - 1) = LineType & comma & RefNumber & comma & QName & comma & BlanketID _
                                        & comma & BillToSiteID & comma & ShipToSiteID & comma & ContractID _
                                        & comma & PONumber & comma & CaseID & comma & ShipVia & comma & RequiredDate _
                                        & comma & Comments & comma & Priority & comma & TerminalID & comma _
                                        & AltContactFirst & comma & AltContactLast & comma & AltPhone & comma _
                                        & AltShipTo1 & comma & AltShipTo2 & comma & AltShipToCity & comma _
                                        & AltShipToState & comma & AltShipToZip & comma & AltShipToCountry _
                                        & comma & UserId & comma

So at the moment in the code where it is pointing to the column Q,G,H,I,J. I want to change this so it looks up a value in another workbook. MyWorksheet lets say, Sheet(2) range ("H5") has the value Q in it and I want my code so the ShipToSiteID points at that cell and uses the value of that cell. So if the column parameter every changes I can go into MyWorksheet and change it rather than going through my coding and changing it. — user3581948, May 01 '14 at 15:39

score 1 · Answer 1 · edited May 23 '17 at 10:26

A few suggestions...

To reference a value in workbook2 on workbook1, the easiest way to do this would be to copy the worksheet from workbook2 to workbook1. Then you can reference the value by typing "=" in the cell, and clicking on the cell that you want to reference.

Referencing in VBA code gives you some more flexibility. I'd suggest checking this good SO question and this one for more info. This may be more along the lines of what you want.

Unfortunately, if you reference a value from workbook2 on workbook1, there is no easy way to change the value of workbook2 from workbook1.

Assigning cell value from another workbook to my code

1 Answers1