Trying to copy a macro into another workbook

[Naomin]

Hi everybody, my first post here. This is a VBA 101 question. I'm just getting into VBA and having fun.

I created a spreadsheet with multiple macros, plus one that runs all the others. So far, so good. I noticed that for some reason the macros were distributing themselves randomly into modules.

So I made a copy of the workbook to experiment on. First, to clean up the modules, I cut and pasted the code into one module and deleted the others. Now when I run the macro from the ribbon, they don't work; but when I step into the code, they do work. However, the new spreadsheet works as designed if the old one is open. I am assuming this is because the macros are looking for the file under which they were created; the macros store the original file name somewhere that I can't locate. Can someone guide me to this? I would attach a copy of the macros and sheets, but then it won't work!

So I have 2 questions: why do the macros land in different modules? and how can I update the new file so the run macro command finds the macros within that file, not the old one?

thanks,
Naomi
:creator:

[Paul_Hossler]

Different modules are for different purposes

(Some of the following is personal preference)

Most code should be in a 'Standard Module'

Each 'UserForm' module has a code sheet, but I usually only put the event handlers for that user form on it

'ThisWorkbook' has a code sheet, but I usually only put event handlers that apply to the entire workbook there

Each 'Sheet' module has a code sheet, but I usually only put the event handlers for that sheet on it (includes ActiveX controls that you put on the sheet)

'Class Modules' we'll leave to a later day


Attached is a little demo you can step through if you want and look at the way I like to do it -- I put a 'Stop' in the Workbook Open event so you can use F8 to single step through the code

[Naomin]

Thanks Paul, I have to say your response was a bit above my head. I'm just dipping my toe into VBA and was hoping for a more direct response, cause I haven't the foggiest clue as to what made my sheet behave the way it did. I'll take a look at your sample, however!

Naomi

[Paul_Hossler]

I would attach a copy of the macros and sheets, but then it won't work!

If you can't get it sorted out, go ahead and attach it and I'll / we'll take a shot at rearranging the code so you can see

[Naomin]

What I meant by "it won't work" is that if I save my file as a different name, the macros won't work because they'll be looking for the original file. I can't send you the original; it's proprietary.

[Paul_Hossler]

Just the copy which should be empty would probably be enough

[Naomin]

What should be empty? The sheet? Sorry, I'm not understanding.

[snb]

We can't see the code you are using.
Post a sample file here, so we can have a look.

[Paul_Hossler]

What should be empty? The sheet? Sorry, I'm not understanding.

You said that the copy of the workbook wasn't working

Can you post the copy with the macros, but delete any data?

[Logit]

.
To post your code :



How do I post code samples?

When creating or replying to a post, do the following:

1. paste your VBA code into the text area
2. highlight (select) the code
3. click the # button
The button is found just above the text area where you write your question. This will automatically wrap the
tags around the selected code.

VBA tags make your code appear in your thread in the same way that you see it in your code pane in the Visual Basic Editor (VBE) window, thus making it far easier for others to read.

As an alternative, you may surround your code manually by writing "[CODE]" before your code, and "" after the code sample.

Please only post the relevant section(s) of your code. Don't post 50 lines of code if you only need help debugging one variable. Create a test case to demonstrate an issue.



You can post a copy of your workbook by clicking on GO ADVANCED / MANAGE ATTACHMENTS ... then select the file you want to attach for review.

Be certain you remove any confidential data from your workbook prior to posting for public view.

[Naomin]

Hope this works the way you've suggested.

20256




Sub project_q()
'
' project_q Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-12, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(12, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").SelectEnd Sub
Sub Project_b()
'
' project_b Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-2, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Sub Project_c()
'
' project_c Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-3, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(3, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Sub Project_d()
'
' project_d Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-4, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(4, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Sub Project_e()
'
' project_e Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-5, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(5, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Sub Project_g()
'
' project_g Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-6, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(6, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Sub Project_h()
'
' project_h Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-7, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(7, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Sub Project_i()
'
' project_i Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-8, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(8, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Sub Project_j()
'
' project_j Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.Offset(-9, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(9, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub

[Paul_Hossler]

To run macro in the workbook, you shouldn't use the WB reference



Sub run_all_macros()
'
' run_all_macros Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Application.Run "'To Do list with macros 6-14.xlsm'!Project_a"
ActiveCell.Offset(1, 0).Range("A1").Select



You can just call the macro in the workbook



Sub run_all_macros()
'
Project_a ' <<<<<<<<<<<<<<<<<<<<<<<<<<


ActiveCell.Offset(1, 0).Range("A1").Select



I'm not sure about what you're wanting to do, but while the macro records includes .Select and Scroll and other stuff, it's really not necessary

Also, I think you can make your macros a little smarter so you don't need so many




Option Explicit
'just selected cell in col Q
Sub UpdateStatus()
Dim rSelected As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set rSelected = Selection.Cells(1, 1)
If rSelected.Column <> 17 Then Exit Sub

rSelected.Offset(0, -1).End(xlToLeft).Copy rSelected
End Sub


'all status that have entry in col P, no need to select anything
Sub UpdateStatusAll()
Dim colP As Long

Application.ScreenUpdating = False
With Worksheets("to do")
For colP = 3 To .Cells(3, 16).End(xlDown).Row
.Cells(colP, 16).End(xlToLeft).Copy .Cells(colP, 16).Offset(0, 1)
Next
End With
Application.ScreenUpdating = True
End Sub

[Naomin]

To run macro in the workbook, you shouldn't use the WB reference



Sub run_all_macros()
'
' run_all_macros Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Application.Run "'To Do list with macros 6-14.xlsm'!Project_a"
ActiveCell.Offset(1, 0).Range("A1").Select



You can just call the macro in the workbook



Sub run_all_macros()
'
Project_a ' <<<<<<<<<<<<<<<<<<<<<<<<<<


ActiveCell.Offset(1, 0).Range("A1").Select



I'm not sure about what you're wanting to do, but while the macro records includes .Select and Scroll and other stuff, it's really not necessary

Also, I think you can make your macros a little smarter so you don't need so many




Option Explicit
'just selected cell in col Q
Sub UpdateStatus()
Dim rSelected As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set rSelected = Selection.Cells(1, 1)
If rSelected.Column <> 17 Then Exit Sub

rSelected.Offset(0, -1).End(xlToLeft).Copy rSelected
End Sub


'all status that have entry in col P, no need to select anything
Sub UpdateStatusAll()
Dim colP As Long

Application.ScreenUpdating = False
With Worksheets("to do")
For colP = 3 To .Cells(3, 16).End(xlDown).Row
.Cells(colP, 16).End(xlToLeft).Copy .Cells(colP, 16).Offset(0, 1)
Next
End With
Application.ScreenUpdating = True
End Sub

[Naomin]

Paul, I appreciate your help but this is way above my head. I don't expect to get this with this one post. Could you just tell me what a WB reference is?

Thanks.

[Paul_Hossler]

In



Application.Run "'To Do list with macros 6-14.xlsm'!Project_a"


'To Do list with macros 6-14.xlsm'! is the workbook reference

If the macro Project_a is in the same workbook as the macro Run_all_macros, you just need to call the macro without the Application.Run



Sub run_all_macros
Project_a
Project_b
Project_c
Project_d
etc.


I suspect that you were recording a macro in one workbook and ran macro Project_a in "To Do list with macros 6-14.xlsm"