consolidated sheet

manojxls

New Member
Hi, I want to consolidated sheet from 10 different workbook to single sheet in same folder.

VBA code should like, first check that file exists or not if exit than run code if file not exist then exit sub or loop.
 

manojxls

New Member
HI James, thanks for reply but due to privacy issue i cannot download the above file. but need vba code i tried below but it became loop or i making something wrong as all excel freeze. below is code.

Sub book1()
myfile = "C:\Users\Manoj\Documents\New folder (2)\test1\1.xlsx"
Workbooks.Open Filename:="C:\Users\Manoj\Documents\New folder (2)\1.xlsx"

If Dir(myfile) <> "" Then
MsgBox "opened 1"
MsgBox "opened ok1"
Call book2 'MsgBox "exit"
Else
Call book2
End If
Workbooks("1.xlsx").Close

End Sub


Sub book2()
myfile = "C:\Users\Manoj\Documents\New folder (2)\test1\2.xlsx"
Workbooks.Open Filename:="C:\Users\Manoj\Documents\New folder (2)\2.xlsx"

If Dir(myfile) <> "" Then
MsgBox "opened 2"
MsgBox "opened ok2"
Call book3 'MsgBox "exit"
Else
Call book3
End If
Workbooks("2.xlsx").Close


End Sub


Sub book3()
myfile = "C:\Users\Manoj\Documents\New folder (2)\test1\3.xlsx"
Workbooks.Open Filename:="C:\Users\Manoj\Documents\New folder (2)\3.xlsx"

If Dir(myfile) <> "" Then
MsgBox "opened 3"
MsgBox "opened ok3"
Call book4 'MsgBox "exit"
Else
Call book4
End If
Workbooks("3.xlsx").Close
End Sub


Sub book4()
myfile = "C:\Users\Manoj\Documents\New folder (2)\test1\4.xlsx"
Workbooks.Open Filename:="C:\Users\Manoj\Documents\New folder (2)\4.xlsx"

If Dir(myfile) <> "" Then
MsgBox "opened 4"
MsgBox "opened ok4"
'Call book4 'MsgBox "exit"
Else
'Call book4
End If
Workbooks("4.xlsx").Close
End Sub
 

James006

Well-known Member
Hello,

If you are dealing with 10 workbooks to consolidate ... you must have an 11th file : the Master File ... the one which will hold the macro which will handle everything ...

If this your structure ?
 

manojxls

New Member
VBA code should like, first check that file exists or not if exit than run code if file not exist then exit sub or loop master file will 11th file
 

Some videos you may like

This Week's Hot Topics

Top