Help in sheets and workbooks

[deepanc]

Hi ... Is this Possible...
I have 3 workbks (one,two,three)... and in that sheet 3 of each has consolidate data... Now I want in 4 th wrkbk all the three sheets from sheet 3 in each bok... any idea...

eg: one.xlsx = sheet 1,sheet2,sheet3
two.xlsx= sheet1,sheet2,sheet3
three.xlsx=sheet1,sheet2,sheet3

fur.xlsx= one!sheet3,two!sheet3,three!sheet3

Thank U

[arlu1201]

Are one.xlsx to three.xlsx saved in one particular folder?

Will it always be 3 workbooks or can the number increase in future?

[deepanc]

yes. All are in same folder. Again yes, in future it may increase. And when I change data in that sheet, then the duplicate sheet also to be change.

[arlu1201]

So in future, if your files increase, only sheet3 from each file should be consolidated right?

Is your file named "Sheet3" or something else in each file?

[deepanc]

yes. The sheet name 'te1','te2'....and so

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

The output will be in a new workbook. You can place this code in any file except the files to be consolidated.

[deepanc]

not work. Error. Subscribt out of range

[arlu1201]

Is the error coming on this line ?

Please Login or Register  to view this content.

You told me the sheets are named as "te1", "te2", etc. So i put te3 there since you want sheet 3. So change the te3 in the above line to whatever sheet you need consolidated.

[deepanc]

its work. Thank u so much. But I want when the data changed in original sheet, then it automatically changed in duplicate too...

[arlu1201]

In the case of changes, you need to run the macro again.

[deepanc]

when I run macro it copied but each sheet copied in difft wrkbuk. But I want all the sheet3 in a single book.

[arlu1201]

You mean, the first time it ran fine? But when you made changes and ran it again, it ran in a different workbook?

[deepanc]

yes. Exactly

[arlu1201]

Correct. Each time you run the macro, it will create a new workbook for you with the updated data. So you have to just discard the old data.

Or we can ask the macro to pick up the file and clear all the sheets and then load. Which option do you want?

[deepanc]

I want second option

[arlu1201]

Try this code

Please Login or Register  to view this content.

[deepanc]

ok. I will try. Now I am with mobile. Thank 4 ur support. Later I will write the result. Once I thank you

[deepanc]

HI Can u explain little
what are these which is to be copy file path,


'The folder containing the files to be recap'd
myPath = "B:\Test"

'Finds the name of the first file of type .xls in the current directory
CurrentFileName = Dir(myPath & "\*.xlsx")

'Create a workbook for the recap report
Set Master = Workbooks.Open("B:\Report\Master.xlsx")


Set sourceData = sourceBook.Worksheets("te3")= every time do I change sheet name if so...

[arlu1201]

'The folder containing the files to be recap'd
myPath = "B:\Test"
This is the path which contains your files which need to be consolidated

'Finds the name of the first file of type .xls in the current directory
CurrentFileName = Dir(myPath & "\*.xlsx")
You do not need to change this. Except the file extension if you are using excel 2003 files or others. This statement saves the filename including the path in the Currentfilename variable.

'Create a workbook for the recap report
Set Master = Workbooks.Open("B:\Report\Master.xlsx")
This opens the master workbook and names it as "Master" so its easy to refer to it anytime in the code.

Set sourceData = sourceBook.Worksheets("te3")= every time do I change sheet name if so...
If its just one sheet name, you can change it above. However, if you need to consolidate multiple sheet names each time, we can change the code slightly for that. For e.g. if you want all sheet1 to be consolidated in master file sheet1, all sheet2 to be consolidated in sheet2, etc.

[deepanc]

hey, thanks man. You did a nice job. Thanks again

[arlu1201]

Thank you.