Excel row data adding to row

[salman426]

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 1	1
	2
	3
	4
Sheet 1	a
	b
	c
	d
Result
1	a
1	b
1	c
1	d
2	a
2	b
2	c
2	d
3	a
3	b
3	c
3	d
4	a
4	b
4	c
4	d
5	a
5	b
5	c
5	d

 

[Joe4]

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:

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

 

[salman426]

tHANKS jOE

 

[salman426]

Thanks Joe
it worked

if i have more columns in sheet 2 (Like A TO S) any kind of code CHANGE

 

[Joe4]

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.