VBA: Copy and Paste Large Range, Fill Adjacent Column with Variable Numbers

[cmschlatt]

Hi all,

New user, but long time lurker. This is the first time I've been unable to find a solution in old threads

I have a set of data that I need to copy and paste below itself 8 times. The number of rows will vary each time I run the macro.

Each time the data is pasted, I need to fill the column next to the newly pasted data with a number. Each set of pasted data will have a different number.
First set of pasted data will be 61
2nd - 191
3rd - 420
4th - 491
5th - 545
6th - 546
7th - 552
8th- 653

Additionally, the original data that was copied should have 55 down its column.

Example:

Excel 2013

Row\Col	A​	B​	C​	D​
1​	6/10/2015​	6/16/2015​	4011​	Bananas
2​	6/10/2015​	6/16/2015​	20401100000​	Bananas
3​	6/10/2015​	6/16/2015​	111111111​	Cereal
4​	6/10/2015​	6/16/2015​	111111111​	Cereal
5​	6/10/2015​	6/16/2015​	111111111​	Cereal

Sheet3

Start with the above and turn it into this:

Excel 2013

Row\Col	A​	B​	C​	D​	E​
1​	6/10/2015​	6/16/2015​	4011​	Bananas	55​
2​	6/10/2015​	6/16/2015​	20401100000​	Bananas	55​
3​	6/10/2015​	6/16/2015​	111111111​	Cereal	55​
4​	6/10/2015​	6/16/2015​	111111111​	Cereal	55​
5​	6/10/2015​	6/16/2015​	111111111​	Cereal	55​
6​	6/10/2015​	6/16/2015​	4011​	Bananas	61​
7​	6/10/2015​	6/16/2015​	20401100000​	Bananas	61​
8​	6/10/2015​	6/16/2015​	111111111​	Cereal	61​
9​	6/10/2015​	6/16/2015​	111111111​	Cereal	61​
10​	6/10/2015​	6/16/2015​	111111111​	Cereal	61​
11​	6/10/2015​	6/16/2015​	4011​	Bananas	191​
12​	6/10/2015​	6/16/2015​	20401100000​	Bananas	191​
13​	6/10/2015​	6/16/2015​	111111111​	Cereal	191​
14​	6/10/2015​	6/16/2015​	111111111​	Cereal	191​
15​	6/10/2015​	6/16/2015​	111111111​	Cereal	191​
16​	6/10/2015​	6/16/2015​	4011​	Bananas	420​
17​	6/10/2015​	6/16/2015​	20401100000​	Bananas	420​
18​	6/10/2015​	6/16/2015​	111111111​	Cereal	420​
19​	6/10/2015​	6/16/2015​	111111111​	Cereal	420​
20​	6/10/2015​	6/16/2015​	111111111​	Cereal	420​
21​	6/10/2015​	6/16/2015​	4011​	Bananas	491​
22​	6/10/2015​	6/16/2015​	20401100000​	Bananas	491​
23​	6/10/2015​	6/16/2015​	111111111​	Cereal	491​
24​	6/10/2015​	6/16/2015​	111111111​	Cereal	491​
25​	6/10/2015​	6/16/2015​	111111111​	Cereal	491​
26​	6/10/2015​	6/16/2015​	4011​	Bananas	545​
27​	6/10/2015​	6/16/2015​	20401100000​	Bananas	545​
28​	6/10/2015​	6/16/2015​	111111111​	Cereal	545​
29​	6/10/2015​	6/16/2015​	111111111​	Cereal	545​
30​	6/10/2015​	6/16/2015​	111111111​	Cereal	545​
31​	6/10/2015​	6/16/2015​	4011​	Bananas	546​
32​	6/10/2015​	6/16/2015​	20401100000​	Bananas	546​
33​	6/10/2015​	6/16/2015​	111111111​	Cereal	546​
34​	6/10/2015​	6/16/2015​	111111111​	Cereal	546​
35​	6/10/2015​	6/16/2015​	111111111​	Cereal	546​
36​	6/10/2015​	6/16/2015​	4011​	Bananas	552​
37​	6/10/2015​	6/16/2015​	20401100000​	Bananas	552​
38​	6/10/2015​	6/16/2015​	111111111​	Cereal	552​
39​	6/10/2015​	6/16/2015​	111111111​	Cereal	552​
40​	6/10/2015​	6/16/2015​	111111111​	Cereal	552​
41​	6/10/2015​	6/16/2015​	4011​	Bananas	653​
42​	6/10/2015​	6/16/2015​	20401100000​	Bananas	653​
43​	6/10/2015​	6/16/2015​	111111111​	Cereal	653​
44​	6/10/2015​	6/16/2015​	111111111​	Cereal	653​
45​	6/10/2015​	6/16/2015​	111111111​	Cereal	653​

