macro recording help

[Kathy in Cincinnati]

I need to pick four non-contiguous cells in a row, copy
them and then paste their values into a column
using 'paste special', 'value', 'transpose'. The
worksheet has one row of data, four rows for summary,
another row of date, four more rows for summary etc.

I can do this easily by clicking the four cells using the
mouse while holding down the control key, but I can't
find a way to do this that the macro recorder will
recognize.

Does anyone have a solution? Thank you. Kathy

 

[Otto Moehrbach]

Kathy
I turned on the macro recorder and selected 4 cells as you said you do,
and it worked for me. What are you doing to record the macro and what
happens when you do it? What do you mean by "but I can't find a way to do
this that the macro recorder will recognize"? HTH Otto

 

[Max]

Tried recording the sample macro below
along the lines of your steps
and it seems to work ok? ..

Sub Macro2()
'Selected A1, C1, E1 and G1 > Copy
'Right-clicked on A3, Paste special > Transpose > OK

Range("A1,C1,E1,G1").Select
Range("G1").Activate
Selection.Copy

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

Application.CutCopyMode = False

End Sub

 

[Guest]

Otto -- your are right, when I reopened the spread sheet
and tried it again, it worked. I don't know what I was
doing before that was different, maybe it was just that I
had recorded several other macros and the gremlins in my
brain or the spreadsheet were working overtime. Anyway,
thanks for getting me to do it again! Kathy

 

[Guest]

Max -- Thanks for the VBA code. I have been using VBA in
access but haven't previously run into it in excel.
Otto 'said' he tried it the way I was doing it and it
worked, so I reopened the spread sheet and tried it also,
and I did get it to work, so I'll try it that way but it
is always nice to know the code so I can add it in if
necessary. Kathy

 

[Max]

Max -- Thanks for the VBA code. I have been using VBA in
access but haven't previously run into it in excel.
Otto 'said' he tried it the way I was doing it and it
worked, so I reopened the spread sheet and tried it also,
and I did get it to work, so I'll try it that way but it
is always nice to know the code so I can add it in if
necessary. Kathy

You're welcome, Kathy!
Great to hear you got it to work
Thanks for the feedback

 

[Otto Moehrbach]

Kathy
Did you ever get a solution to your problem? I was looking back over
past postings and found yours. The macro you need is pretty simple to write
but I'll need some sample data layout. If you wish, send me a small file
with about a half dozen cycles of data and I'll write a macro for you.
(e-mail address removed)
Remove "cobia97" from this address.
HTH Otto