CODEWITHSUNDEEP
vbaexcel
Meesha
Meesha

Reputation: 821

Asking user input to copy sheets in VBA

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.

Upvotes: 0

Views: 760

Answers (2)

user3819867
user3819867

Reputation: 1118

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

Upvotes: 1

user3819867
user3819867

Reputation: 1118

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

Upvotes: 1

Related Questions