Forum Discussion
Need to copy some data from workbook A to Workbook B
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 = 20000
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
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