Insert and Paste

jpprespa

New Member
Joined
Apr 10, 2013
Messages
8
Hello,

I am working on a macro that has 240 columns of data with a value in the first row (A1) that I need to copy and paste in the next column. Thus, I have wrote the vba to go through the worksheet and insert 240 blanks columns.

Dim j As Integer, k As Integer, m As Integer​

j = Range("A1").End(xlToRight).Column​
'j is the last column​
For k = j To 2 Step -1​
Range(Cells(1, k), Cells(1, k)).EntireColumn.Insert​
Next k

Now I need to copy the data in the first cell (A1) to the new inserted column (B2:B), throughout the document. The length of each column of data changes, some will be 4 cells in length, others might be 60 cells in column length.

I tried this:
Dim LR As Long

LR = Range("C" & Rows.Count).End(xlUp).Row
Range("C1").AutoFill Destination:=Range("D1:D" & LR)
For m = LR To 2
Next m


However as you can imagine by me posting here, it did not work. Suggestions?

New to VB. Thanks!
Joe
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883
Perhaps look into using Range.PasteSpecial Transpose:=True
 

jpprespa

New Member
Joined
Apr 10, 2013
Messages
8
Just so folks understand, my original date looks like this:
132133134135
32024R022;32003H018;32259R154;32713C010;
32024R024;32003H017;32259R134;32713C003;
32024R032;32003R018;32259H015;32724C023;
32003H016;32259R171;
32003H019;32259R189;
32003R017;32223R057;
32003R002;
32003R010;

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>


and I need it to look like this:
132 133 134 135
32024R022;13232003H018;13332259R154;13432713C010;135
32024R024;13232003H017;13332259R134;13432713C003;135
32024R032;13232003R018;13332259H015;13432724C023;135
32003H016;13332259R171;134
32003H019;13332259R189;134
32003R017;13332223R057;134
32003R002;133
32003R010;133

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>


I don't know if the transpose will work in this case.
 

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883
Gotcha. Misunderstood. Will look at this from work in the morning if nobody else chips in before then. Looks simple enough.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,566
Members
414,078
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top