Forum Discussion
Need to copy some data from workbook A to Workbook B
Brad_Yundt one more set of questions. As you could see I have a workbook_open sub (is this a reserved name?). What triggers it - (I know I can only have one). All of my code is in Module 1. Is there also a similar structure for closing i.e. when the user clicks on the X so that I can save (or not) and close the data workbook and save (or not) the Entry workbook before exiting out the "program"?
Thanks again for sharing your knowledge.
Bob
Bob,
Excel VBA has a pre-defined set of so-called "event subs" that run whenever the event they are associated with occurs. Each of the worksheets and ThisWorkbook have their own set. In my copy of Excel, there are 48 in ThisWorkbook, which you can see by choosing Workbook in the left field and then clicking the field on the right to display the dropdown choices.
Some event subs run before the associated activity is completed, while others run after. All of them are triggered automatically--you need do nothing more than include the stub (first and last lines of the sub, which Excel generates for you automatically) in the class module sheet.
It is possible to have a sub named Workbook_Open in a regular module sheet. That sub won't run automatically when you open the workbook, and if you have another Workbook_Open sub in ThisWorkbook, you will need to qualify the sub names so VBA knows which one you mean, e.g. Module1.Workbook_Open. All that said, it isn't good practice to have a sub in a regular module sheet with the same name as an event sub.
"All my code is in Module1." That's good practice.
The snippet below shows how you might save and close a workbook.
Workbooks(dataEntryName).Save. 'Save workbook Workbooks(dataEntryName).Close SaveChanges:= False. 'Close without saving Workbooks(dataEntryName).Close 'If changes made, Excel will ask to Save before closing
Brad_Yundt Thanks for all the help you've given me previously. Now I'm back with more tribulations.! Experiencing some code that works sometime and sometimes doesn't. When I have a row of data from the Entry worksheet I call this sub (PostAddr). I have the Entry sheet on the screen, check to make sure I have data to post. I haven't cracked the code to switch to the Data sheet in the data workbook. Using this statement : Set wsData = Workbooks(dataName).Worksheets("Data") seems to execute properly, but anything I have come up with for the next statement fails with a 1004 error. e.g. wsData.Rows("4:4").Select. If I skip this statement and go to the next statement:
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove a blank row 4 is inserted on the Entry worksheet . I know I'm missing something fundamental but can't figure out what.
As always, thanks for any help you can give.
failing code below
Option Explicit
Public iCurrentRow As Integer
Public entryName As String
Public dataName As String
Public bEntryOpen As Boolean
Public bDataOpen As Boolean
Public wsData As Worksheet
Public wsEntry As Worksheet
Public rgEntry As Range
Public rgData As Range
Public cel As Range '??
Public Const lastRow As Long = 20000Sub PostAddr()
'
' PostAddr Macro
' Post entry data to data sheet
'
' Keyboard Shortcut: Ctrl+Shft+A
'
Dim mySpace As String
Dim longAddress As String
Dim addrLen As IntegerApplication.ScreenUpdating = False
If CheckForDuplicateAddress = True Then Exit Sub
mySpace = " "
' check to make sure there is data to post
ThisWorkbook.Worksheets("Entry").Select
addrLen = Len(Trim(Range("B9")))
If addrLen < 5 Then
Call ShowPreviousEntry
Exit Sub
End If
' insert a blank row pushing existing rows down
Set wsData = Workbooks(dataName).Worksheets("Data")
wsData.Rows("4:4").Select
' Set rgData = Rows("4:4")
' rgData.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'.....end sub
Bob,
Recorded macros are littered with .Select and .Activate statements. You don't need them, and getting rid of them will make your macro shorter, easier to debug, and faster.
You will notice that I rewrote your code to use With blocks. When you use an object in a With block, any time you start an expression with a dot, VBA knows you are referring to the With block object. And because VBA doesn't have to resolve a fully qualified reference, the code runs faster. When you ask for help on Excel help forums, you should anticipate almost everybody either getting rid of the .Select statements for you, or wishing that you would get rid of them yourself.
The one concern I have is that after I eliminated your .Select statements, your code calls the sub ShowPreviousEntry. I don't know which worksheet needs to be active when you run that sub--but I may have broken a dependency.
Ideally, all of your code will be rewritten to get rid of the .Select statements. You may need to learn a little bit of programming from scratch, but I have found most people pick it up with about an hour of study or one on one tutoring.
Brad
Sub PostAddr() ' ' PostAddr Macro ' Post entry data to data sheet ' ' Keyboard Shortcut: Ctrl+Shft+A ' Dim mySpace As String Dim longAddress As String Dim addrLen As Integer Application.ScreenUpdating = False If CheckForDuplicateAddress = True Then Exit Sub mySpace = " " ' check to make sure there is data to post With ThisWorkbook.Worksheets("Entry") addrLen = Len(Trim(.Range("B9"))) End With If addrLen < 5 Then Call ShowPreviousEntry 'Does this sub depend on a particular worksheet being active? Exit Sub End If ' insert a blank row pushing existing rows down Set wsData = Workbooks(dataName).Worksheets("Data") With wsData Set rgData = .Rows("4:4") rgData.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove '..... End With End Sub