Excel row data adding to row

salman426

New Member
Joined
Jun 7, 2014
Messages
4
Hi,

am using excel 2007 version

i just want a simple task getting confused how to do please help me thanks in advance

my task is in one sheet have rows 1,2,3,4,5 in another sheet has a,b,c,d rows

12345 rows add to abcd equally i mean 1 has abcd, 2 has abcd, 3 has abcd

it means 5*4= total 20 rows

Sheet 11
2
3
4
Sheet 1a
b
c
d
Result
1a
1b
1c
1d
2a
2b
2c
2d
3a
3b
3c
3d
4a
4b
4c
4d
5a
5b
5c
5d
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
I am sure someone can probably come up with a more concise way of doing it, but since no one has posted yet, here is a somewhat brute-force method that works:
VBA Code:
Sub CopyData()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim lr1 As Long
    Dim lr2 As Long
    Dim nr As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Set worksheet objects
    Set ws1 = Sheets("Sheet1") 'name of first sheet
    Set ws2 = Sheets("Sheet2") 'name of second sheet
    Set ws3 = Sheets("Sheet3") 'name of destination sheet
    
'   Find last rows with data in column A on first two sheets
    lr1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Initialize new row number on destination sheet
    nr = 1
    
'   Loop through data and populate destination sheet
    For i = 1 To lr1
        ws3.Activate
        ws3.Range(Cells(nr, "A"), Cells(nr + lr2 - 1, "A")) = ws1.Cells(i, "A")
        ws2.Activate
        ws2.Range(Cells(1, "A"), Cells(lr2, "A")).Copy ws3.Cells(nr, "B")
'       Increment new row counter for next time
        nr = nr + lr2
    Next i
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub
 
Upvote 0
Solution
Thanks Joe
it worked

if i have more columns in sheet 2 (Like A TO S) any kind of code CHANGE
In your example, everything was just one column.
The variable number was the row number, not the column number.
To do anything with multiple columns seems like an entirely different question to me, and would need to be explained how that should work, what that would all look like.

Since it is a different question, your best bet would be to post a new thread, with examples of how the data on each sheets looks initially, and how the final result should look.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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