[SOLVED] Pasting to a Worksheet based on Cell Value

Pasting to a Worksheet based on Cell Value

I'm trying to separate specific data from a Master list into individual sheets. Having trouble specifying the destination sheet based on a cell value from the master list.

Sub TESTT()
    Dim wss As Sheets
    Dim ws As Worksheet, master As Worksheet
    
    Set master = Worksheets("FMASTER")
    Set wss = ThisWorkbook.Worksheets
    Set ws = wss(master.Cells(2, 3).Value) 'Cell C2 Value

    master.Range("A2").Copy
    ws.Paste Destination:=Range("A1")

End Sub

This was just a small test. Once I had it working, I would then put it in a function that would loop through all rows, target the specific sheet and copy/paste a few cells from that row.

Solution

Assuming that a worksheet with the name of cell C3 of your worksheet exists, setting the destination worksheet will work (you don't need the wss-variable, btw).

The problem with your code is the copy+paste operation. The line

ws.Paste Destination:=Range("A1")

will not do what you expect. Destination:=Range("A1") will not copy into range A1 of the worksheet ws, it will copy into Range A1 of the current active sheet. You always need to specify the sheet, else VBA will assume the active sheet. This, for example, will do the trick:

ws.Paste Destination:=ws.Range("A1")

If you only want to copy the content of cells, not the formatting, the following statements are much more efficient (all do the same):

ws.Range("A1").Value = master.Range("A2").Value
ws.Range("A1") = master.Range("A2")
ws.Cells(1, 1) = master.Cells(2, 1)