Solved copy/paste selection range based on a cell value

February 26, 2013 at 23:57:37
Specs: Windows 7
Hello,

i'm trying to build a macro and assign it to a button for:


- copy/paste selection value
copy from sheet1 range A2:E4 based on a number in colomn F(cell F2)
paste to sheet 2
- copy/paste selection value
copy from sheet1 range A5:E7 based on a number in colomn F(cell F5)
paste to sheet 2

and continue like this.
if there is nothing in colomn F(cell F..) just to pass it and move forward.

for copying i want to be copied like this in sheet 2
for example
5 times for A2:E4 range from sheet1
2 times for A5:E7 range from sheet1
to copy like this in sheet 2
1.1-A2:E4
1.2-A5:E7
1.3-A8:E10
1.4-A11:E13
1.5-A14:E16
2.1-A17:E19
2.2-A20:E22


Thank you very much in advance for the time and help.

Best Regards
D.D.


See More: copy/paste selection range based on a cell value

Report •


✔ Best Answer
February 28, 2013 at 09:49:39
Try this...

Sub Copy_F2F5_Count()
'Copy A2:E4 based on F2
 numCopies = Sheets(1).Range("F2")
  For copyRange = 1 To numCopies
    nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
     Sheets(1).Range("A2:E4").Copy _
       Destination:=Sheets(2).Range("A" & nxtRow)
  Next
'Copy A5:E7 based on F5
 numCopies = Sheets(1).Range("F5")
  For copyRange = 1 To numCopies
    nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
     Sheets(1).Range("A5:E7").Copy _
       Destination:=Sheets(2).Range("A" & nxtRow)
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 27, 2013 at 03:58:10
I'd like to help, but I don't understand what you are trying to do.

I don't understand what "5 times for A2:E4", etc. means. I also don't know what any of the 1.1, 1.2 lines mean.

Perhaps it would help if you posted some example data. Please click on the following line and read the instructions found via that link before posting your examples.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 27, 2013 at 06:32:36
Hello thank you very much for the fast reply.

i have a big sheet of data(sheet1). i want to copy multiple times based on a number in column F(for example cell F2) the range of cell A2:E4
and continue like this multiple times the next range A5:E7 based on a number in column F(for example cell F5)
and continue like this.
the copy i want to be in sheet2
1.1 1.2 was just to show in which order i want the copy's. i do not want this in the sheet2.


thank you


Report •

#3
February 27, 2013 at 09:39:03
You don't say anything about where in Sheet 2 you want the ranges to be copied to.

If the code copies A2:E4 5 times, it has to be pasted someplace but we don't know where. "Sheet2" is not specific enough.

The same goes for A5:E7. Where does it get pasted after each copy?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 27, 2013 at 13:07:26
i want to be pasted one below the other one and continue like this in sheet2

for example if A2:E4 should be copied 5 times (from sheet1) it should be pasted like this in sheet 2
A2:E4
A5:E7
A8:E10
A11:E13
A14:E16
after this continue to paste the next range a5:E7 from sheet1 and continue like this.

Thank You very much.

Best Regards
D.D


Report •

#5
February 28, 2013 at 09:49:39
✔ Best Answer
Try this...

Sub Copy_F2F5_Count()
'Copy A2:E4 based on F2
 numCopies = Sheets(1).Range("F2")
  For copyRange = 1 To numCopies
    nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
     Sheets(1).Range("A2:E4").Copy _
       Destination:=Sheets(2).Range("A" & nxtRow)
  Next
'Copy A5:E7 based on F5
 numCopies = Sheets(1).Range("F5")
  For copyRange = 1 To numCopies
    nxtRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
     Sheets(1).Range("A5:E7").Copy _
       Destination:=Sheets(2).Range("A" & nxtRow)
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 28, 2013 at 10:20:17
Thank you so much it is working but,
i forgot to add that in the range (A2:E4),(A5:E7) and etc. i have some boxes which are merged can you make it to work with merget cells and to keep the same format of the cells like the ones from sheet1.

Thank you so much.

Best Regards
D.D


Report •

#7
February 28, 2013 at 11:28:38
re: "i have some boxes which are merged..."

What do you mean by "boxes"?

re: "can you make it to work with merget cells and to keep the same format of the cells like the ones from sheet1."

Obviously I can't see your spreadsheet from where I'm sitting, but when I run the code against my test data, it copies all formatting including merged cells (both horizontally and vertically merged), cells filled with colors, italics, bolds, underlined and different fonts and font sizes. In other words, it copies all formatting from Sheet1 to Sheet2.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 3, 2013 at 23:19:25
Thank you very much for the help. I solved the case.

Best Regards
D.D


Report •


Ask Question