+ Reply to Thread
Results 1 to 4 of 4

Macro to combine multiple worksheets into a Master file

  1. #1
    Registered User
    Join Date
    11-09-2012
    Location
    manila
    MS-Off Ver
    Excel 2010
    Posts
    3

    Macro to combine multiple worksheets into a Master file

    I'm new here and found these code

    Option Explicit

    Sub Consolidate()
    'Author: Jerry Beaucaire'
    'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
    'Summary: Merge files in a specific folder into one master sheet (stacked)
    ' Moves imported files into another folder

    Dim fName As String, fPath As String, fPathDone As String
    Dim LR As Long, NR As Long
    Dim wbData As Workbook, wsMaster As Worksheet

    'Setup
    Application.ScreenUpdating = False 'speed up macro execution
    Application.EnableEvents = False 'turn off other macros for now
    Application.DisplayAlerts = False 'turn off system messages for now

    Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into

    With wsMaster
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
    .UsedRange.Offset(1).EntireRow.Clear
    NR = 2
    Else
    NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'appends data to existing data
    End If

    'Path and filename (edit this section to suit)
    fPath = "C:\2011\Files\" 'remember final \ in this string
    fPathDone = fPath & "Imported\" 'remember final \ in this string
    On Error Resume Next
    MkDir fPathDone 'creates the completed folder if missing
    On Error GoTo 0
    fName = Dir(fPath & "*.xls*") 'listing of desired files, edit filter as desired

    'Import a sheet from found files
    Do While Len(fName) > 0
    If fName <> ThisWorkbook.Name Then 'don't reopen this file accidentally
    Set wbData = Workbooks.Open(fPath & fName) 'Open file

    'This is the section to customize, replace with your own action code as needed
    LR = Range("A" & Rows.Count).End(xlUp).Row 'Find last row
    Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
    wbData.Close False 'close file
    NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
    Name fPath & fName As fPathDone & fName 'move file to IMPORTED folder
    End If
    fName = Dir 'ready next filename
    Loop
    End With

    ErrorExit: 'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True 'turn system alerts back on
    Application.EnableEvents = True 'turn other macros back on
    Application.ScreenUpdating = True 'refreshes the screen
    End Sub

    when i try to run the code i always got these Runtime Error '9' Subscript out of range and highlight this portion of the code when i try to debug it Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into
    can anyone help me with this

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro to combine multiple worksheets into a Master file

    There is a sheet named Master in your workbook ?
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    11-09-2012
    Location
    manila
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to combine multiple worksheets into a Master file

    Yes. Also the name of the workbook is Master...

  4. #4
    Registered User
    Join Date
    11-09-2012
    Location
    manila
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to combine multiple worksheets into a Master file

    finally got it running maybe im just sleep deprive... sorry for the trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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