+ Reply to Thread
Results 1 to 3 of 3

VBA Code doesnt create new sheets for items further down the list

  1. #1
    Registered User
    Join Date
    08-18-2008
    Location
    Australia
    Posts
    6

    Angry VBA Code doesnt create new sheets for items further down the list

    Hi All,

    I have a VBA code i have written. It is supposed to:

    1. Look at "HAZOP" Sheet
    2. For each row that has an ID in Column A, create a new sheet and paste each ID into cell Q1.

    What the below currently does is what I want, but doesnt include items where the ID is further down the sheet and has blanks on top of it. It also creates two blank sheets at the end which dont have any data in the, labelled "D_" and "HazopMasterAction(2)".

    What do i need to change to make it work for all items with an ID and also to stop creating the blank sheets at the end?

    Thanks so much for your help!!!



    Sub MultipleActionSheets()

    Dim LR As Long, ID As Range, Emp As Range
    Application.ScreenUpdating = False
    LR = Sheets("HAZOP").Cells(Rows.Count, 1).End(xlUp).Row

    Set Emp = Sheets("HAZOP").Range("A3:A5000" & LR)

    For Each ID In Emp
    Sheets("HazopMasterAction").Range("Q1") = ID
    Sheets("HazopMasterAction").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "D" & "_" & ID.Value
    Next ID

    Application.ScreenUpdating = True

    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: VBA Code doesnt create new sheets for items further down the list

    Maybe this:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: VBA Code doesnt create new sheets for items further down the list

    Or maybe so.
    Please Login or Register  to view this content.
    This assumes that all the cells in Column A that have something in it are the ID's. The rest of the Cells Are empty.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Create array of items which are in List A but not present in List B
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2018, 01:28 AM
  2. Create list of items concurrently from multiple sheets
    By deneh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2017, 08:39 PM
  3. To create a list of items in which certain items can be selected and printed
    By swashbuckler in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 03-05-2017, 01:26 PM
  4. Replies: 4
    Last Post: 11-05-2014, 09:38 AM
  5. Replies: 4
    Last Post: 06-30-2014, 11:13 AM
  6. [SOLVED] How to shorten code into an array to create a list of items separated by commas
    By mdovey in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2014, 12:23 PM
  7. Replies: 10
    Last Post: 03-19-2007, 11:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1