Trying to consolidate data from 10 worksheets into one

[Jediantman]

Hi there,


I'm a bit of a macro novice and so I'm trying to understand why mine refuses to work.


All I need to do is copy a specific range of cells, which is common to 10 worksheets, into a master sheet. One of the conditions is that people may well be using them, so they have to be opened as read only, but that bit works fine. Once the file is open it then stops and refuses to copy the range of cells and paste them to the destination or anything else.


The code I'm currently using is below:


Workbooks.Open Filename:= _

"T:FilepathTracker Ann.xlsx" _

, ReadOnly:=True, Notify:=False

Sheets("Section 5").Select

Range("A23:CS250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=213

Range("A251").Select

Windows("Tracker Ann.xlsx").Activate

ActiveWindow.Close


I've tried using the example on this website, using the table to establish where to copy to and from but that doesn't seem to work either.


Any help would be appreciated....


Thanks Ant

 

[Hui]

Ant


Firstly, Welcome to the Chandoo.org forums.


The worksheet Datamastermacro.xlsm must already be open in Excel when you run this macro


Where abouts does the code stop?


If you step through this macro using F8 to run it take note of which line it is on when it stops

 

[Jediantman]

The code stops after going through the other 9 sheets, but the F8 step by step was genius, thank you, as it does it properly.


It just doesn't seem to work from my shortcut...

 

[SirJB7]

Hi, Jediantman!

The code you posted seems to be a part of the whole code involved, since it doesn't handle anyway 10 worksheets. More indeed you mention two workbooks (Tracker Ann.xlsx and Datamastermacro.xlsm) and I don't know if a third as the code isn't clear to which file it belongs, so...

Could you please upload a sample of each involved file, indicating source ranges, target range, code/macros related? Please refer to second green sticky topic at this forums main page for guidelines.

Regards!