Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Miracle VBA Copy

Hello,

There is a workbook called SRC. It has a sheet called “BufferThis”.
Now, there is a folder with 100 xlsm files.
I need to go through each xlsm file in that folder and add the worksheet “BufferThis” of SRC workbook to each of the xlsm files.
Whats the VBA to do this?

tHanks
0
Rayne
Asked:
Rayne
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try this macro
You will have to change the file path on line 9
Run the macro with the “BufferThis” sheet active
Sub wstoall()
Dim fnam As String
Dim fpath As String
Dim twb As Workbook
Dim swb As Workbook
Dim sws As Worksheet
Set swb = ActiveWorkbook
Set sws = ActiveSheet
fpath = "C:\Documents and Settings\Administrator\My Documents\"
fnam = Dir(fpath & "*.xls*")
Do While fnam <> ""
    Set twb = Workbooks.Open(fpath & fnam)
    sws.Copy twb.Sheets(1)
    twb.Close True
    fnam = Dir
Loop
End Sub

Open in new window

0
 
RayneAuthor Commented:
Hello Ssaqibh,
That worked like a charm. Thank you as always ;)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now