?
Solved

Help with Macro to Multiple Tabs Excel

Posted on 2016-11-02
2
Medium Priority
?
64 Views
Last Modified: 2016-11-03
Hello,

Can someone please help me create a macro that will iterate through a link in excel and create several tabs based on values in the link.  In the attached example, I have placed a data validation list in cell B4 as a drop down.  I want to iterate though the values in the link and create tabs of all the values contained in the link.

Thanks in advance for your help
Report-Out.xlsx
0
Comment
Question by:fb1990
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 41871230
Check attached.
Macro in Module1.

Option Explicit

Sub CreateReports()
    Dim wsReport As Worksheet, ws As Worksheet
    Dim ListName As Variant
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
    Set wsReport = Worksheets("Report")
    For Each ListName In [NamedList]
        wsReport.Range("C2") = ListName
        wsReport.Copy After:=Sheets(ThisWorkbook.Sheets.Count)
        i = 0
        For Each ws In ThisWorkbook.Worksheets
            If Left(ws.Name, Len(ListName)) = CStr(ListName) Then
                i = i + 1
            End If
        Next ws
        If i > 0 Then
            ActiveSheet.Name = ListName & " " & CStr(i + 1)
        Else
            ActiveSheet.Name = ListName
        End If
    Next ListName
End Sub

Open in new window

Report-Out.xlsm
0
 
LVL 1

Author Closing Comment

by:fb1990
ID: 41872035
Thank you very much.  It worked as expected...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question