Using the data in a cell as a worksheet reference in vba

[Yeoman.jeremy]

Hi there,
I've created in my workbook a simple sheet to help the users input new products into the system (as opposed to them going into the different worksheet where there are formulas)

It has a cell with a validated list of all the different suppliers (also the name of the sheets for each supplier's products). also cells with: the product name, cost, price etc etc etc

Once all this data has been entered into the cells, they press a command button, which will look at the supplier name, and enters the information to their relative cells in the appropriate sheet.

How can I code it to duplicate the information to the sheet with the same name as the information in the cell?

 

[Paradigm_Shift]

Simple code (here, A2 is the cell with the dropdown, and the "master" sheet the user inputs information on is Sheet1. This code inserts the value from the dropdown list into A1 in whichever sheet is selected from the dropdown. But you can make it insert what ever you need it to. Let me know if you have further questions regarding how to input other information...

Sub Button1_Click()
 Sheets(Range("A2").value).Range("A1")=Sheets("Sheet1").Range("A2").value
End Sub

 

[Yeoman.jeremy]

Hi again,

Just realised another complication, which yoiu'll probably be able to solve in a split second

There is a large list of the products on this page, is there a way for the destination cell to be the next cell in the column that doesn't have anything in it?

Cheers

 

[Paradigm_Shift]

Try a version of this:

        Sheets(Range("A2").Value).Range("A1").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Sheets("Sheet1").Range("A2")

If that doesn't work for you, try removing the third line...I don't know exactly how your sheets are set up...

 

[Yeoman.jeremy]

I pasted the coding, and changed the cells inclued, but returned the error
Select Method of Range Class failed