Help with Excel Macro

vidyashankara

New Member
Joined
Mar 24, 2014
Messages
16
I am new to excel and I need to make a macro that reads data from Sheet1 and pastes it into sheet2 successively.

This is how my sheet one looks..

A1 10 1249
A2 13 1232
A3 21 2312
.
.
.
A1 402 12212
A2 432 23323
A3 442 23232
.
.
.

I need Sheet 2 to look like this

A1 10 1249 A2 13 1232 A3 21 2312 . . .
A1 402 12212 A2 432 23323 A3 442 23232 . . . .
.
.
.

How do I write a macro for this? I recorded it using the recorder, and it works only for the first couple of values...
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Yes, sheet1 has only 3 columns. all the A1 data comes into a pile in sheet 2, all the a2 data goes into another pile and so on. I have data ranging from A1 - A12, B1-B12 ... H1-H12... totally 96 piles of data at the end...
 
Upvote 0
Try This and Let me know if this works

Code:
Sub Format()
Dim MyCell As Range
Dim Mycell2 As Range
Dim LastRow As Long
Dim LastRow2 As Long
Dim Col As Long
LastRow = Sheets("Sheet1").Range("A100000").End(xlUp).Row
LastRow2 = 1
Col = 1

    For Each MyCell In Sheets("Sheet1").Range("A1:A" & LastRow)
        With Sheets("Sheet2")
        
        LastRow2 = Sheets("Sheet2").Range("A100000").End(xlUp).Row
         
                If MyCell.Value = "A1" Then
                   LastRow2 = LastRow2 + 1
                   Col = 1
                End If
            .Cells(LastRow2, Col + 0).Value = MyCell.Value
            .Cells(LastRow2, Col + 1).Value = MyCell(1, 2).Value
            .Cells(LastRow2, Col + 2).Value = MyCell(1, 3).Value
            
            Col = Col + 3
        End With
    Next MyCell
End Sub
 
Upvote 0
Try This and Let me know if this works

Code:
Sub Format()
Dim MyCell As Range
Dim Mycell2 As Range
Dim LastRow As Long
Dim LastRow2 As Long
Dim Col As Long
LastRow = Sheets("Sheet1").Range("A100000").End(xlUp).Row
LastRow2 = 1
Col = 1

    For Each MyCell In Sheets("Sheet1").Range("A1:A" & LastRow)
        With Sheets("Sheet2")
        
        LastRow2 = Sheets("Sheet2").Range("A100000").End(xlUp).Row
         
                If MyCell.Value = "A1" Then
                   LastRow2 = LastRow2 + 1
                   Col = 1
                End If
            .Cells(LastRow2, Col + 0).Value = MyCell.Value
            .Cells(LastRow2, Col + 1).Value = MyCell(1, 2).Value
            .Cells(LastRow2, Col + 2).Value = MyCell(1, 3).Value
            
            Col = Col + 3
        End With
    Next MyCell
End Sub

Thanks Redwolf. I changed A100000 to A7944, which is the last row. When I ran the macro, it only copies the first row and nothing else... :S

Are you able to get it to work in the excel sheet I attached...
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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
Back
Top