Jul 14 2019 12:22 PM
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.
Jul 14 2019 04:01 PM
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
Jul 15 2019 08:47 AM
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.
Jul 15 2019 09:32 AM
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
Jul 16 2019 07:07 PM
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
Jul 16 2019 07:47 PM
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
Jul 17 2019 05:56 AM
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
Jul 17 2019 06:23 AM
@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
Jul 17 2019 01:08 PM
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
Jul 22 2019 06:03 PM
@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
Jul 22 2019 09:31 PM
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