Macro not running from shortcut

Jediantman · Aug 21, 2012

This is my first fully fledged macro that I'm creating and so I'm still very much a novice. Thanks to some help through this message board I've created my code for compiling 10 individual workbooks into one 'Master', by copying and pasting the data to specific locations in the master.

All works fine when I run the macro step by step (F8) and it populates everything I need but when I return to the workbook to run it from the shortcut it first of all told me that it "Can't execute code in break mode" but, since correcting one little error I noticed, it now opens the first spreadsheet and does nothing further.

The code I currently have is:

'Open Ann's Tracker, select S5 cells and copy

Workbooks.Open Filename:= _

"T:FilepathAut2012 Tracker Ann.xlsx" _

, ReadOnly:=True, Notify:=False

Sheets("Section 5").Select

Range("A23:CS250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Sheets("Section5Master").Select

Range("A23").Select

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

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=213

Range("A251").Select

'Go back to Ann's Tracker and select S5 - SM tab, select cells and copy

Windows("Aut2012 Tracker Ann.xlsx").Activate

Sheets("S5 Cat. - SM").Select

Range("A23:CQ250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Sheets("S5 Cat - SM Master").Select

Range("A23").Select

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

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=213

Range("A251").Select

'Go back to Ann's Tracker and select S5 - NTI tab, select cells and copy

Windows("Aut2012 Tracker Ann.xlsx").Activate

Sheets("S5 Cat - NTI").Select

Range("A23:CQ250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Sheets("S5 Cat - NTI Master").Select

Range("A23").Select

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

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=213

Range("A251").Select

'Go back to Ann's Tracker and select S8 tab, select cells and copy

Windows("Aut2012 Tracker Ann.xlsx").Activate

Sheets("S8 All Types").Select

Range("A23:BT250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Sheets("S8 All Types Master").Select

Range("A23").Select

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

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=213

Range("A251").Select

'Go back to Ann's Tracker and select S162 Standard tab, select cells and copy

Windows("Aut2012 Tracker Ann.xlsx").Activate

Sheets("S162a - Ind 1st,Std, LT").Select

Range("A23:BM250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Sheets("s162a Std Master").Select

Range("A23").Select

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

:=False, Transpose:=False
r />ActiveWindow.SmallScroll Down:=213

Range("A251").Select

'Go back to Ann's Tracker and select S162 Progress Monitoring tab, select cells and copy

Windows("Aut2012 Tracker Ann.xlsx").Activate

Sheets("S162a Prog Mon").Select

Range("A23:BN250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Sheets("s162a Prog Mon Master").Select

Range("A23").Select

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

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=213

Range("A251").Select

'Go back to Ann's Tracker and select S162 Other tab, select cells and copy

Windows("Aut2012 Tracker Ann.xlsx").Activate

Sheets("S162a Other").Select

Range("A23:BD250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Sheets("s162a Other Master").Select

Range("A23").Select

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

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=213

Range("A251").Select

'Go back to Ann's Tracker and select Academy Registration tab, select cells and copy

Windows("Aut2012 Tracker Ann.xlsx").Activate

Sheets("Academy Registration").Select

Range("A23:CS250").Select

Selection.Copy

Windows("Datamastermacro.xlsm").Activate

Sheets("Academy Reg Master").Select

Range("A23").Select

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

:=False, Transpose:=False

ActiveWindow.SmallScroll Down:=213

Range("A251").Select

'Close Ann's Tracker

Windows("Aut2012 Tracker Ann.xlsx").Activate

Application.DisplayAlerts = False

ActiveWindow.Close

' Open Ant's Tracker, select cells and copy

Workbooks.Open Filename:= _

"T:FilepathAut2012 Tracker Ant.xlsx" _

, ReadOnly:=True, Notify:=False

Everything from this point just repeats for each of the remaining workbooks but the above section for "Ant's Tracker" is currently highlighted in red (presumably to tell me something is wrong) but I can't see what is causing that problem. When the shortcut runs it stops as soon as it has opened the first workbook.

Any and all help is very much appreciated and I look forward to your responses

Thanks Ant.

Hui · Aug 21, 2012

Ant

Without seeing the full Subroutine and also where it is located it is a bit hard to advise

I would set a break point on line Workbooks.Open Filename:= _

put the cursor on the line and press F9

Go back to the worksheet and run the macro

It will stop at the line with the breakpoint

Now press F8 and it will step through the code 1 line at a time

If you set up the screens so you can see the Excel and VBA windows next to each other you can watch what is going on

Step through the macro by pressing F8 and observe what happens until it stops doing what it should do

Now you know where the problem is and can tackle it appropriately

Jediantman · Aug 21, 2012

Thanks, I'll give it a go and let you know.

mercatog · Aug 23, 2012

Hi Jediantman

What a big code. To optimze it, use object variables without activing or selecting any thing. Also you can transfer values between workbooks without using Copy/PasteSpecial who needs more ressources.

Here your code modified

[pre]

Code:

Sub Test()
Dim aWbk As Workbook, Wbk As Workbook
Dim MyFile As String

Application.ScreenUpdating = False
MyFile = &#34;T:FilepathAut2012 Tracker Ann.xlsx&#34;
If Dir(MyFile) &#60;&#62; &#34;&#34; Then                        &#39;test if the file exists
Set Wbk = Workbooks.Open(MyFile, ReadOnly:=True, Notify:=False)
Set aWbk = ThisWorkbook
With Wbk
aWbk.Worksheets(&#34;Section5Master&#34;).Range(&#34;A23:CS250&#34;).Value = .Worksheets(&#34;Section 5&#34;).Range(&#34;A23:CS250&#34;).Value
aWbk.Worksheets(&#34;S5 Cat - SM Master&#34;).Range(&#34;A23:CQ250&#34;).Value = .Worksheets(&#34;S5 Cat. - SM&#34;).Range(&#34;A23:CQ250&#34;).Value
aWbk.Worksheets(&#34;S5 Cat - NTI Master&#34;).Range(&#34;A23:CQ250&#34;).Value = .Worksheets(&#34;S5 Cat - NTI&#34;).Range(&#34;A23:CQ250&#34;).Value
aWbk.Worksheets(&#34;S8 All Types Master&#34;).Range(&#34;A23:BT250&#34;).Value = .Worksheets(&#34;S8 All Types&#34;).Range(&#34;A23:BT250&#34;).Value
aWbk.Worksheets(&#34;s162a Std Master&#34;).Range(&#34;A23:BM250&#34;).Value = .Worksheets(&#34;S162a - Ind 1st,Std, LT&#34;).Range(&#34;A23:BM250&#34;).Value
aWbk.Worksheets(&#34;s162a Prog Mon Master&#34;).Range(&#34;A23:BN250&#34;).Value = .Worksheets(&#34;S162a Prog Mon&#34;).Range(&#34;A23:BN250&#34;).Value
aWbk.Worksheets(&#34;s162a Other Master&#34;).Range(&#34;A23:BD250&#34;).Value = .Worksheets(&#34;S162a Other&#34;).Range(&#34;A23:BD250&#34;).Value
aWbk.Worksheets(&#34;Academy Reg Master&#34;).Range(&#34;A23:CS250&#34;).Value = .Worksheets(&#34;Academy Registration&#34;).Range(&#34;A23:CS250&#34;).Value
End With
Set aWbk = Nothing
Wbk.Close False
Set Wbk = Nothing
End If
End Sub

[/pre]

Note that to transfer datas between 2 ranges, thoses ranges should have the same size

Regards,

mercatog · Aug 23, 2012

Another way is using sub routine like this

[pre]

Code:

Sub Test()
Dim Wbk As Workbook
Dim MyFile As String

Application.ScreenUpdating = False
MyFile = &#34;T:FilepathAut2012 Tracker Ann.xlsx&#34;
If Dir(MyFile) &#60;&#62; &#34;&#34; Then                        &#39;Test if the file exists
Set Wbk = Workbooks.Open(MyFile, ReadOnly:=True, Notify:=False)
TransferData Wbk, &#34;Section 5&#34;, &#34;Section5Master&#34;, &#34;A23:CS250&#34;
TransferData Wbk, &#34;S5 Cat. - SM&#34;, &#34;S5 Cat - SM Master&#34;, &#34;A23:CQ250&#34;
TransferData Wbk, &#34;S5 Cat - NTI&#34;, &#34;S5 Cat - NTI Master&#34;, &#34;A23:BT250&#34;
TransferData Wbk, &#34;S8 All Types&#34;, &#34;S8 All Types Master&#34;, &#34;A23:CS250&#34;
TransferData Wbk, &#34;S162a - Ind 1st,Std, LT&#34;, &#34;s162a Std Master&#34;, &#34;A23:BM250&#34;
TransferData Wbk, &#34;S162a Prog Mon&#34;, &#34;s162a Prog Mon Master&#34;, &#34;A23:BN250&#34;
TransferData Wbk, &#34;S162a Other&#34;, &#34;s162a Other Master&#34;, &#34;A23:BD250&#34;
TransferData Wbk, &#34;Academy Registration&#34;, &#34;Academy Reg Master&#34;, &#34;A23:CS250&#34;
Wbk.Close False
Set Wbk = Nothing
End If
End Sub

Private Sub TransferData(ByVal Wbk As Workbook, ByVal ShSce As String, ByVal ShDes As String, ByVal Rng As String)

ThisWorkbook.Worksheets(ShSce).Range(Rng).Value = Wbk.Worksheets(ShDes).Range(Rng).Value
End Sub

[/pre]

SirJB7 · Aug 23, 2012

@mercatog

Hi!

Didn't checked if your code does the job, but if so... congrats!

Regards!

mercatog · Aug 23, 2012

Thank you very much Sir SirJB7

Me too, without file, I just wrote what I guess doing the job.

Waiting for feedback from Jediantman to see what is the result.

Regards!

Macro not running from shortcut

Jediantman

New Member

Hui

Excel Ninja

Jediantman

New Member

mercatog

New Member

mercatog

New Member

SirJB7

Excel Rōnin

mercatog

New Member