Insert data from one sheet into after every row of another sheet

[satis]

Hi,

I'm new to this community, looking for solution for the below requirement. Any help is much appreciated.

want to copy data from one sheet, insert into after every row of another sheet in excel.

Example:

Sheet1:

1.US
2.UK
3.IN
4.CH
5.JP
6.BR

Sheet2:

1. Administrator
2. Customer support
3. Designer
4. Product Engineer
5. Accountant

Sheet2 Output:
1. Administrator

1.US
2.UK
3.IN
4.CH
5.JP
6.BR
2. Customer support
1.US
2.UK
3.IN
4.CH
5.JP
6.BR
3. Designer
1.US
2.UK
3.IN
4.CH
5.JP
6.BR
4. product Engineer
1.US
2.UK
3.IN
4.CH
5.JP
6.BR
5. Accountant
1.US
2.UK
3.IN
4.CH
5.JP
6.BR

 

[johnnyL]

How about:

Sub InsertingDataIntoSheet2ColumnA()
'
    Sheets("Sheet2").Range("A2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'
    Sheets("Sheet1").Select
    Range("A1:A6").Copy
    Sheets("Sheet2").Select
    Range("A3").Insert Shift:=xlDown
'
    Sheets("Sheet1").Select
    Range("A1:A6").Copy
    Sheets("Sheet2").Select
    Range("A10").Insert Shift:=xlDown
    
    Sheets("Sheet1").Select
    Range("A1:A6").Copy
    Sheets("Sheet2").Select
    Range("A17").Insert Shift:=xlDown
    
    Sheets("Sheet1").Select
    Range("A1:A6").Copy
    Sheets("Sheet2").Select
    Range("A24").Insert Shift:=xlDown
'
    Sheets("Sheet1").Select
    Range("A1:A6").Copy
    Sheets("Sheet2").Select
    Range("A31").Insert Shift:=xlDown
'
    Application.CutCopyMode = False
End Sub

 

[HaHoBe]

Hi satis,

maybe try:

Sub MrE1175625()
'https://www.mrexcel.com/board/threads/insert-data-from-one-sheet-into-after-every-row-of-another-sheet.1175625/
  
  Dim varValues         As Variant
  Dim lngNumberRows     As Long
  Dim lngRow            As Long
    
  varValues = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).Value2
  lngNumberRows = UBound(varValues)
  With Sheets("Sheet2")
    For lngRow = .Range("A" & .Rows.Count).End(xlUp).Row To 1 Step -1
'      .Cells(lngRow + 1, "A").Resize(lngNumberRows).EntireRow.Insert        'will insert rows
      .Cells(lngRow + 1, "A").Resize(lngNumberRows).Insert                  'will insert cells
      .Cells(lngRow + 1, "A").Resize(lngNumberRows).Value = varValues
    Next lngRow
  End With
  
End Sub

Ciao,
Holger

 

[johnnyL]

Shortened version of my previous offering:

Sub InsertingDataIntoSheet2ColumnA_V2()
'
    Sheets("Sheet2").Range("A2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'
    Sheets("Sheet1").Range("A1:A6").Copy
    Sheets("Sheet2").Range("A3").Insert Shift:=xlDown
'
    Sheets("Sheet1").Range("A1:A6").Copy
    Sheets("Sheet2").Range("A10").Insert Shift:=xlDown
    
    Sheets("Sheet1").Range("A1:A6").Copy
    Sheets("Sheet2").Range("A17").Insert Shift:=xlDown
    
    Sheets("Sheet1").Range("A1:A6").Copy
    Sheets("Sheet2").Range("A24").Insert Shift:=xlDown
'
    Sheets("Sheet1").Range("A1:A6").Copy
    Sheets("Sheet2").Range("A31").Insert Shift:=xlDown
'
    Application.CutCopyMode = False
End Sub

 

[satis]

Thanks JohnnyL and Holger. I've tried both solutions, unfortunately this is not working as expected.

​

 

[HaHoBe]

Hi satis,

could you please tell us what went wrong with these codes? Both codes try to bring up the result from the opening post Sheet 2 Output example.

Ciao,
Holger

 

[johnnyL]

Thanks JohnnyL and Holger. I've tried both solutions, unfortunately this is not working as expected.

​

The code I posted produces the exact same result you posted for 'Sheet 2 Output:'

 

[satis]

Thanks for your efforts. Please find the attachments for output details.

 

[HaHoBe]

Hi satis,

which attachments?

Ciao,
Holger

 

[satis]

Sorry, attachment was missed. Please find the screenshots.
The sheet1 and sheet2 have exact data same as in my question. I've run this macro in sheet2.