Excel VBA copy range of data and paste in every nth row of another sheet

paneliyadhruv

New Member
Joined
May 21, 2018
Messages
36
Dear All,

I have two sheets in excel file. First sheet1 having data from column A to J.
I want to paste it in sheet2
From A3 to J3 first row of sheet1.
From A6 to J6 second row of sheet1.
From A9 to J9 second row of sheet1.
And so on till it reach to blank in sheet1.

My data is changing. For example sheet1 may be column A to K or any thing else. Pasting in every nth row i.e. every 4 row or every 3 row depending upon requirement.

Kindly provide your valuable inputs
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
This assumes you will always have data in A3, the nth row or gap is calculated from this column (i.e. click A3, press CTRL+Down and calculate the gap between the activecell and A3) and the width is calculated from this row (3):
Code:
Sub MoveData()

    Dim arr()   As Variant
    Dim LR      As Long
    Dim x       As Long
    Dim xStep   As Long
    
    Application.ScreenUpdating = False
    
    With Sheets("Sheet1")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LC = .Cells(3, .Columns.Count).End(xlToLeft).Column
        xStep = .Cells(3, 1).End(xlDown).Row - 3
        
        For x = 3 To LR Step xStep
            arr = .Cells(x, 1).Resize(, LC).Value
            Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, LC).Value = arr
        Next x
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr

End Sub
 
Last edited:

paneliyadhruv

New Member
Joined
May 21, 2018
Messages
36
Dear Sir,
From copy sheet1 each row (continuously till blank) and paste it in every nth row of another sheet.

Reverse order of your macro.
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Change xStep value to suit:
Rich (BB code):
Sub MoveData()

    Dim arr()   As Variant
    Dim LR      As Long
    Dim x       As Long
    Dim xStep   As Long
    
    xStep = 2
    
    Application.ScreenUpdating = False
    
    With Sheets("Sheet1")
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LC = .Cells(3, .Columns.Count).End(xlToLeft).Column
        
        For x = 3 To LR
            arr = .Cells(x, 1).Resize(, LC).Value
            Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(xStep).Resize(, LC).Value = arr
        Next x
    End With
    
    Application.ScreenUpdating = True
    
    Erase arr

End Sub
 
Last edited:

paneliyadhruv

New Member
Joined
May 21, 2018
Messages
36

ADVERTISEMENT

Dear Sir,

Working perfectly. However if i want to specify copy column for eg. A to D only and change nth row (may be every 4th or evry 5th row). where i need to make change in macro.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Change
Rich (BB code):
LC = .Cells(3, .Columns.Count).End(xlToLeft).Column
To
Rich (BB code):
LC = 4
if you want A to D
LC (last column) represents the column number for the last column the data is in for a specific row (3)
 
Last edited:

paneliyadhruv

New Member
Joined
May 21, 2018
Messages
36

ADVERTISEMENT

From specific columns like D to F or G to L. Range of copy column.
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,701
Office Version
  1. 365
Platform
  1. Windows
Adjust this line:
Rich (BB code):
arr = .Cells(x, 1).Resize(, LC).Value
LC explained as above
1 is column "A" so that line is write values from Sheet1 Ax:Dx (if LC = 4) into array and then print contents to Sheet2, first empty row after last used row.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,894
Messages
5,598,724
Members
414,253
Latest member
MarieCo

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