Relative Cell position NOT working with or without macro

[Guest]

I am trying to move 4 cells within one column to 4 cells within one row, ie:
A2, A3, A4, A5 to B1, C1, D1, E1 by using a macro. I want to repeat the macro
then on cells A8, A9, A10, A11 moving them to rows A7, B7, C7, D7, etc. See
the pattern? IE I am converting data in a column to data in rows. The first 3
different files I tried this on, relative cell position worked great. Then
all of a sudden on the next file (and every file since) the macro always runs
as if it is using absolute cell references. No matter what cell on the sheet
I start the macro from, it always go back and tries the use A2,3,4,5 and move
it to B1, C1, D1, E1. It worked in the beginning but now it doesn't. Can
anyone Help????
Thanks

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/30/05 by new
'
' Keyboard Shortcut: Ctrl+m
'
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Range("A3").Select
Selection.Cut
Range("C1").Select
ActiveSheet.Paste
Range("A4").Select
Selection.Cut
Range("D1").Select
ActiveSheet.Paste
Range("A5").Select
Selection.Cut
Range("E1").Select
ActiveSheet.Paste
Range("A8").Select
End Sub

 

[Guest]

I am not real sure of the pattern you are trying to develop
A2:A5 to B1:E1
is not really the same pattern as
A8:A11 to A77

The macro you have is definately absolutes only.

If what you want to do is A2:A5 to B1:E1
A8:A11 to B7:E7 and Next series A14:A17to B13:E13

Then in fyour macro something like

for rr = 1 to 61 step 6
for cc = 1 to 4
cells(rr,1+cc)=cells(rr+cc,1)
next cc
next rr

You will have ot play with it if you want a different pattern.

 

[Harald Staff]

Hi

If you read your code, it's pretty obvious what to expect from it:

Whatever selected is cut.
B1 is selected and Paste.
A3 is selected and Cut
C1 is selected and Paste
.... and so on.

Your pattern isn't too obvious, but this should work with the data
described:

Sub Macro1()
Dim R As Long, C As Long
For R = ActiveCell.Row To ActiveCell.Row + 12 Step 6
For C = 1 To 4
Cells(R + C, 1).Cut _
Destination:=Cells(R, C + 1)
Next
Next
End Sub

HTH. Best wishes Harald

 

[Guest]

I guess I did a pretty bad job of describing my question. I truly appreciate
your reply but it didn't do what I needed. Let me ask it this way, I am
pasting a copy of a simple macro below.

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 5/30/05 by new
'
' Keyboard Shortcut: Ctrl+z
'
Range("G2:G4").Select
Selection.Copy
Range("H1:J1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Range("G7").Select
End Sub

The problem I have is that is copies and pastes the same data over and over
no matter what cell I have selected when I run the macro. What I need is for
the macro to run from the cell I have selected at the time I run the macro
and to perform the copy and paste operations relative to the cell that is
selected when the macro is ran.
Can you help?

Thanks
Still sctatching my head.

 

[Harald Staff]

Sure

Sub Macro1()
Dim R As Long, C As Long, L As Long
R = ActiveCell.Row
C = ActiveCell.Column
For L = 1 To 4
Cells(R + L - 1, C).Cut _
Destination:=Cells(R - 1, C + L)
Next
End Sub

But how will the code know that the first time 4 cells was to be transposed,
now you want only 3 ?

HTH. Best wishes Harald

 

[Guest]

Great Harald, I'm almost there. How can I make the last thing the macro does
is select the cell in the same beginning column but exactly 5 rows down? ie:
the ending selected cell is the original active cell number plus 5.

Thanks,
Danny (not scratching as much now)

 

[Harald Staff]

Good.

Activecell.Offset(5, 0).Select

HTH. Best wishes Harald