Solved Copy and paste 3 rows a number of times based on cell value

January 3, 2018 at 05:31:54
Specs: Windows 10
Hi. I've tried to find the answers I need but can't work out how to stitch it all together.

I have a spreadsheet form on a worksheet called "Aide memoire" designed to capture data and be emailed to one of several parties depending on a value selected in cell B10. I know how to create a 'Submit' button and attach a macro to it that will attach the workbook to an open email window with a set address, but don't know how to code it so that the address changes depending on the value selected in B10.

In addition, I also need to copy and paste directly beneath the values in A15:A17, a set number of times determined by the value entered in cell B15. So if the user entered '4' in B15, you'd see the data below duplicated in A18:A20, A21:A23 and A24:A26.

A15 = "Record name to be linked"
A16 = "Ref Number"
A17 = "ABC number if applicable"

Ideally I'd like the rows inserted so that I can keep a banner at the bottom of the form, but I'm asking for perfection with that and would be happy just to accomplish the copying and pasting.

I'm a beginner in terms of VBA and just try and grab bits of code and struggle to adapt it to my needs for jobs like this. Thanks very much in advance. Please let me know if you need more information.


See More: Copy and paste 3 rows a number of times based on cell value

Report •

#1
January 3, 2018 at 11:17:06
✔ Best Answer
Without knowing what your email code looks like, it's hard to offer a specific solution to your recipient question. That said, something like this might work:

Sub MailToWho()
 Select Case Range("B10")
   Case 5
     myRecipient = "abc@gmail.com"
   Case 10
     myRecipient = "def@gmail.com"
   Case 15
     myRecipient = "ghi@gmail.com"
 End Select
 
'Code to create email goes here
'This line sets the recipient
 .To = myRecipient
End Sub

As far as your Copy-Paste, Keep Banner question, try this:

Sub PasteByB15()
 If Range("B15") > 1 Then
   Range("A15:A17").Copy
   Range("A18:A" & 17 + (Range("B15") - 1) * 3).Insert
 End If
End Sub

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

message edited by DerbyDad03


Report •

#2
January 3, 2018 at 11:25:32
This might help with your quest for VBA knowledge...

https://www.computing.net/howtos/sh...

message edited by DerbyDad03


Report •

#3
January 4, 2018 at 05:02:15
DerbyDad03 you're brilliant! Thank you so much. I've managed to adapt what you wrote successfully for my specific needs. It's been real fun. Thank you for being so helpful.

Report •
Related Solutions


Ask Question