Choosing spreadsheet for paste

**jomili** · 02-02-2010, 04:55 PM

I have a routine that copies from one worksheet ("Sheet2) and pastes into another ("Report") just fine:

Please Login or Register  to view this content.

However, I want to be able to paste to a variable worksheet. In other words, if my user chooses (from a dropdown) "Apple", I want to paste into "Fruit", and if he/she picks "Pig" I want to paste into "Animals". How do I write the variable into my little copy/paste routine?

**Paul** · 02-02-2010, 05:02 PM

Is the drop-down a combobox from the ActiveX/ControlsToolbox? Or is it from Data Validation?

Also, you'd need to create a lookup table, or within the macro itself create a list of the appropriate sheet name based on the dropdown. Apple -> Fruit, Ford -> Cars, Pig -> Animals, etc.

**jomili** · 02-02-2010, 05:04 PM

I haven't made it yet, so could go either way, but was assuming just a simple data validation dropdown. Would there be a benefit to using a combobox? There are only 7 choices.

I'm just starting this, so am way open to suggestions.

**Paul** · 02-02-2010, 05:20 PM

Somewhere in your workbook outside the sheets/areas you're copying and pasting - let's say on Sheet3 - create a lookup table with two columns. First column will have the seven drop-down values, second column will have the sheet name you want returned based on the value selected, e.g.

Please Login or Register  to view this content.

The following code assumes your lookup table (shown above) would be on Sheet3 in columns A:B, and your drop-down box is in cell A150 on Sheet2 with the rest of the data you're copying (from rows 1:144).

Please Login or Register  to view this content.

Hope that helps.

**jomili** · 02-02-2010, 05:53 PM

Paul,

I'm having a bit of difficulty; Here's how I adapted your code:

Please Login or Register  to view this content.

I'm getting a compile error: Invalid or Unqualified reference at the (.Sheets( part of the code:

Please Login or Register  to view this content.

I deleted the With and End With lines, because putting them in kept giving me a type mismatch (see first attempt code)

Please Login or Register  to view this content.

Also, on my dropdown, the options are Region 1, Region 2, etc. The worksheets are also named Region 1, Region 2, etc. Do I still need a lookup table?

**Paul** · 02-02-2010, 06:06 PM

You stated that if the user picked 'Apple' then it would go to sheet 'Fruit', or for 'Pig' it would go to 'Animal'. That's why I added the lookup to the code. If you're not going to lookup a value, then just set a string variable equal to the cell value of the drop-down.

As for the With/EndWith, if you don't use them you'll have to fully qualify all workbook and sheet references.

Finally, when you did try to adjust the VLookup, you put in a range of cells to lookup. It is only meant to handle one lookup value, not a range like I2:I350.

Perhaps:

Please Login or Register  to view this content.

Assuming your codes (Region 1, Region 2, etc.) are in cell A150 on the Masterlist sheet.

**jomili** · 02-02-2010, 06:15 PM

Sorry Paul,
You're right: I DID say "Apple" and "Fruit". Afterwards I reconsidered, as it seemed easier to go with a simpler naming convention.

I tried your new code, corrected one problem (my problem) in naming my worksheet (should be like this):

Please Login or Register  to view this content.

then before I ran it again I noticed that it was just copying and pasting into the same worksheet, not selecting any other one. Did you leave part of your code out?

Thanks for your help.

**Paul** · 02-02-2010, 06:31 PM

Your original code was:

Please Login or Register  to view this content.

The source and destination workbooks are the same. The worksheets are different. In my last code, the source and destination workbooks are still the same (DstWkb), but the sheets differ. Source sheet is Masterlist("A1:A144"), while Destination sheet is .Sheets(drop-down value).first_empty_row_in_column_A.

The drop-down value is dstwkb.worksheets("masterlist").Range("A150"). Change that sheet and cell reference to match the sheet/cell where your drop-down is located.

**jomili** · 02-03-2010, 09:07 AM

I think there may be one problem; my dropdown isn't in a particular cell, but in a range (IE all of column F). So, I'll be typing in the cells on row 10. The last cell I fill will be the dropdown. When I choose that dropdown, I want the row containing that choice to be copied to another sheet which is named (already) with that dropdown choice.