Help with copying and pasting from user input

[KimberlyC]

Hi


I'm trying to write code to make the following happen:

I need the user to be able to copy a specific range (A4:M7) from another
worksheet in the activeworkbook to the active worksheet that they are on.

I need them to be able to click a button (once they are viewing the active
worksheet that they want the range copied to) that will bring up a user form
displaying the active worksheets in the workbook. Then, I would like for
them to select the worksheet in the userform that they would like to copy
cells (A4:M7) from...click ok and those cells are pasted to the active
worksheet.
However... in the listbox of active worksheets.. I only want worksheets
listed that start with the name Worksheet, Worksheet (2), Worksheet (3),
..... and so on .. there could be as many as 50 of these worksheets ( and a
minimum of just Worksheet and Worksheet (2) )depending on how many the user
has added (they add these worksheets via code I've written).

I'm not sure how to make this all come together...
Any help would be greatly appreciated..
Thanks in advance.
Kimberly

 

[Tom Ogilvy]

Private Sub Userform_Initialize()
Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
if lcase(left(sh.name,9)) = "worksheet" then
Listbox1.AddItem sh.name
end if
Next
End Sub

Private Sub Listbox1_Click()
With Listbox1
if .listindex <> -1 then
worksheets(Listbox1.Value).Range("A4:M7").copy _
Destination:=Activecell
end if
End with
End Sub

 

[KimberlyC]

Thanks so much Tom..

It's works perfectly!!!

Private Sub Userform_Initialize()
Dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
if lcase(left(sh.name,9)) = "worksheet" then
Listbox1.AddItem sh.name
end if
Next
End Sub

Private Sub Listbox1_Click()
With Listbox1
if .listindex <> -1 then
worksheets(Listbox1.Value).Range("A4:M7").copy _
Destination:=Activecell
end if
End with
End Sub