VBA - a) copying data from one workbook to another, to include b) hyperlinks, formulas

[manc]

Good evening Demigods,

Bit of a complicated one this...

I am looking for a VBA alternative, or a more practical suggestion for the following:

1st workbook - 'Workbook A' - we would call it our job book, and basically provides a sequential number each time a value is entered in column B. We then use this number as our job number.

Row 1 - TEST ROW with cell A1 = 10001, B2 = "TEST", C3 = "GO"
Cell A2 contains the formula =IF(B2="","",A1+1)
Cell A3 contains the formula =IF(B3="","",A2+1)
And so on and so on...

Column B contains a dynamic drop-down list that could in theory contain the names of 100's of Excel workbook templates.
When a user selects a line item from the drop-down list, say in cell B2, cell A2 is populated with 10002.
The same applies to cell A3 when a value is chosen from the drop-down list in cell B3.
And so on and so on...

Column C, cell C2 (for example) contains the hypothetical formula =IF(B2="","",HYPERLINK('D:\ExampleFolder\['&B2&'.xlsx]Sheet1!) **
In this instance, B2 = Customer_C, so Customer_C.xlsx is opened.

** just for example purposes.

The same applies to C3, C4 etc - depending on the value chosen, depends on the workbook opened when "GO" is clicked.

Once the target workbook is opened, I want the value from Workbook A, Column A (let's use cell A2 for this example = 10002) displayed in cell A10 in Customer_C workbook, and then for Workbook A to save and close.

I know this is a lot. Any help, ideas, suggestions greatly appreciated as always.

Best regards
manc

 

[My Aswer Is This]

And your wanting all this done without using Vba. Is that correct?

You said:

"I am looking for a VBA alternative"

 

[manc]

Sorry - got caught up in all the excitement ? I meant to say that I want to do it all using VBA.

Regards
manc