How To Change Sheets

[rockyw]

I asked this before but this still does not work. This code works great when on sheet 1, send data to sheet 2,3,4 based on if there is a A,B,C ect in column A Sheet 1. If I move it to sheet 2, it stops working. I tried many things but nothing worked. Here is the origanal code for sheet one. What can I change to make this work on sheet 2? I tried changing the top area. Set ws = Sheets(1) ti this = Set ws = Sheets(2) This did not work Thanks

Sub BreakoutReDux()
Application.ScreenUpdating = False
Dim ws As Worksheet
Set ws = Sheets(1)
ws.Select
TrackCol = 7 ' Use this to set the tracking column as you like.
'Set up a tracking column
Cells(2, TrackCol) = "Status"
'Determine how many rows of data we have
lastrow = Range("A32000").End(xlUp).Row
lastpostedrow = Cells(32000, TrackCol).End(xlUp).Row
'Got back to the primary-sheet1
ws.Activate
'Start looping through rows and copy to target sheet
For RowIdx = lastpostedrow + 1 To lastrow
    Range("A" & RowIdx).Select
    
    'Only copy if col A is a value > 0, else skip it
    If Range("A" & RowIdx).Value > 0 Then
        tgtsht = Asc(UCase(Range("A" & RowIdx).Value)) - 63
        tgtRow = Sheets(tgtsht).Range("A30000").End(xlUp).Row + 1
        Range("A" & RowIdx).Offset(0, 1).Copy Sheets(tgtsht).Range("A" & tgtRow)
        Range("A" & RowIdx).Offset(0, 4).Copy 'Sheets(tgtsht).Range("B" & tgtRow)
        Sheets(tgtsht).Range("B" & tgtRow).PasteSpecial (xlPasteValues)
        Range("A" & RowIdx).Offset(0, TrackCol - 1) = "Posted"
    Else    'skip it and indicate as such
        ws.Range("A" & RowIdx).Offset(0, TrackCol - 1) = "Skipped"
    End If
Next RowIdx
'Go back to the first sheet
ws.Select
Set ws = Nothing
Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

This line...

tgtsht = Asc(UCase(Range("A" & RowIdx).Value)) - 63

Converts the letter in column A to the sheet number you want to paste to (destination sheets). Where "A" = 2, "B" = 3, "C"= 4, etc.

So if you change your source sheet number to 2

Set ws = Sheets([COLOR="Red"]2[/COLOR])

You want to offset set your destination sheets by one as well

tgtsht = Asc(UCase(Range("A" & RowIdx).Value)) - [COLOR="Red"]62[/COLOR]

This makes "A" = 3, "B" = 4, "C"= 5, etc.

 

[rockyw]

AlphaFrog Yes that does it, thanks so much. I'm trying to learn a little about this but it is not easy. Thanks