Need to copy some data from workbook A to Workbook B

Copper Contributor

Workbook A contains all of the code and a simple spreadsheet("Entry") for entering the data.  When a row is completed the data is copied to a more comprehensive spreadsheet ("Data") in workbook B.  After data is posted to the "data" worksheet the "Entry" row is cleared for the next entry.

 

I created a working version using just 1 workbook with 2 worksheets ("Entry" and "Data") but now need to split into 2 different workbooks.  All of the code is contained in Workbook A.  I am trying to do a simple Copy and Paste but can't seem to make Workbook B be the target for the paste.

 

Any help is gratefully appreciated.  Thanks.

10 Replies

@Bob Hilton 

You might use a macro like shown below to transfer your data.

 

As written, Workbook A contains the code and a worksheet named Entry. Values in cells A2:D2 in that worksheet will be copied to a worksheet named Data in Workbook B (which I named Book2). I commented those statements in the code, and you will definitely need to edit them to match your actual circumstances.

 

When you run the macro, the code looks up worksheet Data column A for the last cell with information. The info from worksheet Entry will be copied into the next row on worksheet Data. It is important that column A (or whichever one you specify) must always contain values.

Sub MoveToData()
Dim wsData As Worksheet
Dim cel As Range, rgEntry As Range

Set wsData = Workbooks("Book2").Worksheets("Data")                      'Change to match actual names
Set rgEntry = ThisWorkbook.Worksheets("Entry").Range("A2:D2")           'Change to match actual names and data entry range
If rgEntry.EntireRow.Cells(rgEntry.Rows.Count, "A").Value = "" Then     'The column specified must always contain data
    MsgBox "Column D must not be blank. Edit your entries then retry."
    Exit Sub
End If

With wsData
    Set cel = .Cells(.Rows.Count, "A").End(xlUp)                        'The column specified must always contain data
End With

If cel.Value <> "" Then Set cel = cel.Offset(1, 0)
cel.EntireRow.Resize(rgEntry.Rows.Count, rgEntry.Columns.Count).Value = rgEntry.Value
rgEntry.ClearContents
End Sub

@Brad_Yundt 

Thanks for the quick response Brad!  I was making this too hard.  But I still have an issue that I am confused on.  what is the best way to have the user start Excel that is foolproof so they don't forget the data workbook?  How do I open the workbook in code?  

 

I am now getting an error when attempting the set rgEntry=thisWorkbook... statement.  Error is run-time error '1004' select method of range class has failed.  It all worked ok when I was in debug mode but when I saved everything and closed Excel, then attempted to start from double-clicking on the .xlsm file I get the error.  I have a workbook_open sub which calls some initial routines that display info on the Entry worksheet.  Obviously I missing something basic (no pun intended).  Thanks again for your help.

@Bob Hilton 

The 1004 error results when VBA can't find a workbook or worksheet by the name shown in the code.

 

Since you didn't post sample workbooks, I may have made some incorrect guesses on the names and locations of your data. I commented the statements I thought you might need to review and possibly change.

 

I can certainly open the other workbook programmatically, and the ideal place to do so is in a Workbook_Open event sub. You may only have one of those, so I'll need to tweak your existing code.

 

Please post sample workbooks so I can adjust the code, confirm data location, workbook name, worksheet names, then test everything.

 

Brad

@Brad_Yundt 

Thanks again for the prompt response.  I just realized the response I sent to you did not go because this system did not like .txt files.  I've attached a sample of the first part of my code.  What I would like to be able to open the data workbook (dataName which is "JustSoldData.xlsb) in the GetDataFile sub.  Thanks again for your help and your patience.  (It's embarrassing how much I've forgotten in the 10 years since I did any VB or VBA.)   Code follows:

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 rgEntry As Range
Dim cel As Range


Private Sub workbook_open()
Call GetEntryFile
Call GetDataFile

' MsgBox "we did it"

End Sub

Function IsWorkbookOpen(ByVal WorkbookName As String) As Boolean

' Returns True if the specified workbook is found open in the current instance of excel
On Error Resume Next
IsWorkbookOpen = CBool(Len(Workbooks(WorkbookName).Name) <> 0)
On Error GoTo 0
End Function
Public Sub GetEntryFile()
Dim srcFilePath As Variant
Dim controlname As String
srcFilePath = ThisWorkbook.FullName

entryName = ThisWorkbook.Name
bEntryOpen = IsWorkbookOpen(entryName)
If Not bEntryOpen Then MsgBox "Entry workbook failed to open", vbCritical, "Entry Workbook Open Failed"

ThisWorkbook.Worksheets("Entry").Range("B22").Value = srcFilePath
End Sub

Public Sub GetDataFile()
Dim dataFilePath As Variant
Dim wbx As Workbook
MsgBox ("Please select the file containing the data for Neighbors")
dataFilePath = Application.GetOpenFilename _
("Excel files (*.xls, *xlsx, *xlsm, *xlsb")
If dataFilePath = False Then Exit Sub
dataName = Right(dataFilePath, Len(dataFilePath) - InStrRev(dataFilePath, _
Application.PathSeparator))
bDataOpen = IsWorkbookOpen(dataName)
If Not bDataOpen Then MsgBox "Data workbook (file) failed to open", vbCritical, "Data Workbook Open Failed"

ThisWorkbook.Worksheets("Entry").Range("E22").Value = dataFilePath
end sub

@Bob Hilton 

FWIW, sub GetEntryFile can never fail to find the entry workbook--because it is the one containing the code that is running. Not a problem--just that you have a useless test of whether that file is open.

 

The data file workbook, however, may or may not be open. I revised sub GetDataFile so it would open the workbook if need be:

Public Sub GetDataFile()
Dim dataFilePath As Variant
Dim wbx As Workbook
'MsgBox ("Please select the file containing the data for Neighbors")
dataFilePath = Application.GetOpenFilename _
("Excel files (*.xls, *xlsx, *xlsm, *xlsb", Title:="Please select the file containing data for Neighbors")
If dataFilePath = False Then Exit Sub
dataName = Right(dataFilePath, Len(dataFilePath) - InStrRev(dataFilePath, _
Application.PathSeparator))
bDataOpen = IsWorkbookOpen(dataName)
If Not bDataOpen Then
    Workbooks.Open dataFilePath
    'MsgBox "Data workbook (file) failed to open", vbCritical, "Data Workbook Open Failed"
End If
ThisWorkbook.Worksheets("Entry").Range("E22").Value = dataFilePath
End Sub

@Brad_Yundt 

Man, you must never sleep!  Once again thanks for the quick response.  I had tried the workbooks.open statement several days ago but must have fat fingered something.  Thanks for putting up with me, your patience and your expertise.

Bob

@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.

image.png

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 = 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