Repeat Macro Transpose

[joyousrob]

Hey guys , so I got this macro below but I want to introduce a loop or some way for it to repeat itself 13 times, i ave got a range that I coy and then paste transposed.

Any ideas ?

Sub Macro35()
'
' Macro35 Macro
'


'
Range("B18:F20").Select
Selection.Copy

Range("B64").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B22:F24").Select
Application.CutCopyMode = False
Selection.Copy

Range("B71").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
End Sub

 

[Joe4]

Welcome to the Board!

Does the range you are copying from always increase by 4 rows each time (18:20 -> 22:24 -> ...)
and does the range your are copying to always increase by 7 rows each time (64 -> 71 -> ...)?

 

[joyousrob]

Welcome to the Board!

Does the range you are copying from always increase by 4 rows each time (18:20 -> 22:24 -> ...)
and does the range your are copying to always increase by 7 rows each time (64 -> 71 -> ...)?

Yes they do, both of them

 

[Joe4]

If my assumptions above are correct, try this:

Sub Macro35()
'
    Dim i As Long
    
    For i = 1 To 13
        Range(Cells((i * 4) + 14, "B"), Cells((i * 4) + 16, "F")).Copy
        Cells((i * 7) + 57, "B").PasteSpecial Paste:=xlAll, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next i
    
End Sub

 

[Rick Rothstein]

Welcome to the Board!

Does the range you are copying from always increase by 4 rows each time (18:20 -> 22:24 -> ...)
and does the range your are copying to always increase by 7 rows each time (64 -> 71 -> ...)?

@Joe4,

If it does, then the newly written data will overwrite the last two groups of existing data since the address of the 12th existing range would be B62:D64 and the 13th existing range would be B66:D68.



@joyousrob,

Can you please clarify what 13 ranges are you copying transposed.

 

[Rick Rothstein]

If my assumptions above are correct, try this:

Sub Macro35()
'
    Dim i As Long
    
    For i = 1 To 13
        Range(Cells((i * 4) + 14, "B"), Cells((i * 4) + 16, "F")).Copy
        Cells((i * 7) + 57, "B").PasteSpecial Paste:=xlAll, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
    Next i
    
End Sub

I don't think your assumption is right even though it was what my original assumption was (see my comment in Message #5). If your assumption is correct, though, your code can be simplified further to this (although I am not sure about what happens at the overlap)...

Sub Macro35modified()
  Dim X As Long
  For X = 0 To 12
    Range("B64:D68").Offset(7 * X) = Application.Transpose(Range("B18:F20").Offset(4 * X))
  Next
End Sub

 

[Joe4]

I don't think your assumption is right even though it was what my original assumption was

Yeah, we didn't have much to go on...

 

[joyousrob]

Hey guys thanks for the answers, Im gonna be completely honest, I just started with macros and dont have much of an idea. What i did was copy and pasted what Joe posted replacing everything i had there so now it looks like this .....

Sub Macro35()
'
Dim i As Long

For i = 1 To 13
Range(Cells((i * 4) + 14, "B"), Cells((i * 4) + 16, "F")).Copy
Cells((i * 7) + 57, "B").PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Next i

End Sub


think i might have done something wrong (copied above is all the info on that macro i might be missing some formating on it ) but it only copied it once,I probably did something wrong, as for your question RICK, idont know how to describe it but I did make a macro, so this is what I could like it to do, without having to write it over and over again. I might not be explaining myself very well.

Sub Macro4()
'
' Macro4 Macro
'


'
Range("B18:F20").Select
Selection.Copy

Range("B64").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B22:F24").Select
Application.CutCopyMode = False
Selection.Copy

Range("B70").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B26:F28").Select
Application.CutCopyMode = False
Selection.Copy

Range("B76").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B30:F32").Select
Application.CutCopyMode = False
Selection.Copy

Range("B82").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B34:F36").Select
Application.CutCopyMode = False
Selection.Copy

Range("B88").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B38:F40").Select
Application.CutCopyMode = False
Selection.Copy

Range("B94").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B42:F44").Select
Application.CutCopyMode = False
Selection.Copy

Range("B100").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B46:F48").Select
Application.CutCopyMode = False
Selection.Copy

Range("B106").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B50:F52").Select
Application.CutCopyMode = False
Selection.Copy

Range("B112").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B54:F56").Select
Application.CutCopyMode = False
Selection.Copy

Range("B118").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

Range("B58:F60").Select
Application.CutCopyMode = False
Selection.Copy

Range("B124").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True

End Sub



Thanks for the answers guys !

 

[Joe4]

From what you posted, it looks like there were two errors in your original questions:
1. You only want to loop 11 times, not 13.
2. Your second copy is to row 70, not 76.

My amended code would look like:

Sub Macro35()
' 
    Dim i As Long 
    For i = 1 To 11 
        Range(Cells((i * 4) + 14, "B"), Cells((i * 4) + 16, "F")).Copy 
        Cells((i * 6) + 58, "B").PasteSpecial Paste:=xlAll, Operation:=xlNone, _ 
            SkipBlanks:=False, Transpose:=True 
        Application.CutCopyMode = False 
    Next i 
End Sub

Rick's would look like:

Sub Macro35modified() 
    Dim X As Long 
    For X = 0 To 10 
        Range("B64:D68").Offset(6 * X) = Application.Transpose(Range("B18:F20").Offset(4 * X)) 
    Next
End Sub

 

[Rick Rothstein]

@joyousrob,

Your posted code show 11 ranges being copied/transposed... Joe and I thought you meant you had 13 ranges to copy/transpose (which is why I asked about the new data overlapping the existing data); however, your "(copied above is all the info on that macro i might be missing some formating on it ) but it only copied it once" statement seems to mean you wanted something else copied 13 times. You are going to have to explain what want copied in more detail for us. I know you are new to asking questions on forums, so I'll give you one thing to keep in mind when doing so... everything that is obvious to you about what you want to do is completely unknown to the people you are asking help from, so you have to explain in full what your existing data looks like, how it is laid out and what you want it to look like afterwards.