Referencing Cell using Variable for Row

[hallingh]

Hello everyone,

I think this is a pretty easy fix, I just haven't used VBA in quite some time and am rusty to say the least.. All I'm trying to do is run through each row of one sheet, take the contents in columns C through N, and paste them (transposed) in a cell on another sheet. Then, I'd like to copy the sheet, then wash rinse and repeat. Here's the code I have:

Sub Calculate()

Dim myRow As Long
Dim LastRow As Long


Application.ScreenUpdating = False




LastRow = Sheets("Locations").Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row


For myRow = 2 To LastRow






[FONT=arial black]    Sheets("Locations").Range("C" & myRow & ":N" & myRow).Select[/FONT]
    Selection.Copy
    Sheets("Bonus Calc").Select
    Range("K3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    
    Sheets("Locations").Select
    Range("B" & myRow).Select
    Selection.Copy
    Sheets("Bonus Calc").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    
    Sheets("Bonus Calc").Select
    Sheets("Bonus Calc").Copy After:=Sheets(7)
    
Next myRow


Application.ScreenUpdating = True


    
    
End Sub

I've made bold the portion of the code that's not functioning properly. I'm assuming the reason is that I'm not using the correct syntax to reference the cells using the myRow variable. I could reference each cell one by one, but that seems like cheating! Any help you provide would be greatly appreciated.

Thanks!
Hank

 

[frabulator]

Works fine for me. Are you sure that you have a sheet called "Locations"?

 

[hallingh]

Oh, interesting, after stepping through the code, it is working when I already have Locations as the active sheet, but not when I have any other sheet as the active sheet. Well, I've just set Locations to be the active sheet in the beginning of the For Loop, but I'm not totally sure why it wasn't working. Thanks for chiming in!

 

[Norie]

You don't really need all that Select stuff and it can cause problems when working with multiple sheets.

Does this work?

Sub Calculate()
Dim myRow As Long
Dim LastRow As Long

    Application.ScreenUpdating = False

    LastRow = Sheets("Locations").Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row

    For myRow = 2 To LastRow

        Sheets("Locations").Range("C" & myRow & ":N" & myRow).Copy
        Sheets("Bonus Calc").Range("K3").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                               False, Transpose:=True

        Sheets("Locations").Range("B" & myRow).Copy
        Sheets("Bonus Calc").Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                        :=False, Transpose:=False
        Application.CutCopyMode = False

        Sheets("Bonus Calc").Copy After:=Sheets(7)

    Next myRow

    Application.ScreenUpdating = True

End Sub