consolidated sheet

manojxls

New Member
Joined
Dec 21, 2017
Messages
6
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

manojxls

New Member
Joined
Dec 21, 2017
Messages
6
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
Joined
Apr 4, 2009
Messages
3,680
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
Joined
Dec 21, 2017
Messages
6
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
 

Forum statistics

Threads
1,089,201
Messages
5,406,808
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top