How can run this macro in all excell files in specific folder

bigmak

New Member
Joined
Sep 8, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Hello everybody

how can run this vba code in all excell file in folder ??
this code copy some info from Book4 to Book1 .
i have 5 excell file in a folder and i want this code run in all 5 excell file same time.

VBA Code:
Sub Macro3()
    Windows("Book4.xlsm").Activate
    Range("G5:G7").Select
    Selection.Copy
    Windows("Book1.xlsm").Activate
    Range("C13").Select
    ActiveSheet.Paste
End Sub
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,160
What is the full path to the folder containing the files? What is the file extension (xlsx, xlsm)? Are they the only files in that folder? Do you want to paste the ranges underneath each other starting at C13? Please clarify.
 

bigmak

New Member
Joined
Sep 8, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
What is the full path to the folder containing the files? no matter
extension = xlsm
Are they the only files in that folder? = yes
i want code select G5:G7 cells from Book4.xlsm and copy them to C13:C15 of all sheets of folder files.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub bigmak()
   Dim Pth As String
   Dim Fname As Object
   Dim Rng As Range
   Dim ws As Worksheet
   
   Pth = "C:\mrexcel\"
   Set Rng = Range("G5:G7")
   With CreateObject("scripting.filesystemobject")
      For Each Fname In .getfolder(Pth).Files
         With Workbooks.Open(Fname)
            For Each ws In .WorkSheets
               Rng.Copy ws.Range("C13")
            Next ws
            .Close True
         End With
      Next Fname
   End With
End Sub
 

bigmak

New Member
Joined
Sep 8, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

یخ
How about
VBA Code:
Sub bigmak()
   Dim Pth As String
   Dim Fname As Object
   Dim Rng As Range
   Dim ws As Worksheet
 
   Pth = "C:\mrexcel\"
   Set Rng = Range("G5:G7")
   With CreateObject("scripting.filesystemobject")
      For Each Fname In .getfolder(Pth).Files
         With Workbooks.Open(Fname)
            For Each ws In .WorkSheets
               Rng.Copy ws.Range("C13")
            Next ws
            .Close True
         End With
      Next Fname
   End With
End Sub
[/QUOTE
dosnt work bro
i find this code and work great for my some work but dont work on my code that i post in first thread.
i want to get some cells info from Book4.xlsm file and copy that to all sheets of all excell files in a folder.
VBA Code:
Sub LoopThroughFiles()      
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here                                                       
            End With
            xFileName = Dir
        Loop
    End If
End Sub
 

bigmak

New Member
Joined
Sep 8, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

i find this code and work great for my some work but dont work on my code that i post in first thread.
i want to get some cells info from Book4.xlsm file and copy that to all sheets of all excell files in a folder.
VBA Code:
Sub LoopThroughFiles()     
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here                                                       
            End With
            xFileName = Dir
        Loop
    End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Did you try the code I supplied?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
In what way?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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