Choosing spreadsheet for paste

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

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