CODEWITHSUNDEEP
excelloopsvba
Kaish
Kaish

Reputation: 255

Looping a macro in excel

I want to creat a Macro that looks down column H and selects 14 cells. It then copies them and pastes them via a "transpose paste", i.e they go from rows to columns in the next sheet. It then keeps on doing this until it has run out. This is my code so far:

Sub Macro5()
    '
    ' Macro5 Macro
    '
    ' Keyboard Shortcut: Ctrl+t
    ' Dim x as integer
    ' Dim y as integer
    ' x = 313
    ' y = x + 13
    '     Range("Hx:Hy").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    'x = x + 14
End Sub

It supposed to start at cell H313.

My problem is, evertime I run this it just copies whatever cell I've highlighted and pastes that in the next sheet, without selecting all the cells I want.

How do I fix this?

Upvotes: 1

Views: 144

Answers (2)

ForkandBeard
ForkandBeard

Reputation: 884

Because you've 'commented out' your first portion of code the first line actually being run is

Selection.Copy

Which is why it's only copying what's highlighted. I think, if you uncomment everything, you've still got a couple of issues but I think your main issue is the line:

Range("Hx:Hy").Select

Should be something like:

Range("H" & CStr(x) & ":H" & CStr(y)).Select

Dim x as integer
Dim y as integer

x = 313
y = x + 13

Range("H" & CStr(x) & ":H" & CStr(y)).Select

Selection.Copy

Sheets("Sheet3").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Upvotes: 0

sina
sina

Reputation: 1829

Rows starting with a ' are considered a comment and are ignored by VBA. Therefore your code that selects the range (Range("Hx:Hy").Select) is never executed.

The first line being executed is Selection.Copy that - you guessed it - copies the cell you currently selected :)

Besides you need to compute the range, as "Hx:Hy" will never be parsed to H313:H326.

Use Range("H"&x&":H"&y) or Range(Cells(x, 8), Cells(y, 8)).

Upvotes: 1

Related Questions