Excel - A Macro to move data from rows to columns

Closed
Report
Posts
2
Registration date
Wednesday August 17, 2016
Status
Member
Last seen
August 25, 2016
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Posts
2
Registration date
Wednesday August 17, 2016
Status
Member
Last seen
August 25, 2016

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.
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490 >
Posts
2
Registration date
Wednesday August 17, 2016
Status
Member
Last seen
August 25, 2016

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