Results 1 to 3 of 3

Help for enhance the code - Excluding Sheet While Consolidation

Threaded View

ExcelUser2707 Help for enhance the code -... 03-25-2014, 04:18 AM
AB33 Re: Help for enhance the code... 03-25-2014, 05:26 AM
ExcelUser2707 Re: Help for enhance the code... 03-25-2014, 05:43 AM
  1. #1
    Forum Contributor
    Join Date
    07-16-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    124

    Help for enhance the code - Excluding Sheet While Consolidation

    Hi Experts,

    I have code which is doing consolidation of all spreadsheet to master sheet.

    I want to exclude one sheet say “Project 2”, while consolidation “Project 2” data / or sheet require to excluding.

    I am attaching code for your reference.

    Illustration fill is attached for your ready reference.

    Thanks in advance

    Amar K


    Sub ConsolidateSheets()
    
     Dim ms As Worksheet, ws As Worksheet, LR As Long, i As Long, NR&
     
          Application.ScreenUpdating = 0
          Application.DisplayAlerts = 0
        
         On Error Resume Next
        
            If Not Evaluate("ISREF('Master'!A1)") Then
    
               Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master"
            Else
            
              Sheets("Master").Range("A2:M" & Rows.Count).ClearContents
          End If
        
           Set ms = Sheets("Master")
    
        For Each ws In ActiveWorkbook.Sheets
           With ws
             If .Name <> ms.Name Then
                 LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
                 NR = ms.Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
                  .Range("A2:L" & LR).Copy
                   ms.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues
                   ms.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(LR - 1) = ws.Name
             End If
           End With
        Next
                 Application.CutCopyMode = 0
                 ms.Columns.EntireColumn.AutoFit
                 Set ms = Nothing
                Application.ScreenUpdating = True
                Application.DisplayAlerts = True
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Enhance existing code to hide rows between specific text
    By MissaLissa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-24-2014, 03:20 PM
  2. [SOLVED] My formatting code takes forever - please enhance?
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-10-2013, 06:33 AM
  3. Need help to enhance disabling of various features on this code
    By alchavar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2013, 09:54 AM
  4. [SOLVED] Data Consolidation and running of code
    By sgmat in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 03-15-2013, 02:34 AM
  5. Need help to enhance and improve VBA code. Need to end the operations
    By BrandonFromSingapore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2012, 03:34 AM

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