How to copy a range of Excel values in one worksheet with a defined row number and paste special (pa-CodePudding

first time using vba and I have run into a problem.

The way my Excel is set up is in a sheet called "Key Values" there are a list of cells with formulas spanning from columns J to DY. This data is over rows 7 to 34. I am trying to copy each row and paste special (as values and transposing them) into a singular column in a different sheet called "Shares"

Originally I wrote the vba code out manually and it worked fine, however I wanted to clean up the code as I imagine the manual version can be made into a loop. This is where I ran into the problem.

Original Code: (note obviously this isn't the whole thing, just part of it to give an idea of what it looked like)

    Sheets("Key Values").Range("J7:DY7").Copy
    Sheets("Shares").Range("I4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

    Sheets("Key Values").Range("J8:DY8").Copy
    Sheets("Shares").Range("I124").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

    Sheets("Key Values").Range("J9:DY9").Copy
    Sheets("Shares").Range("I244").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

I tried to make the above into a loop to go from rows 7 to 34 but keep running into an error.

Code with Loop (and problem):

Sub MacroTestv2()

    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
        
' i is the number of rows in the Key Values
' j is the row corresponding to the Key Values that will be copied
' k is the row number where the Key Values will be pasted (as values and transposed)

    i = 34
    k = 4
    
    For j = 7 To i
    
    Sheets("Key Values").Range(Cells(j, 10), Cells(j, 129)).Copy
    Sheets("Shares").Range("I", k).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    
    k = k   120
    
    Next

    Sheets("Shares").Range("A1").Select

End Sub

Any help would be much appreciated!

CodePudding user response：

Integer values are limited to 32,767 so use Long. Avoid unqualified refences like Cells(j, 10) because they default to the active sheet.

Sub MacroTestv2()

    Dim i As Long, j As Long, k As Long
        
' i is the number of rows in the Key Values
' j is the row corresponding to the Key Values that will be copied
' k is the row number where the Key Values will be pasted (as values and transposed)

    i = 34
    k = 4
      
    Const NUM = 120
    For j = 7 To i
    
        Sheets("Key Values").Range("J" & j).Resize(, NUM).Copy 'J:DY
        Sheets("Shares").Range("I" & k).PasteSpecial Paste:=xlPasteValues, _
              Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        
        k = k   NUM
    
    Next
    Sheets("Shares").Activate
    Sheets("Shares").Range("A1").Select

End Sub