Results 1 to 7 of 7

Check if cell is not empty and checks if worksheets already exists

Threaded View

rskay14 Check if cell is not empty... 03-19-2014, 12:15 AM
Kaper Re: Check if cell is not... 03-19-2014, 03:40 AM
rskay14 Re: Check if cell is not... 03-19-2014, 07:22 PM
rskay14 Re: Check if cell is not... 03-19-2014, 07:30 PM
rskay14 Re: Check if cell is not... 03-19-2014, 07:15 PM
Kaper Re: Check if cell is not... 03-20-2014, 02:23 AM
Fotis1991 Re: Check if cell is not... 03-20-2014, 02:40 AM
  1. #1
    Registered User
    Join Date
    03-18-2014
    Location
    port vila, vanuatu
    MS-Off Ver
    Excel 2010
    Posts
    12

    Check if cell is not empty and checks if worksheets already exists

    I have three worksheets in a workbook namely "Client", "Collections" & "Interest". I want to use data from these worksheets to create new worksheets and also populate the corresponding values. As a start i manage to create new worksheets but it is also creating worksheets with no tab name. Please help me fix this:

    Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    
    Set MyRange = Sheets("Interest").Range("A5:A100")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    
    For Each MyCell In MyRange
        If MyCell.Value <> "" Then    'checks if cell is not empty
            If Not SheetExists(MyCell.Value) Then 'check if worksheet already exists
                Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
                Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
                'Copy worksheet("Client") to new worksheets
                Sheets("Client").Cells.Copy
                'paste to new worksheet
                ActiveSheet.Paste
                'Fill cell A1 with client name
                For Each ws In ActiveWorkbook.Worksheets
                    ws.Range("B6") = ws.Name
                Next
            End If
        End If
    Next MyCell
    
    End Sub
    Function SheetExists(shName As String) As Boolean
    SheetExists = False
    For Each sh In Sheets
        If sh.Name = shName Then
            SheetExists = True
            Exit For
        End If
    Next sh
    End Function
    Last edited by arlu1201; 03-19-2014 at 12:50 AM. Reason: Use code tags in future.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] checks if row is blank, if not puts zero in first cell of row if cell is empty
    By synergio in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-13-2012, 10:19 AM
  2. macro that checks if the content of a given cell range exists in a Word file
    By Vogelmann in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-19-2012, 03:55 AM
  3. [SOLVED] check if worksheet already exists when auto creating worksheets form a list
    By y0rk1e72 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2012, 04:53 PM
  4. if check box is marked that it also checks another cell
    By AGoldy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2010, 03:49 PM
  5. [SOLVED] Micro that checks if file exists
    By pete in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-24-2005, 01:06 PM

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