dynamic sheet copy
 

I wondered if anyone could help me modify this line of code,
so instead of it copying the sheet with index 1 it would be dynamic, so that
the user could input a string and this is the sheet that would be copied.

mybook.Worksheets(1).Copy after:= _
bsebk.Sheets(bsebk.Sheets.Count)

The routine is based around Ron de Bruin's sheet copy macro, I am just
trying to get to grips with generalising it.

Thanks in advance

dynamic sheet copy
 

try
mysheet=inputbox("Enter sheet name"
mybook.Worksheets(mysheet).Copy after:= _
bsebk.Sheets(bsebk.Sheets.Count)



--
Don Guillett
SalesAid Software

"John" wrote in message
...
I wondered if anyone could help me modify this line of code,
so instead of it copying the sheet with index 1 it would be dynamic, so
that
the user could input a string and this is the sheet that would be copied.

mybook.Worksheets(1).Copy after:= _
bsebk.Sheets(bsebk.Sheets.Count)

The routine is based around Ron de Bruin's sheet copy macro, I am just
trying to get to grips with generalising it.

Thanks in advance

dynamic sheet copy
 

Sub TryThis()
Dim mySheetName
Dim Sht As Worksheet
Dim SheetExist As Boolean
mySheetName = Application.InputBox(prompt:=Chr(10) & "Insert th
sheet NAME!", Type:=2)
If mySheetName = False Then Exit Sub
For Each Sht In ThisWorkbook.Sheets
If LCase(Sht.Name) = LCase(mySheetName) Then
SheetExist = True
Exit For
End If
Next Sht
If SheetExist = True Then
Sheets(mySheetName).Cop
after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
Else
MsgBox "The sheet name you inserted does not exist"
vbInformation + vbOKOnly, "I'm so Sorry, but:"
End If
End Su

--
Message posted from http://www.ExcelForum.com

dynamic sheet copy
 

I cant seem to get in in without an error occuring in the final lines,
can you help, maybe i'm putting the breaks in the wrong place, if this works I
can add it to the rest (From Ron) which loops around, that would be
brilliant!!!

I have to say I was surprised by how quickly someone got back to me

Thanks again for looking at this


"Andoni " wrote:

If SheetExist = True Then
Sheets(mySheetName).Copy
after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
Else
MsgBox "The sheet name you inserted does not exist",
vbInformation + vbOKOnly, "I'm so Sorry, but:"
End If



---
Message posted from http://www.ExcelForum.com/

dynamic sheet copy
 

I got it to work, however, my version XL97 doesnt seem to like
the End If statement. I'll play with this over the weekend to see if I
can put all of the code together.

Thanks again

John

dynamic sheet copy
 

If you have an IF then you need an end if IF more than one line

if tt then
ddd
end if

or
if tt then ddd

no end if needed

--
Don Guillett
SalesAid Software

"John" wrote in message
...

I got it to work, however, my version XL97 doesnt seem to like
the End If statement. I'll play with this over the weekend to see if I
can put all of the code together.

Thanks again

John