Macro Not running

S

SantanaKRE8s

Board Regular
Joined
Jul 11, 2023
Messages
114
Office Version
  1. 365
Platform
  1. Windows
Macro stops when it has to get data from the "data" Workbook. I have a workbook that copies data from another workbook. The Macro was working but when I added the option to run the Macro automatically when I open the workbook it does not work.


Private Sub Workbook_Open()
'

Sheets("FAB & JAB OOR").Select

'
Windows("data.xlsx").Activate
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("Y2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("Z2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("AA2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("I3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range("AR2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Dashboard").Select


End Sub
 

Attachments

  • DEBUG.png
    DEBUG.png
    42.9 KB · Views: 10

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
How are you opening this file? Form inside Excel or from Windows Explorer?
Is the other file (your "data.xls" file) already open in the exact same session of Excel?
 
Upvote 0
Im using Kutools to open both both, first I Open the data file and then I open the workbook with the Macro
 
Upvote 0
Tried reqritting it this way, but still not working.



Private Sub Workbook_Open()

Sub GET_DATA()
'
' GET_DATA Macro
'

'
Sheets("FAB & JAB OOR").Select
Windows("data.xlsx").Activate
ActiveWindow.SmallScroll Down:=-9
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-12
ActiveWindow.ScrollRow = 1
Range("F2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("Y2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Range(Selection, Selection.End(xlUp)).Select
Range("Z2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
Range(Selection, Selection.End(xlUp)).Select
Range("AA2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("I3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("data.xlsx").Activate
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
Range(Selection, Selection.End(xlUp)).Select
Range("AR2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("FBN JBL OOR_TEST FILE.xlsm").Activate
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub
 
Upvote 0
SantanaKRE8s said:
Im using Kutools to open both both
Click to expand...
That doesn't make much sense to me. Isn't Kutools an add-in?
Can you explain this in more detail?

It looks like you are putting the code in the "ThisWorkbook" module, which is where it needs to go.
Are you sure that Macros/VBA are enabled the way that you are opening it?

In your last post, why do you have two Sub lines at the beginning?
That is not valid code. You cannot imbed one Sub inside of another.
Rich (BB code): 
Private Sub Workbook_Open()

Sub GET_DATA()
'
 
Upvote 0
Yes that was my mistake the two sub lines. But the original VBA does not have two Subs and neither does it have the code to run macro when workbook opens. The macro works but when i put the code to run when it opens thats when its stops working
 
Upvote 0
SantanaKRE8s said:
Yes that was my mistake the two sub lines. But the original VBA does not have two Subs and neither does it have the code to run macro when workbook opens. The macro works but when i put the code to run when it opens thats when its stops working
Click to expand...
You did not address the other questions I had for you in my previous reply.

Also, you can try adding this line to the top of your code:
Rich (BB code): 
Private Sub Workbook_Open()

    MsgBox "Workbook_Open code is running"
This will at least let you know if the event is firing, as the first thing it will do is return this Message to the screen.

If you get this message, then the code actually is firing, which means that if it is not doing anything, there is an issue with the code itself.

If you do NOT get this message, then the code is NOT firing, which means one of the following:
- The code is not placed in the correct module
- The code is not named correctly
- VBA/Macros have not been enabled
- Events have been disabled in your present Excel session
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
168
shknbk2
S
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
526
jolivanes
jolivanes
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
107
willow1985
willow1985
P
  • Solved
Counter in For Each loop
Replies
2
Views
198
Possum17
P
P
  • Solved
Recorded Macro Ammendments
Replies
3
Views
346
Joe4
Joe4

Forum statistics

Threads
1,215,069
Messages
6,122,958
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top