Asking user input to copy sheets in VBA

Question

I want to copy sheets from a particular workbook in VBA to my Active Workbook and do a lot of calculations using it. Now the problem is that the target sheet name always keeps on changing and I always get an error.

Set targetWorkbook = Application.ActiveWorkbook
filter = "Text files (*.xls*),*.xls*"
Caption = "Please Select the Target file"
Ret = Application.GetOpenFilename(filter, , Caption)

If Ret = False Then Exit Sub
Application.AskToUpdateLinks = False
Set wb = Workbooks.Open(Ret)
Application.AskToUpdateLinks = True
wb.Worksheets("**This Keeeps on Changing**").Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)

Can I select or Input the name in a MsgBox or something similar so that I dont get an error. Please help.

Set the worksheet being copied to a variable, now you have a reference to it and can continue your code using the variable as well as change the name at the same time. [Here is an example](http://stackoverflow.com/a/7692456/2521004) by Tim Williams — Automate This, Apr 22 '15 at 20:25
And if you wanted to go down the road you are suggestion, `mySheet = inputbox(...` should do the trick. — Matt Cremeens, Apr 22 '15 at 21:25

score 1 · Answer 1 · answered Apr 23 '15 at 14:56

Set targetWorkbook = Application.ActiveWorkbook
Filter = "Text files (*.xls*),*.xls*"
Caption = "Please Select the Target file"
Ret = Application.GetOpenFilename(Filter, , Caption)

If Ret = False Then Exit Sub
Set wb = Workbooks.Open(Ret, False) 'why set it on application level when it's an optional argument?

For Each Worksheet In wb
    If LCase(Worksheet.Name) Like "*changing*" Then                              'it might be changing but it probably has a fixed part, right? 'note: you can use wildcards and string conversion rules
        Worksheet.Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count) 'do whatever it did before
    End If
Next

score 1 · Accepted Answer · answered Apr 23 '15 at 15:01

Sub ertdfgcvb()
Set targetWorkbook = Application.ActiveWorkbook
Filter = "Text files (*.xls*),*.xls*"
Caption = "Please Select the Target file"
Ret = Application.GetOpenFilename(Filter, , Caption)

If Ret = False Then Exit Sub
Set wb = Workbooks.Open(Ret, False) 'why set it on application level when it's an optional argument?

shname = InputBox("What worksheet are you looking for?", "Changing sheet names are for losers")
For Each Worksheet In wb
    If LCase(Worksheet.Name) Like "*" & LCase(shname) & "*" Then                              'it might be changing but it probably has a fixed part, right? 'note: you can use wildcards and string conversion rules
        Worksheet.Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count) 'do whatever it did before
    End If
Next
End Sub

Got it. Will try to use this. Its just the data I use of a file not updated by me and have seen sheet names changing, so just wanted to make it general. Thanks — Meesha, Apr 23 '15 at 15:16

Asking user input to copy sheets in VBA

2 Answers2