Need help with loop through a worksheet copying 3 cells from every 8th column

[mikenelena]

Beginning in column N, I need to copy range N5-N7 to a different workbook and sheet, transposing the range as I paste. Then I need to skip 8 columns and do the same with V5-V7, and so on out to Column GH. I should finish with 23 rows on the new workbook/sheet, and ideally, I like these to paste in reverse order, such that GH5-GH7 pastes into row 2, and N5-N7 pastes into row 24.

I've been playing with iterative loops, but as a novice, I can't seem to get it right. Any help anyone could offer would be most appreciated.

Thanks,
...Mike

 

[welshgasman]

Why can you not use the Macro recorder to create the steps, then perhaps tailor as you need and use that.?

Whenever I want something like this, that is my first approach. That then gives me he basics to work with and I tailor the code from there.

People seem to forget the macro recorder can give them a huge head start, especially when they do not know VBA that well.

 

[Rick Rothstein]

I assumed the output should go to Column A:C for the rows being outputted to. I did not set this up for output to a different workbook (you didn't tell us anything about such a workbook), rather, I outputted to Sheet2 in the same workbook. I'll leave it up to you to adjust that for the workbook and worksheet within that workbook that you want the output to go to. With that said, see if this code does what you want...

[table="width: 500"]
[tr]
	[td]Sub mikenelena()
  Dim C As Long, R As Long
  R = 24
  For C = 14 To 190 Step 8
    Sheets("Sheet2").Cells(R, "A").Resize(, 3).Value = Application.Transpose(Cells(5, C).Resize(3))
    R = R - 1
  Next
End Sub[/td]
[/tr]
[/table]

 

[mrshl9898]

How's this? Just rename the sheets.

Sub loopntranspose()


Dim rownum As Long
Dim colnum As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet


Set ws1 = Sheets("Data")
Set ws2 = Sheets("Paste")


colnum = 14 'column N
rownum = 24
ws1.Select


Do Until colnum > 190 'colnum GH


With ws1
.Range(Cells(5, colnum), Cells(7, colnum)).Copy
End With
ws2.Cells(rownum, 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True


rownum = rownum - 1
colnum = colnum + 8


Loop

ws2.Select


End Sub

 

[mikenelena]

welshgasman wrote:

Why can you not use the Macro recorder to create the steps, then perhaps tailor as you need and use that.?

I did try that... You are right. It is often helpful, but in this particular case it was pretty much useless.

...Mike

 

[welshgasman]

welshgasman wrote:
I did try that... You are right. It is often helpful, but in this particular case it was pretty much useless.

...Mike

Really?, as if your range was variable, that would be much harder, but I would have thought stepping through the process carefully step by step would have provided most of what you needed. Really puzzled at that.?

However you have been provided code samples to do the same.

 

[mikenelena]

Rick Rothstein & mrshl9898,

Thanks to you both. I tried both of your proposed solutions, and both worked flawlessly. I much appreciate the quick response. I'll study and learn from both of these methods.

Thanks again,
...Mike

 

[mikenelena]

Really?, as if your range was variable, that would be much harder, but I would have thought stepping through the process carefully step by step would have provided most of what you needed. Really puzzled at that.?

However you have been provided code samples to do the same.

Yes, I've found some procedures come out of the macro recorder much more usable than others. Here's what it spit at me in this case:

Range("V6:V8").Select
    Selection.Copy
    Windows("Book2").Activate
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows("FileName.xlsm"). _
        Activate
    Range("AD5:AD8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Range("AD5:AD7").Select
    Selection.Copy
    Windows("Book2").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("C6").Select
    Application.CutCopyMode = False
    Windows("FileName.xlsm"). _
        Activate

Thanks,
...Mike