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

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,158
Office Version
  1. 2010
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?



VBA Code:
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
 
  • Like
Reactions: JEC

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,158
Office Version
  1. 2010
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?
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,158
Office Version
  1. 2010
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? :unsure:o_O
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,867
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 3, 2019
Messages
1,158
Office Version
  1. 2010
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,867
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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

Well-known Member
Joined
May 3, 2019
Messages
1,158
Office Version
  1. 2010
thanks. I appreciate your efforts . I tested but unfortunately it repeats creating the sheets every time open the file
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,867
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 3, 2019
Messages
1,158
Office Version
  1. 2010
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,867
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,823
Messages
5,743,406
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top