Copy Variable Range and past x times in new sheet with criteria

Leochin

New Member
Joined
May 7, 2018
Messages
2
i need vba coding for the work that i have to do on daily basis. i have values in 3 consecutive Columns in sheet1, let say Columns AR AS AT and 70 rows starting from AR2 AS2 AT2.(these Rows are variable some time 10 some time 70 or even 500). i need to to copy values from these columns and paste them to sheet2 multiple times till x value given in cell AV2 (x=3, Starting from 1) and also need to assign 1 2 3 numbers to these new values in sheet 2.

Sorry for language.

Initial Form

-Cut# Bud Qty Start End
-369A 1 27 1 3
-369A 2 27
-369B 1 19
-370 1 30

required Form

x Cut# Bnd Qty
1 369A 1 27
1 369A 2 27
1 369B 1 19
1 370 1 30
2 369A 1 27
2 369A 2 27
2 369B 1 19
2 370 1 30
3 369A 1 27
3 369A 2 27
3 369B 1 19
3 370 1 30

Cut# Bnd and Qty Columns are fixed and their row can vary. Starting value is always 1 and End value can vary.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,362
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Code:
Sub CopyMultiples()
   Dim Lr As Long, i As Long, j As Long
   Lr = Range("AR" & Rows.Count).End(xlUp).Row - 1
   Range("AR2:AT" & Lr + 1).Copy Sheets("Sheet2").Range("B1").Resize(Lr * Range("AV2").Value)
   For i = 1 To Lr * Range("AV2").Value Step Lr
      j = j + 1
      Sheets("Sheet2").Range("A" & i).Resize(Lr).Value = j
   Next i
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,362
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,066
Messages
5,526,576
Members
409,712
Latest member
lager2020

This Week's Hot Topics

Top