Combo box copy and paste

G

gavmer

Hi all,

Quick question. I have a combo box i want to use to copy columns fro
one sheet to another. The criteria is:

704 - Copy column a sheet1 to column D sheet2
706 - Copy column b sheet1 to column D sheet2
708 - Copy column c sheet1 to column D sheet2
710 - Copy column d sheet1 to column D sheet2

704,706,etc is the fill range. Can someone please provide the code t
copy the columns if selected. Note also there is protection....

Thankn you all in advance!!
 
D

Dave Peterson

I'm not sure what kind of combobox you're using, but once you get the value, you
could do something like:

Option Explicit
Sub testme()

Dim myVal As String
Dim fRng As Range
Dim tRng As Range

Set fRng = Nothing
Set tRng = Worksheets("sheet2").Range("d1")

myVal = "706" 'whatever is in the combobox

Select Case myVal
Case Is = "704"
Set fRng = Worksheets("sheet1").Range("a1")
Case Is = "706"
Set fRng = Worksheets("sheet1").Range("b1")
Case Is = "708"
Set fRng = Worksheets("sheet1").Range("c1")
Case Is = "710"
Set fRng = Worksheets("sheet1").Range("d1")
Case Else
'do nothing???
End Select

If fRng Is Nothing Then
'not 704, 706, 708, 710...
'so do nothing
Else
fRng.EntireColumn.Copy _
Destination:=tRng
End If

End Sub
 
G

gavmer

Hi Dave,

Thanks for your assistance. I got it to copy once but not again after
The selelctions i listed are the only ones and im not sure by what yo
mean whatever is in the combo box. Can you set up completely based o
the selections i hav given. Will protection of cells/sheet be
problem.

Thank you in advance Dave.

Cheers!!!!
 
D

Dave Peterson

Your combobox gets populated some way.

Since you didn't say where the combobox is (on a worksheet or a userform??) and
what it's named, I didn't know how to get that value.

maybe...
myVal = "706" 'whatever is in the combobox

becomes
myVal = worksheets("sheet1").combobox1.value
 
Top