Macros that copy certain range to another cell conditionally

burnin2emax

New Member
Joined
Nov 9, 2005
Messages
10
Hi there,

I need help in a macro to perform the following.

The situation is as such :
I have a cell C6 waiting for user input.
I want to create a control button which runs a macro which will copy certain range of cells according to the user input.

For example, when a user keys in "a" at cell C6, when the macro is run, columns(A:D) of Worksheet A are copied to columns(B:E) of Worksheet B.
When a user keys in "b" at cell C6, when the macro is run, columns(E:H) of Worksheet A are copied to columns(B:E) of Worksheet B.

Hope someone can help.

Thanks in advance.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
paste the code onto sheet module
Code:
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim txt As String
With Target.Cells(1, 1)
    If .Address(0, 0) <> "C6" Then Exit Sub
    Select Case .Value
        Case "a"
            txt = "A:D"
        Case "b"
            txt = "E:H"
        Case Else
            Exit Sub
    End Select
    Me.Range(txt).Copy Sheets("sheet2").Range("b1")
End With
End Sub
 

burnin2emax

New Member
Joined
Nov 9, 2005
Messages
10
Hi ,

Being the noob that I am, I don't quite grasp what's happening in the code that was given. Perhaps, someone can enlighten me.

As mentioned in earlier post, I want to create a control button which runs a macro which copy certain range of cells according to a user input and copy it into another sheet.

Situation :
If user input "a" into cell C6, when the macro is run, the range(A1:D1) of "worksheet A" are copied onto (A5:D5) of another worksheet, "worksheet B".
If user input "b" into cell C6, when the macro is run, the range(A2:D2) of "worksheet A" are copied onto (A5:D5) of another worksheet, "worksheet B".

Thanks in advance for the help...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,334
Messages
5,571,589
Members
412,407
Latest member
ElmerCC
Top