+ Reply to Thread
Results 1 to 2 of 2

add message box and rename the same sheet for all of closed files in the folder

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    395

    add message box and rename the same sheet for all of closed files in the folder

    Hi experts ,
    here is code will pull all of data from all of files are existed in the same folder for sheet name is MATCH , so if there files don't contain MATCH sheet then will shows error subscript out of range in this line
    With .Sheets("MATCH")
    . so what I want show message box" the sheet is not existed, do you want rename all of sheets in all closed file to MATCH sheet" and contains two choices if press ok , then will rename to MATCH sheet and pull data , if I press no then will just pull data for just files contains MATCH sheet.
    last thing when rename sheet to MATCH sheet for closed files should search for the first sheet to rename to MATCH sheet and ignore the others files contain MATCH sheet .
    Sub CopyRangeFromSetFolder()
    
    Dim desWS As Worksheet, wb As Workbook, lRow As Long
    Dim wbNm As String, Fld As String
    
    Application.ScreenUpdating = False
    
    Set desWS = ThisWorkbook.Sheets("Sheet1")
    desWS.Range("A2").CurrentRegion.Offset(1, 0).ClearContents
    ' define path to set folder, ending in \
    Fld = ThisWorkbook.Path & "\"
    'get first file with wildcard match
        wbNm = Dir(Fld & "*.xls*", vbNormal)
        ' loop while there's another matching file
        Do While wbNm <> ""
           ' Check it's not this workbook
            If wbNm <> ThisWorkbook.Name Then
                With GetObject(Fld & wbNm)
                    With .Sheets("MATCH")
                        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                        .Range("A2:D" & lRow).Copy
                        desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
                    End With
                    ' close the file without saving
                    Application.DisplayAlerts = False
                    .Close False
                    Application.DisplayAlerts = True
                End With
                Else
                MsgBox "File Elmarghanie Brand .xlsm not found"
         Exit Sub
            End If
    
            ' get next matching file
            wbNm = Dir()
        Loop
    
    Application.ScreenUpdating = True
    End Sub
    I hope somebody has idea to do that
    thanks
    Last edited by MKLAQ; 03-20-2023 at 05:37 AM.

  2. #2
    Forum Contributor
    Join Date
    02-08-2021
    Location
    africa
    MS-Off Ver
    2016
    Posts
    395

    Re: add message box and rename the same sheet for all of closed files in the folder

    Hi guys,
    any idea?

+ 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] Loop through all files in a folder and rename the files considering a pattern
    By excelactuary in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-22-2022, 10:57 AM
  2. [SOLVED] Copy Folder then rename files in new folder from list in Excel
    By jimjones1958 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2021, 05:00 PM
  3. Replies: 18
    Last Post: 11-27-2018, 05:25 AM
  4. Rename Sheet name for all the excel files within the folder
    By pitamine in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-06-2017, 02:27 PM
  5. [SOLVED] vba to rename sheet as GSI In all closed workbooks in target folder
    By julielara in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2016, 07:06 AM
  6. Rename Sheet name for all the excel files within the folder
    By banglong in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-12-2014, 11:45 PM
  7. Rename the first Sheet Name of 130 closed excel files
    By ZarrinPour in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2009, 03:43 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