Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet with dynamic chart? | Charts and Charting in Excel | |||
Dynamic column chart - copying from Sheet to Sheet. | Excel Discussion (Misc queries) | |||
Dynamic column chart - copying from Sheet to Sheet. | Charts and Charting in Excel | |||
Reference / Copy Dynamic Data | Excel Discussion (Misc queries) | |||
How to Copy worksheet to new workbook with dynamic name? | Excel Programming |