Really simple macro problem - I bet!

[mrmnz]

I have the following text in 3 separate cells ie. C16, C17, C18

Esk Valley
Hastings
New Zealand

I wrote a macro to get it looking like this (using Paste Special/Transpose),
starting with Esk Valley in F16 (and it works!)

Esk Valley Hastings New Zealand

BUT when I go to the next set of cells to run the macro again, it just
repeats the macro using the cells already recorded:

Keyboard Shortcut: Ctrl+j
'
Range("C16:C18").Select
Selection.Copy
Range("F16").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

How do I tell the macro to change to C20:C22 and move this text to C20?

 

[L. Howard Kittle]

Provide some more detail about what your data layout looks like and what you
want to happen with it.

If you have three rows of data in C16:C18 and there is a blank row and then
three rows of data again in C20:C22 ... repeated for several rows down
column C, do you want to continue transposing those data sets to F16, F17,
F18 and so on?

Regards,
Howard

 

[Per Jessen]

Hi

Not much information to work with.

I assume C20:C22 should be moved to F20, not C20 as you wrote!

Try if this is what you need:

Sub TestIt()

LastRow = Range("C" & Rows.Count).End(xlUp).Row
For rw = 16 To LastRow Step 4
Range("C" & rw).Resize(3, 1).Copy
Range("F" & rw).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
'Range("C" & rw ).Resize(3).ClearContents
'****Use the line above to clear origial data
Next
Application.CutCopyMode = False
End Sub

Regards,
Per