CODEWITHSUNDEEP
vbaexcel
Anju Maharjan
Anju Maharjan

Reputation: 51

Running a Macro over multiple excel files and save them in a separate directory

I am trying to run a macro over all excel sheets in a directory and save as in the new directory. My code is as:

Sub TestMacro()

Dim pathname As String
Dim newpath As String
Dim newfullpath As String
Dim filenam As String
Dim fullpath As String

pathname = "\\filepath\"
newpath = "\\filepath\newfilepath\"
filenam = Dir(pathname & "*.xls") 


fullpath = pathname & filenam
Do While filenam <> ""
    MsgBox (filenam)
    newfullpath = newpath & filenam

    Workbooks.Open Filename:=fullpath, ReadOnly:=True
        Sheets("Sheet 1").Select
        Sheets("Sheet 1").Name = "Sheet1"

    ActiveWorkbook.SaveAs Filename:=newfullpath

    'MsgBox (filenam)
    ActiveWindow.Close
    'Workbooks.Close

    filenam = Dir()
Loop
End Sub

The problem here is that all the excel sheets in the new directory contains the content of the first excel sheet of \filepath Where am I going wrong with the code? Thanks in advance!!

Upvotes: 1

Views: 121

Answers (1)

YowE3K
YowE3K

Reputation: 23994

Your code is constantly opening the same workbook:

Workbooks.Open Filename:=fullpath, ReadOnly:=True

You should be opening the workbook defined by pathname & filenam, i.e.

Workbooks.Open Filename:=pathname & filenam, ReadOnly:=True

Upvotes: 1

Related Questions