Avatar of Milind Agarwal
Milind Agarwal
Flag for United States of America asked on

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
End Sub
Microsoft ExcelVB ScriptSpreadsheetsVisual Basic Classic

Avatar of undefined
Last Comment
Milind Agarwal

8/22/2022 - Mon
Saurabh Singh Teotia


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??

Milind Agarwal

I think that would work.
Saurabh Singh Teotia

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Milind Agarwal

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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Milind Agarwal

It worked just made little change to the existing code wb.Sheets(wb.Sheets.Count)