Copy & Pasting cells using a number in a cell

Nick_Somerset

New member
Joined
May 20, 2014
Messages
6
Reaction score
0
Points
0
Evening all,

I would like to copy a range of cells, lets say A1:B10 and then copy this range of cells a defined number of times into different locations each time.

The "number of times" will be determined by a number in a cell, lets say D1.

Location of the copied cells can be A20, D20 & G20. (3 locations so therfore the number in cell D1 will be 3)

Can anyone help me with this?

Many Thanks
Nick
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
try:
Code:
Sub blah()
For i = 1 To Range("D1").Value
  Range("A1:B10").Copy Cells(20, (i - 1) * 3 + 1)
Next i
End Sub
Works on the currently active sheet.
 

Nick_Somerset

New member
Joined
May 20, 2014
Messages
6
Reaction score
0
Points
0
Great thankyou.

How can I apply this macro so when I open this excel document and enter a number into the D1 cell it changes the number of times it pastes the cells in range A1:B10? ie, it is always active.

If the number is above 3 then a new position based on a pattern will need to be set. The next location will be J20 for example.

Hope you can help
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
If the number is above 3 then a new position based on a pattern will need to be set. The next location will be J20 for example.
The next location already is J20, no new pattern needed there.





How can I apply this macro so when I open this excel document and enter a number into the D1 cell it changes the number of times it pastes the cells in range A1:B10? ie, it is always active.
This in the sheet concerned's code module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
  For i = 1 To Range("D1").Value
    Range("A1:B10").Copy Cells(20, (i - 1) * 3 + 1)
  Next i
End If
End Sub
 

Nick_Somerset

New member
Joined
May 20, 2014
Messages
6
Reaction score
0
Points
0
So I have the first bit of code in book1 code window and the second bit of code in the sheet concerned code module.

I was hoping that when I open the document and type a number into D1 that many pastes are made in the cells below and If I changed that number in cell D1 then the pastes below would change again. Each time I did it without having to run the macro.

Is this possible?

Thanks
Nick
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
So I have the first bit of code in book1 code window and the second bit of code in the sheet concerned code module.
you only need the code the sheet's code module. You should delete the code anywhere else.



I was hoping that when I open the document and type a number into D1 that many pastes are made in the cells below
it does this.



and If I changed that number in cell D1 then the pastes below would change again.
It does this, but bear in mind that no deleteing is done prior to the pasting, so if data is already there it will overwrite it and if the number in D1 is reduced the previous pastes will not be deleted.



Each time I did it without having to run the macro.
Excellent!



Is this possible?
It already is happening.
 

Nick_Somerset

New member
Joined
May 20, 2014
Messages
6
Reaction score
0
Points
0
Haha! Yes! It works... as you know. Thankyou.

So next step...

If i type 1 in the D1 box it pastes 1. OK.

If i type 5 it pastes 5. OK.

Can we get it to go from 5 to 4 for example? ie delete the already pasted cells?

Go on... i know you can do it!? :)
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
if you're happy clearing entire rows 20:30 on the sheet prior to pasting then:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
Rows("20:30").Clear ' or .ClearContents
  For i = 1 To Range("D1").Value
    Range("A1:B10").Copy Cells(20, (i - 1) * 3 + 1)
  Next i
End If
End Sub
 

Nick_Somerset

New member
Joined
May 20, 2014
Messages
6
Reaction score
0
Points
0
Good answer. Boom!

So lets say the range of cells (A1:B10) were all called "TEST" for example. Id now like to call one of those cells "TEST1".

So lets say we call cell A1 "TEST1".

Every time it pastes id like TEST1 t become TEST2, TEST3 etc...

Hope this makes sense? Can this be done?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$1" Then
  Rows("20:30").Clear  ' or .ClearContents
  For i = 1 To Range("D1").Value
    Set Destn = Cells(20, (i - 1) * 3 + 1)
    Range("A1:B10").Copy Destn
    Destn.Value = Destn.Value & i
  Next i
End If
End Sub
 

Nick_Somerset

New member
Joined
May 20, 2014
Messages
6
Reaction score
0
Points
0
Again thankyou.

I think we are about half way through what i want to achieve. Do you think it would be possible if i sent you an excel document and asked you to do a few more tweaks to it. Similar to our previous posts?

It is currently hurting my head! Not so much yours i imagine...
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
Sent you a Private Message.
 
Top