Advice for code copy / paste userform to cell range

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
On my worksheet called Sheet1 i have a range I26:I35 with values in.
My intensions are to press a transfer button on a userform & select a Month.
This will copy the cell range values shown above to 1 of 13 possible paste locations on the same sheet depending on the MONTH selected from the listbox.

Here is some info for you.
Worksheet called SUMMARY SHEET
Cell Range to copy values from I26:I35

These MONTH NAMES are loaded into the ComboBox1
Selecting a MONTH NAME & then pressing the transfer button will paste the values to location specific to the MONTH NAME

Transfer button is called TRANSFER_VALUE_BUTTON
comboBox where Months are loaded is called ComboBox1

APRIL START Cell Range to paste to B4:B13
MAY Cell Range to paste to E4:E13
JUNE Cell Range to paste to B19:B28
JULY Cell Range to paste to E19:E28
AUGUST Cell Range to paste to B34:B43
SEPTEMBER Cell Range to paste to E34:E43
OCTOBER Cell Range to paste to B49:B58
NOVEMBER Cell Range to paste to E49:E58
DECEMBER Cell Range to paste to B64:B73
JANUARY Cell Range to paste to E64:B73
FEBRUARY Cell Range to paste to B79:B88
MARCH Cell Range to paste to E79:E88
APRIL END Cell Range to paste to B94:B108


Example.
I open userform & select DECEMBER from the drop down option list.
I then press the TRANSFER BUTTON.
The values in the Range I26:I35 are copied & then pasted into the Range B64:B73
Once done i should see a confirmation msgbox & the ComboBox will then reset itself so DECEMBER is no longer selected.

Many thanks
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,071
Here are the codes I used to perform this task.
First I loaded the Month names into the Combobox

Then I put this code into the Command Button.

You will see I did January and February
I will let you do the other 10 Months.
You should see how I did the first two and just do the other 10 the same.

Code:
Private Sub TRANSFER_VALUE_BUTTON_Click()
'Modified  11/2/2019  10:37:46 AM  EDT
With Range("I26:I35")
    Select Case ComboBox1.Value
        Case "January"
            .Copy Range("E64:B73")
        Case "February"
            .Copy Range("B79:B88")
    End Select
End With
MsgBox "Done"
End Sub
Private Sub UserForm_Initialize()
'Modified  11/2/2019  10:37:46 AM  EDT
With ComboBox1
    .Clear
    .AddItem "January"
    .AddItem "February"
    .AddItem "March"
    .AddItem "April"
    .AddItem "May"
    .AddItem "June"
    .AddItem "July"
    .AddItem "August"
    .AddItem "September"
    .AddItem "October"
    .AddItem "November"
    .AddItem "December"
End With
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,859
Office Version
2007
Platform
Windows
The button is not necessary, just by changing the month in the combo the data will be copied. Try this:

Note: The ranges and months are already settled.
Code:
Private Sub ComboBox1_Change()
  Dim rngs As Variant
  rngs = Array([COLOR=#008000]"B4:B13", "E4:E13", "B19:B28", "E19:E28", "B34:B43", "E34:E43", _[/COLOR]
[COLOR=#008000]               "B49:B58", "E49:E58", "B64:B73", "E64:B73", "B79:B88", "E79:E88", "B94:B103"[/COLOR])
  If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then Exit Sub
  Range(rngs(ComboBox1.ListIndex)).Value = Range("I26:I35").Value
End Sub


Private Sub UserForm_Activate()
  ComboBox1.List = Array([COLOR=#0000ff]"APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER", _[/COLOR]
[COLOR=#0000ff]          "OCTOBER", "NOVEMBER", "DECEMBER", "JANUARY", "FEBRUARY", "MARCH", "APRIL"[/COLOR])
End Sub
 

Forum statistics

Threads
1,078,309
Messages
5,339,410
Members
399,302
Latest member
Swiftymj

Some videos you may like

This Week's Hot Topics

Top