copy & paste (to and from) different worksheets based on the value of a cell for each row

[m2gille]

I have background sheets that make calculations based on input data that is found on each row of the main sheet.

I created a ginormous macro that basically repeats the same command for a series of 30+ 'If/ If Else statements' where it finds the right background sheet for the row and then copies input data from the main sheet and pastes it to the appropriate background sheet, then copies the output cell from the background sheet and pastes it to the main sheet.

I would like to simplify this macro by finding the appropriate background sheet for a row, then assigning it to the variable 'ws'.

Then follow the series of copy and pastes, and then go to the next row and repeat.

However it doesn't work and I haven't been able to figure out why...

Here's the code:

Sub CommandButton1_Click()


     Dim ws As Worksheet
     For i = 4 To Range("E" & Rows.Count).End(xlUp).Row
           
          Cells(i, 5).Select
    
    'Find the appropriate family and use the appropriate sheet to calculate the R information
         If ActiveCell = "320 Fam" Then
             Set ws = Sheets("R_320")
         ElseIf ActiveCell = "125" Then
             Set ws = Sheets("R_125")
         ElseIf ActiveCell = "170/190" Then
             Set ws = Sheets("R_170_190")
                  'continue for about 30 more sheets
         End If

        'select the cell one column to the right ('F')
        ActiveCell.Offset(0, 1).Range("A1").Select
        'copy the selected cell and copy it to cell G9 in the appropriate sheet
        Selection.Copy Destination:=ws.Range("G9")
        
        'move to the cell in column 'O' and Copy & Paste it to cell G8 on the R sheet
        Sheets("R_Forecaster").Cells(i, 15).Copy Destination:=ws.Range("G8")
               
        'Go to the appropriate sheet and copy cell N19
        ws.Range("N19").Copy
        
        'paste to the R 2 year total values cell (column 'P')
        Sheets("R_Forecaster").Cells(i, 16).PasteSpecial xlPasteValues

   Next i

End Sub

Please help my ignorance.

Thanks!

 

[JLGWhiz]

It couldn't be too bad, I was able to use what you had and come up with this. Give it a try.

Sub CommandButton1_Click()
Dim i As Long, sh As Worksheet, sName As String
    Set sh = ActiveSheet 'Use actual sheet name
     For i = 4 To Range("E" & Rows.Count).End(xlUp).Row
         If sh.Cells(i, 5) <> "" Then
            sName = sh.Cells(i, 5).Value
                With Sheets(sName)
                    .Range("G9") = sh.Cells(i, 6).Value
                    .Range("G8") = sh.Cells(i, 15).Value
                End With
            Sheets(sName).Range("N19").Copy
            sh.Cells(i, 16).PasteSpecial xlPasteValues
         End If
     Next
     Application.CutCopyMode = False
End Sub

 

[m2gille]

It worked!! Thank you so much.

 

[JLGWhiz]

It worked!! Thank you so much.

Glad you could use it.
Regards, JLG