Excel - A Macro to move data from rows to columns

Closed
yvonlet Posts 2 Registration date Wednesday August 17, 2016 Status Member Last seen August 25, 2016 - Aug 17, 2016 at 01:37 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Aug 30, 2016 at 11:49 AM
Excel - A Macro to move data from rows to columns
Thanks to Venkat1926 for this tip!

This Macro is working well for me, but with the exception of one thing. Every time I run the macro, it changed the tab that needs to be change along with a 2nd tab associated to it. I explain myself:

1) I have 2 tabs, "ABC" and "XYZ".
2) On tab "XYZ", cells A2 to H2 are refering to position C2-K2 from tab "ABC". For example, A2 from XYZ is: "=ABC!C2".
3) On tab "ABC", I have data in column A2-A10.
4) Run the macro. Cells A2-A10 are copied to line C2-K2 of tab ABC. Working perfectly well for tab ABC.
5) However, tab XYZ is also affected. For example, the 3 first column has a reference error "=ABC!#REF!". And the 4th column is shifted and contains "=ABC!C2".

Any suggestions on preserving the references in XYZ?

Sub test()
Dim rng As Range, m As Integer, c As Range
Columns("c:E").Delete
m = 9
Set rng = Range(Range("a2"), Range("a2").End(xlDown))
Set c = Range("a2")
Do While c <> ""
'MsgBox c.Address

Range(c, c.Offset(m - 1, 0)).Copy
Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
Set c = c.Offset(m, 0)
Loop

End Sub

1 reply

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Aug 25, 2016 at 11:33 AM
Hi Yvonlet,

That happens because you delete columns C:E, thus changing the cell references in your formula's.

Consider changing:
.Delete

into
.ClearContents


Best regards,
Trowa
0
yvonlet Posts 2 Registration date Wednesday August 17, 2016 Status Member Last seen August 25, 2016
Aug 25, 2016 at 12:57 PM
Thank you!
It work first time with the ClearContents.

Now, just another question.

I would like to clear all contents in my TAB:
Columns("a:z").ClearContents,

but then, use the clipboard to do a
Paste Special, Text into cell A2-A20 of the same TAB.

Do you know of such command?

But again, so many thanks for the Clear.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541 > yvonlet Posts 2 Registration date Wednesday August 17, 2016 Status Member Last seen August 25, 2016
Aug 30, 2016 at 11:49 AM
Hi Yvonlet,

Where is the data you want to put in A2-A20 coming from?

Same sheet?: then you can either clear the contents around that range or copy that range outside the cleared range first.

Other sheet?: then you can clear first and copy later.

Not sure if this is helpful. If not, then explain in greater detail what you are after.

Also to clear an entire sheet you can use:
cells.clearcontents
.

Best regards,
Trowa
0