how make this code more dynamically (loop all files in the same directory )

[abdelfattah]

hi experts

I have this code import specific sheet to closed file . now I have many files in the same directory what I want import specific sheets ( sh1,imp, ex ,ret) from multiple files in the same directory . it should search for theses sheets in all files and import the data to closed file.
any suggestion to do that,please?

Sub CopySheetToClosedWB()
Dim SourceSht As Worksheet
Set SourceSht = Sheets("sheet2")
Application.ScreenUpdating = False
Set closedBook = Workbooks.Open("C:\Users\PC WORLD\Desktop\sub1.xlsm")
SourceSht.Copy After:=closedBook.Sheets("rs")
closedBook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

If you want to avoid duplicating sheets differentiated just by suffix, then just delete the sheets and then copy.

so there is no way to do that inside the macro instead of delete manually every time?

 

[abdelfattah]

You are looping many files in the same folder with similar sheet names over and over again. This means you are replacing and replacing sheets over and over again until finish looping all the files?

yes. the reason is the sheets in the files change the data and add a new continuously then should replace the data every time run the macro

 

[Zot]

yes. the reason is the sheets in the files change the data and add a new continuously then should replace the data every time run the macro

My understanding is that besides the ClosedWorkbook you have several other files with sheet names above. Therefore, after first loop, the ClosedWorkbook will have all the sh1,imp,ex,ret copied to it.

Then on second and subsequent loops, it will keep replacing sheets until finish looping through all the files. I don't see the point.

Maybe I misunderstood. You probably put a file in the folder and it will loop just once to replace those sheets. The next day put another file and run. THe bad thing is, you need to remove the previous source file. Otherwise it will loop new files and also previous files in the folder. This is troublesome too. Unless the code only look for the most recent file by date of creation.

It is not a problem to delete the sheet in ClosedWorkbook before copying but need to understand the whole flow of your routine.

 

[abdelfattah]

Then on second and subsequent loops, it will keep replacing sheets until finish looping through all the files.

yes this is should be

Maybe I misunderstood. You probably put a file in the folder and it will loop just once to replace those sheets. The next day put another file and run. THe bad thing is, you need to remove the previous source file. Otherwise it will loop new files and also previous files in the folder. This is troublesome too. Unless the code only look for the most recent file by date of creation.

I don't put a new files . this is the same files but the changing just in sheets , logically replece data instead of add a new sheets every time .
my data in all files are inventory then it will be changable every day then should show in closed file as in all files

 

[Zot]

yes this is should be

I don't put a new files . this is the same files but the changing just in sheets , logically replece data instead of add a new sheets every time .
my data in all files are inventory then it will be changable every day then should show in closed file as in all files

Sorry for the delay. I was ready to call the night off

I have added line to delete previous sheet and copy the latest sheet from source file

Sub CopySheetToClosedWB()

Dim FPath As String, ArryName() As String
Dim FName As Variant, wsName As Variant
Dim ws As Worksheet, SourceSht As Worksheet
Dim wb As Workbook, ClosedBook As Workbook

FPath = "C:\Users\PC WORLD\Desktop\"
FName = Dir(FPath)

Set ClosedBook = ActiveWorkbook
Set SourceSht = ClosedBook.Sheets("rs")

Application.ScreenUpdating = False

ArryName = Split("sh1,imp,ex,ret", ",")
While FName <> ""
    If Not FName = ClosedBook.Name Then
        Set wb = Workbooks.Open(Filename:=FPath & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
        For Each ws In wb.Sheets
            For Each wsName In ArryName
                If wsName = ws.Name Then
                    On Error Resume Next
                    Application.DisplayAlerts = False
                    ClosedBook.Sheets(ws.Name).Delete
                    Err.Clear
                    Application.DisplayAlerts = False
                    On Error GoTo 0
                    Set ws = wb.Sheets(ws.Name)
                    ws.Copy After:=ClosedBook.Sheets("rs")
                End If
            Next
        Next
        'Close wb without saving
        wb.Close False
    End If
    'Set the fileName to the next file
    FName = Dir
Wend
ClosedBook.Close True

End Sub

 

[abdelfattah]

thanks. I appreciate your efforts . I tested but unfortunately it repeats creating the sheets every time open the file

 

[Zot]

thanks. I appreciate your efforts . I tested but unfortunately it repeats creating the sheets every time open the file

It should not be. The code delete previous sheet if already existed and I have tested it with no problem. Probably I missed something.

What is your sub1.xlsm looks like currently. Even if it has all the sh1,imp,ex, and ret sheets; it will get deleted first. I have run with multiple files to replace the sheet at least 5 times and at the end only one sheet of each existed.

 

[abdelfattah]

sorry I was working at first code
that's very exellent !

just I face a simple problem . it's minor . if you fix it will be great
the sheets names are sensitive in letters if I have write by small letter in the code and writing by capital letter in the files .the code doesn't work
if there is way allow me without sensetive in letters.
thanks again

 

[Zot]

sorry I was working at first code
that's very exellent !

just I face a simple problem . it's minor . if you fix it will be great
the sheets names are sensitive in letters if I have write by small letter in the code and writing by capital letter in the files .the code doesn't work
if there is way allow me without sensetive in letters.
thanks again

You can put at the very top before sub

Option Compare Text

This will ignore letter case as default for whole module. See if this works

 

[abdelfattah]

magnificent ! thanks so much for your time and patience