Referencing Cell using Variable for Row

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
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:

Code:
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
You don't really need all that Select stuff and it can cause problems when working with multiple sheets.

Does this work?
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top