VLOOKUP copy worksheet to workbook

[Minsterhol]

I have 2 workbooks called Master and State. On the Master workbook, there are 4 worksheets (sheet1, sheet2, sheet3, Information). On the State workbook, there are 50 sheets named after each state (Alabama, Alaska, Arkansas, etc.).
I am trying to create a macro that will copy the correct state worksheet into the Master workbook based on the VLOOKUP. So in the Master workbook, if I select company X in Michigan, I want the Michigan worksheet copied to the Master workbook.

 

[venkat1926]

1. "So in the Master workbook, if I select company X in Michigan,"

in which the name Michigan is availble in master workbook

2. "I want the Michigan worksheet copied to the Master workbook. "

in which sheet of the master workbook do you want the michigan data to be copied

3. if another state is clicked for e.g. texas do you want that data is to be copied in the same sheet
as michigan data (of course below the data) or do oou want it in different sheet of master workbook

4. if data of different sheets are copied in different sheets of master workbook(you needn50 sheets
in master workbook) do you want thsse sheets to be renames as state name.

 

[Minsterhol]

Only one state tab can be selected for each company. If company XYZ is selected on the Master workbook and their address is located in Michigan, just the Michigan tab from the State Workbook should be copied to the Master workbook so I have Sheet1, Sheet2, Sheet3, Information, and Michigan. The tab should be called Michigan on the Master workbook. THANKS!