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:

DstWkb.Worksheets("Sheet2").Range("A1:A144").EntireRow.Copy Destination:=DstWkb.Worksheets("Report").Range("A65536").End(xlUp).Offset(1, 0)

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.

 Column A      Column B
1  dog          animal
2  pig          animal
3  apple        fruit
4  pear         fruit
5  ford         car
6  chevy        car
7  sofa         furniture

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).

Dim mySheet as String
With DstWkb
    mySheet = Application.WorksheetFunction.VLookup(.Sheets("Sheet2").Range("A150").Value, .Sheets("Sheet3").Range("A:B"), 2, FALSE)
    .Sheets("Sheet2").Range("A1:A144").EntireRow.Copy _
        Destination:=.Sheets(mySheet).Range("A65536").End(xlUp).Offset(1, 0)
End With

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:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim mySheet As String

    mySheet = Application.WorksheetFunction.VLookup(.Sheets("Masterlist").Range("I2:I350").Value, .Sheets("Lookup").Range("B:C"), 2, False)
    .Sheets("Masterlist").Range("A1:A144").EntireRow.Copy _
        Destination:=.Sheets(mySheet).Range("A65536").End(xlUp).Offset(1, 0)


End Sub

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

mySheet = Application.WorksheetFunction.VLookup(.Sheets("Masterlist").Range("I2:I350").Value, .Sheets("Lookup").Range("B:C"), 2, False)

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DstWkb As Workbook
Dim mySheet As String
Set DstWkb = "Required Reports by Region and Date.xls"

With DstWkb
    mySheet = Application.WorksheetFunction.VLookup(.Sheets("Masterlist").Range("I2:I350").Value, .Sheets("Lookup").Range("B:C"), 2, False)
    .Sheets("Masterlist").Range("A1:A144").EntireRow.Copy _
        Destination:=.Sheets(mySheet).Range("A65536").End(xlUp).Offset(1, 0)
End With

End Sub

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:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DstWkb As Workbook
Set DstWkb = "Required Reports by Region and Date.xls"

With DstWkb
     .Sheets("Masterlist").Range("A1:A144").EntireRow.Copy _
        Destination:=.Sheets(.Sheets("Masterlist").Range("A150").Value).Range("A65536").End(xlUp).Offset(1, 0)
End With
End Sub

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):

Set DstWkb = Workbooks ("Required Reports by Region and Date.xls")

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:

DstWkb.Worksheets("Sheet2").Range("A1:A144").EntireRow.Copy Destination:=DstWkb.Worksheets("Report").Range("A65536").End(xlUp).Offset(1, 0)

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.