Macro to copy all files in a folder into active sheet

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
Hope you can help me,
I have a situation where I need to copy all my stores into one document, so here what i was thinking

I save the active folding local, but we dont know it actual location,
however we do know that in that same folder are all the documents I need.

so I want a macro that when run goes to the folder the document is saved in and for ever document that is in there that is XLSX or XLSM
open the document, goto the tab Named "Sales" (they will all have a tab named sales but if it cant find one close and move on to next)
Colpy All Data in Columns A&B

now with the document, create a new sheet, paste that data into A1 and name the sheet the name of the document you opened for example if it was called "Tony Test.XLSX" the tab is Tony Test
do this for all the documents in the folder.

please help if you can

thanks

Tony
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi.​
so I want a macro that when run goes to the folder the document is saved in and for ever document that is in there that is XLSX or XLSM
Hoping this document workbook is not saved as .xlsm but as .xlsb ?​
Colpy All Data in Columns A&B
Does any source file has more data in other column(s) than columns A & B ?​
 
Upvote 0
Ok no, i need it to somehow say "if document does not = this documents name open?"

and
Does any source file has more data in other column(s) than columns A & B ?
Yes, lots but i dont need them, you can use column A for last row if that helps.

Thanks for your help hope you come up with something
Tony
 
Upvote 0
P.s. not being fussy, but Document is a perfectly accurate way to describe an excel workbook! trust me I've been through this a lot,
for example, simple google search and:
"Is an Excel file considered a document?"
Each Excel workbook is a separate document, within which you create one or several worksheets. (y):ROFLMAO:?
 
Upvote 0
A VBA demonstration to open some workbooks for starters :​
VBA Code:
Function ExistWorkbookSheet(BOOK, SHEET) As Boolean
                      Dim V
                          V = Evaluate("ISREF('[" & BOOK & "]" & SHEET & "'!A1)")
         ExistWorkbookSheet = IIf(IsError(V), False, V)
End Function

Sub Demo1()
       Const S = "Sales"
         Dim V, F$
    With Application
       .DisplayAlerts = False
       .ScreenUpdating = False
    For Each V In [{"m","x"}]
              F = Dir$(ThisWorkbook.Path & "\*.xls" & V)
        While F > ""
           If F <> ThisWorkbook.Name Then
              With Workbooks.Open(ThisWorkbook.Path & "\" & F, 0)
                If ExistWorkbookSheet(F, S) Then
                    F = Left$(F, Len(F) - 5)
                    If ExistWorkbookSheet(ThisWorkbook.Name, F) Then ThisWorkbook.Sheets(F).Delete
                   .Sheets(S).Copy , ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
                With ActiveSheet.UsedRange.Columns
                    If .Count > 2 Then .Item(3).Resize(, .Count - 2).Clear
                End With
                    ActiveSheet.Name = F
                End If
                   .Close False
              End With
           End If
              F = Dir$
        Wend
    Next
       .DisplayAlerts = True
       .ScreenUpdating = True
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top