Help in retrieving data from a worksheet of a different workbook

Hello Experts,
The below code works if  I have "Template Tab" and "Forms" tab both present in the same workbook. But If I have a "Forms" tab coming from a Workbook2 which is closed and saved at a location and MyActiveWorkbook has a Template Tab, what modifications do I need to make to the below code in order to achieve my goal without coping the "Forms" Tab into the Active Workbook? Any help would be appreciated

Below code takes the names present in the Column A of Forms Tab and Creates different worksheet with the same names. All the newly formed worksheets will have same template as the template tab

Below Code:

Sub createWorksheets()

    Dim r As Long, TemplateWS As Worksheet
    Set TemplateWS = Worksheets("Template Tab")
    For r = 2 To Worksheets("Forms").Cells(Rows.Count, "A").End(xlUp).Row
        If Worksheets("Forms").Cells(r, "D") = True Then
            TemplateWS.Copy after:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = Worksheets("Forms").Cells(r, "A")
        End If
    Next
   
End Sub
Milind AgarwalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
soulintox,

Quick question are you comfortable..writting a process which opens the workbook which has form tab..copying it over in this workbook..and then creating the necessary templates file and then deleting the form tab??

Saurabh...
Milind AgarwalAuthor Commented:
I think that would work.
Saurabh Singh TeotiaCommented:
You can do something like this assuming the workbook where you are looking for is known as Book1..

Sub createWorksheets()
Application.DisplayAlerts = False
Application.ScreenUpdating = False


    Dim r As Long, TemplateWS As Worksheet
    Dim xtr As String, wb As Workbook, wb1 As Workbook
    
    xtr = ThisWorkbook.Path
    
    Set wb = ThisWorkbook
    
    Set wb1 = Workbooks.Open(xtr & "\" & "Book1.xlsx")
     Sheets("Forms").Copy After:=wb.Sheets(Sheets.Count)
     wb1.Close (False)
     
    Set TemplateWS = Worksheets("Template Tab")
    For r = 2 To Worksheets("Forms").Cells(Rows.Count, "A").End(xlUp).Row
        If Worksheets("Forms").Cells(r, "D") = True Then
            TemplateWS.Copy After:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = Worksheets("Forms").Cells(r, "A")
        End If
    Next
    Sheets("Forms").Delete
    
   
   Application.DisplayAlerts = True
   
End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Milind AgarwalAuthor Commented:
Hey Saurabh,
I am getting Run-Time error 9 : Subscript out of range @ Sheets("Forms").Copy After:=wb.Sheets(Sheets.Count)  that is when the Book1 is closed. Once it is open and I run it, i get an error like Index refers beyond end of list @ Sheets("Forms").Copy After:=wb.Sheets(Sheets.Count).

Am I missing something here?
Milind AgarwalAuthor Commented:
It worked just made little change to the existing code wb.Sheets(wb.Sheets.Count)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.