Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

convert code to import data from one sheet to new sheets

0

Hi experts 

I  look for  way  to  change  this  code 

Sub ConsolidateWbks()

    Dim Pth As String
    Dim MstSht As Worksheet
    Dim fname As String

Application.ScreenUpdating = False

    Pth = "C:\TextFolder\2023\folders\"
    Set MstSht = ThisWorkbook.Sheets("sh")

    fname = Dir(Pth & "*xls*")
    Do While Len(fname) > 0
        Workbooks.Open (Pth & fname)
        With Workbooks(fname)
            .Sheets("sh").Range("A1").CurrentRegion.Copy MstSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Close , False
        End With
        fname = Dir
    Loop

End Sub

the  code will match  SH sheet  name  for  two closed  files  with open  file  where import  data from closed  files  in  the  same  sheet , but  I  look  for way  to  create new sheets names  for  the    MATCH file  open  where run  the  macro   based on sheets names are  in closed  files  and  should  create new sheets like  this sh1,sh2  and  import  data for  each sheet individually  from each  closed file individually and  no  need repeating  create sheets every time  run  the  macro   just  create  sheets  based  on  different files  names in  the  folder and  should  replace  data for  each  sheet when run  the  macro  every time .

I  put the result  in MATCH file after running sheet   how   create new sheets and  import  data from closed files .

thanks

Answer
Discuss

Discussion

Hi Halk. 

Do you mean that if file MATCH imports data from file ss to a sheet today, tomorrow that sheet will be replaced with new data from ss? If so, why not name the sheets ss, dd etc? Or include the import date too?
John_Ru (rep: 6297) Mar 2, '23 at 5:53 am
Hi John,
 Do you mean that if file MATCH imports data from file ss to a sheet today, tomorrow that sheet will be replaced with new data from ss?
well, good  idea !  but  as  you  see theses files  exports by  another  macro where  save  the data in sh sheet name, and  why  I  restrict data  based on date today, may be  passes days  and   if  I  come  back  to  import data  then  I can't  import  becuase the  date doesn't  become date(today)?
adjusting data for  two closed files are not necessarily  occures in  same day and  replace  data colud  be next days after adjusting in past  days .
Halk (rep: 32) Mar 2, '23 at 6:19 am
Add to Discussion

Answers

0
Selected Answer

 Hi Halk.

My suggestion is that you have sheets with names like "dd@01-Mar-23" where the first part is the copied file name (e.g. from dd.xlsx) and after @ is the date of import. That way, you will know when the sheets were revised. If a file is missing on an attempted import, its sheet won't be deleted but left with an old date. New files will get a new dated sheet.

In the attached file, I've renamed your sheets like that and deleted the data. The macro below assumes that files will have a sheet called "sh"  (the variable ImpName) so the (crude) error  handling is triggered. You may need to comment out the first line starting Pth (in bold) and use your fixed path if files aren't in the same location as the .xlsm file:

Sub ConsolidateWbks()

    Dim Pth As String
    Dim fname As String
    Dim ImpName As String, wSh As Worksheet, Dn As String, NewName As String

    Application.ScreenUpdating = False
    On Error GoTo Prob
    ' state what sheet should be imported from data files
    ImpName = "sh"

'    Pth = "C:\TextFolder\2023\folders\"
    Pth = ThisWorkbook.Path & "\"

    fname = Dir(Pth & "*xls*")
    Do While Len(fname) > 0
        If fname <> ThisWorkbook.Name Then
        Debug.Print fname
        Workbooks.Open (Pth & fname)
            With Workbooks(fname)
                If .Worksheets(ImpName).Name = ImpName Then
                    Dn = ""
                    NewName = Left(fname, InStrRev(fname, ".") - 1)
                    'check if similar sheet exists
                    For Each sh In ThisWorkbook.Worksheets
                        If sh.Name <> "running" Then
                            If Left(sh.Name, InStrRev(sh.Name, "@") - 1) = NewName Then
                                ' if old found, copy new and delete old
                                .Worksheets(ImpName).Copy Before:=sh
                                Application.DisplayAlerts = False
                                sh.Delete
                                Application.DisplayAlerts = True
                                'set flag
                                Dn = "Replaced"
                                Exit For
                            End If
                        End If
                    Next sh
                    ' if not found, copy to end
                    If Dn <> "Replaced" Then .Worksheets(ImpName).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
                End If
                .Close , False
                'rename copied sheet with today's date
                ThisWorkbook.Worksheets(ImpName).Name = NewName & "@" & Format(Date, "dd-mmm-yy")
            End With
        End If
        fname = Dir
    Loop

Exit Sub

Prob:
'close opened book and tell user
Debug.Print Err.Number
ActiveWorkbook.Close False
MsgBox "Stopped since file " & fname & " does not contain a sheet called " & ImpName & " to import"

End Sub

Run the macro and it will replaced the empty sheets with data from your files. Add a new file (different name) to the import folder and it will create a new sheet.

Hope this works for you as a solution.

Discuss

Discussion


I  see  now John after  your  comment . , I  misundersood  you in the beginning  
your  suggetion  is really great !
thanks  very  much
Halk (rep: 32) Mar 2, '23 at 2:26 pm
Glad that worked for you. Thanks for selecting my Answer, Halk. 
John_Ru (rep: 6297) Mar 2, '23 at 4:19 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login