consolidated sheet

[manojxls]

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.

 

[James006]

Hi,

With many different workbooks ... you should take a look at Power Query

https://www.myexcelonline.com/blog/consolidate-multiple-excel-workbooks-using-power-query/

Hope this will help

 

[manojxls]

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]

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]

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

 

[James006]

Hi again,

In order to loop through all your workbooks ... have a look at following :

https://www.thespreadsheetguru.com/...oop-through-all-excel-files-in-a-given-folder

Hope this will help