Sheet3

Any help is appreciated!!!

 

[RudiS]

Welcome in....

Is there any logical sequence to the numbers?

 

[RudiS]

Try this code...

Sub PasteData()
Dim rD As Range
Dim rN As Range
Dim lR As Long
Dim i As Integer
    
    Set rD = Range("A1").CurrentRegion
    Set rN = rD.Offset(0, rD.Columns.Count).Columns(1)
    rN.Value = 55
    Application.ScreenUpdating = False
    For i = 1 To 8
        lR = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
        rD.Copy Cells(lR, 1)
        Set rN = rD.Offset(lR - 1, rD.Columns.Count).Columns(1)
        rN.Value = Choose(i, 61, 191, 420, 491, 545, 546, 552, 653)
    Next i
    Application.ScreenUpdating = True


End Sub

 

[cmschlatt]

Welcome in....

Is there any logical sequence to the numbers?

The numbers in column C are UPCs to products, so they will always be different. Column E shows store numbers that will always be one of the nine stores (i.e. 55, 61, 191, etc)

 

[cmschlatt]

Try this code...

Sub PasteData()
Dim rD As Range
Dim rN As Range
Dim lR As Long
Dim i As Integer
    
    Set rD = Range("A1").CurrentRegion
    Set rN = rD.Offset(0, rD.Columns.Count).Columns(1)
    rN.Value = 55
    Application.ScreenUpdating = False
    For i = 1 To 8
        lR = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
        rD.Copy Cells(lR, 1)
        Set rN = rD.Offset(lR - 1, rD.Columns.Count).Columns(1)
        rN.Value = Choose(i, 61, 191, 420, 491, 545, 546, 552, 653)
    Next i
    Application.ScreenUpdating = True


End Sub

Thanks for the reply. I get an error message.

Run-time error '1004'
PasteSpecial method of Range class failed.

 

[RudiS]

Is the macro running based on the same scenario and environment as you specified in your opening post?
I used the data in your OP and constructed the macro from that and it pastes without error?

 

[cmschlatt]

Yes, I am using the same sample. I highlighted the data and ran the macro, but still got an error.

Excel 2013

Row\Col	A​	B​	C​	D​
1​	6/10/2015​	6/16/2015​	4011​	Bananas
2​	6/10/2015​	6/16/2015​	20401100000​	Bananas
3​	6/10/2015​	6/16/2015​	111111111​	Cereal
4​	6/10/2015​	6/16/2015​	111111111​	Cereal
5​	6/10/2015​	6/16/2015​	111111111​	Cereal

Sheet3

The above is a simplified sample of data. I figured I could customize as needed once I got some starting code, but looking at what you just posted, I'm lost

This is a representation of actual data I'll be using. The "store numbers" would need to go in column Q

Excel 2013

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

1​

2015/06/10​

1​

2015/06/16​

29913600000​

Quesadillas

2​

2015/06/10​

1​

2015/06/16​

29698200000​

Quesadillas

3​

2015/06/10​

1​

2015/06/16​

29916700000​

Beer Friendly Dips and Spreads

4​

2015/06/10​

1​

2015/06/16​

26480700000​

Beer Friendly Dips and Spreads

5​

2015/06/10​

