Help with Promting for wooksheet name in macro.

  • Thread starter Thread starter Leatherfoot
  • Start date Start date
L

Leatherfoot

I'm trying to get the macro to let me pick the name on the worksheet t
paste the info into. Here's what i have but it gives me an error
(run-time error '9' subscript out of range)

Help a newbie please! :)


Windows("CUP_Results.xls").Activate
Sheets("Pro Calc").Select
Range("A1:AF100").Select
Selection.Copy
Sheets(strWorksheet = InputBox("Please enter the name of th
worksheet")).Select
ActiveSheet.Past
 
Hi
not tested but try:
Dim target_name

Windows("CUP_Results.xls").Activate
target_name = InputBox("Please enter the name of theworksheet")
Sheets("Pro Calc").Range("A1:AF100").copy
Sheets(target_name).paste
 
Change

Sheets(strWorksheet = InputBox("Please enter the name of the
worksheet")).Select

to

sSheet = InputBox("Please enter the name of the worksheet")
if sSheet <> "" Then
Sheets(sSHeet).Select
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks so much guys... I copied your suggestion into the macro Bob and
it worked perfectly. :)
 
Back
Top