MACROS - Copy info from another spreadsheet, selected from a drop-down list

[LitoII]

Hi.

I have a workbook with 150+ spreadsheets. In another spreadsheet I've managed to make the drop-down list with all the names of the spreadsheets in an A1 cell. When something from the drop-down menu is selected (there will be a spreadsheet with the very same name), the information from the spreadsheet with the same name as the selected word in the A1 cell has to get copied in the spreadsheet with the drop-down menu.

I hope I have explained my issue quite clearly. If not - please, ask!

Thank you very much in advance.

Mike

 

[John Davis]

Maybe:

Sub LitoII()

Dim ws As Worksheet
Dim x As String

Set ws = ActiveSheet

x = Range("A1").Value

Sheets(x).UsedRange.Copy ws.Range("A" & Rows.Count).End(3)(2)


End Sub

 

[LitoII]

Thank you for the quick reply, but when I am to run it, nothing happens (I've only copied it in a new module and clicked CTRL+S before that)

Also, is it possible to copy only the cells from A4 to E63 from the respective spreadsheet?

 

[John Davis]

It worked when I tested it.

Sub LitoII()

Dim ws As Worksheet
Dim x As String

Set ws = ActiveSheet

x = Range("A1").Value

Sheets(x).Range("A4:E63").Copy ws.Range("A" & Rows.Count).End(3)(2)


End Sub

Not sure why it's not working? This modification would copy the range you requested.

 

[LitoII]

hmm, it's odd, still nothing happens :/

Actually, does the 'Copy' command also paste the selected text? And does the 'Value' describe the argument as text as well?

 

[John Davis]

The copy command with a space should paste to the destination. You can try without the Value and see what happens. I the problem resides in your Range("A1") or your Sheet names. See my PM.

 

[John Davis]

hmm, it's odd, still nothing happens :/

Actually, does the 'Copy' command also paste the selected text? And does the 'Value' describe the argument as text as well?

Mike:

I received your email and tested the macro with your sample data. See my reply. I think it's just a matter of clarity of what you want to happen. As it is written the macro works.

 

[John Davis]

hmm, it's odd, still nothing happens :/

Actually, does the 'Copy' command also paste the selected text? And does the 'Value' describe the argument as text as well?

OK, Mike. I thought it was just a misunderstanding, try this:

Sub LitoII()

Dim ws As Worksheet
Dim x As String

Set ws = ActiveSheet

x = Range("A1").Value

Sheets(x).Range("A4:E63").Copy ws.Range("A4" )

End Sub

 

[LitoII]

Thank you very much, John - your replies were real help for me : )

 

[John Davis]

You're welcome, glad to help out, and thanks for the feedback.