1​

2015/06/16​

26428300000​

Beer Friendly Dips and Spreads

6​

2015/06/10​

1​

2015/06/16​

29524800000​

Beer Friendly Dips and Spreads

7​

2015/06/10​

1​

2015/06/16​

29932800000​

Beer Friendly Dips and Spreads

8​

2015/06/10​

1​

2015/06/16​

82935410001​

Sun Tropics Nectars

9​

2015/06/10​

1​

2015/06/16​

82935410002​

Sun Tropics Nectars

10​

2015/06/10​

1​

2015/06/16​

82935410009​

Sun Tropics Nectars

11​

2015/06/10​

1​

2015/06/16​

82935410026​

Sun Tropics Nectars

12​

2015/06/10​

1​

2015/06/16​

82935410068​

Sun Tropics Nectars

13​

2015/06/10​

1​

2015/06/16​

82935410025​

Sun Tropics Nectars

14​

2015/06/10​

1​

2015/06/16​

82935410122​

Sun Tropics Nectars

15​

2015/06/10​

1​

2015/06/16​

25823200000​

GRASKAAS

Sheet3

 

[RudiS]

I cannot account for the paste error? Not sure why that is happening as there is not even a pastespecial command in my code. Are there other macros running in the Excel environment like events being triggered??? I could add some code to disable that.

I'll send you a workbook with the sheet and code I have set up that runs as I mentioned.

Looking at your actual data, there are a lot of empty columns. Are these actually empty? I just need to find out as it will affect the macro.
Please confirm...

 

[cmschlatt]

When you're running the macro, do you highlight everything to be copied, or do something else?

Looking at it again, I can make it so there are no columns in between and the data starts out like this:

Excel 2013

Row\Col	A​	B​	C​	D​	E​
1​	2015/06/10​	1​	2015/06/16​	29913600000​	Quesadillas
2​	2015/06/10​	1​	2015/06/16​	29698200000​	Quesadillas
3​	2015/06/10​	1​	2015/06/16​	29916700000​	Beer Friendly Dips and Spreads
4​	2015/06/10​	1​	2015/06/16​	26480700000​	Beer Friendly Dips and Spreads
5​	2015/06/10​	1​	2015/06/16​	26428300000​	Beer Friendly Dips and Spreads
6​	2015/06/10​	1​	2015/06/16​	29524800000​	Beer Friendly Dips and Spreads
7​	2015/06/10​	1​	2015/06/16​	29932800000​	Beer Friendly Dips and Spreads
8​	2015/06/10​	1​	2015/06/16​	82935410001​	Sun Tropics Nectars
9​	2015/06/10​	1​	2015/06/16​	82935410002​	Sun Tropics Nectars
10​	2015/06/10​	1​	2015/06/16​	82935410009​	Sun Tropics Nectars
11​	2015/06/10​	1​	2015/06/16​	82935410026​	Sun Tropics Nectars
12​	2015/06/10​	1​	2015/06/16​	82935410068​	Sun Tropics Nectars
13​	2015/06/10​	1​	2015/06/16​	82935410025​	Sun Tropics Nectars
14​	2015/06/10​	1​	2015/06/16​	82935410122​	Sun Tropics Nectars
15​	2015/06/10​	1​	2015/06/16​	25823200000​	GRASKAAS

Sheet3

Thanks for all your help. A tool like this will be very helpful in saving some time throughout my day!

 

[RudiS]

See the workbook here that runs the code properly for me...

You don't need to select anything....just run the macro.
The macro assumes the data starts in cell A1. It will copy the initial block of data and paste it 8 more times below each other and assign the codes to the right.
Just run it...no selections needed.

I'll update the macro to run on the new data you posted (without the blank columns) (Note - blank columns will cause the macro to malfunction!!)

BTW: It' a pleasure to assist

EDIT.... As a matter of fact, if you paste the new data into cell A1 and run the macro, it will process the new data as effectively as the original mini sample in your OP.

Just confirm if the paste issue is resolved in the workbook I sent you!