Select range based on cell value in Sheet1, copy selection from Sheet2 to Sheet3 every nth row

vaemps

New Member
Joined
Apr 13, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
I have three sheets, one (sheet1) to handle data, one (sheet2) for example what will be pasted and one (sheet3) where this example is pasted.

In sheet one I have list of serial numbers, one per row. This serial number will be broken to cells in same row. In column "M", there will be identification of the product.

Based on this column "M", I would need to copy a range from sheet2 (ie. A3:N8) to sheet3. There are total of 7 different ranges in sheet2, all same sized (6, 14).

This range would be pasted to sheet3 starting from cell A3 to every sixth row (A3.. A9.. A15.. etc.) until the list of serial numbers from sheet1 ends. Also I would need to input cell value from sheet1 column L to every pasted range to cell (1, 3).

This all is activated with command button.

This is what I have so far only to test the function of copying the range to another sheet. It pastes the whole range to column A and to every sixth row.

Private Sub CommandButton1_Click()

Dim i As Long
Dim r As Long
Dim Rng As Range

With Sheets("PACKING LIST EXAMPLES")
Set Rng = .Range("A3:N8")
End With

For i = 2 To Rng.Count * 6 Step 6
r = r + 1
Sheets("PACKING LIST").Range("A" & i).Value = Rng(r).Value
Next i

End Sub

From here I need some help to make this work.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

vaemps

New Member
Joined
Apr 13, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Just to make clear sheet PACKING LIST EXAMPLES is Sheet2 and PACKING LIST is Sheet3
 

Watch MrExcel Video

Forum statistics

Threads
1,133,616
Messages
5,659,866
Members
418,535
Latest member
Ajith55

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