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