I have wrksht 1 as an entry page for an invoice, I have an array formula that copies relevant cells entered by user to another hidden wrksht used as a database, problem is i want to have the array copy a protected cell with a formula in it ( the "Total" cell for the invoice) by i get a #Ref! on the database worksheet and a copy of the formula, not the result (total amount) i assume I cant use an array to copy cells with formulas in them, is this true ?
Computer OS: Windows 7Browser: FirefoxProgramming Language: vbaCompiler: visual basic excel 2003
Private Sub database()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim r1 As Range, r2 As Range
Dim v1 As Variant, v2 As Variant
Set sh1 = Worksheets("office")
Set sh2 = Worksheets("data")
' specifing the cells that will contain information in sheet1(office)
v1 = Array("aq5", "k5", "o5", "k7", "L23", "c23", "c30", "g30", "p30", "L32")
' in the same order specify what columns that info would be placed in sheet2
v2 = Array("B", "D", "E", "F", "G", "H", "I", "j", "K", "L", "M")
' NOW FIND THE NEXT OPEN ROW IN SHEET2 USING COLUMN B
rw = sh2.Cells(Rows.Count, "b").End(xlUp).Offset(1, 0).Row
'now copy the data to the data base
For I = LBound(v1) To UBound(v1)
Set r1 = sh1.Range(v1(I))
Set r2 = sh2.Cells(rw, v2(I))
What you can do is add a line of code to unprotect the sheet that the macro attempts to change then with another line of code at the end it will re protect the sheet when the macro is done.
I can add these lines for you if you can give me the name of the sheet that is protected and the password.
by unprotecting my sheet, that will allow the array to copy the sum value and not the formula as it is doing now??also, though my sheet is protected, the array has no problem copying the unprotected cells that are empty before my user types something in, it's just that the array is copying the formula from my total cell to my database and i get something like this.. =sum(#ref!,#ref) etc. instead of the value of the cell, (example $ 400.00 ).....
Ok, I think I am a little confused here. Maybe I am not understanding the question because the code that you have posted came through gabbled. I suggest you send me the file so I can see the code and the data in the worksheet.
I will send you my file with my sheets unhidden, i hide them because they are all unprotected by necessity. only the office sheet is seen by the userhow do i send you my file
Ok to send the file go to www.wikisend.com and upload it there. You will then get a page that has the download link. Copy the download link and come back here and paste it in your reply.
Got it, thanks.
There are several sheets and several macros in that file. Now that I have the file could you restate your question making reference to the specific cells in question and the sheet names
I am concerned only with the Database() and the Data worksheetDatabase() is called when the PRINT commandbutton is pressedthe array copies the cells i need from the Office sheet to the datasheetexcept for when it trys to copy AQ42 on office to the "M" column on the datasheet, then I get the #ref! Instead of copying the sum value of that cell it copies the formula in that cell. I am trying to understand how i can copy the sum of the formulaand not the formula. As you can see it has to happen automatically and at the same time.Any Ideas?thanks
Ok, you have incorrect formulas in the Data sheet, i.e.formulas in cells that refer to themselves causing circular reference. It is hard to diagnose that workbook with all the protection and the all functionality hidden/disabled. Took me 30 min. just to get to this point.
Sorry for the trouble and yes, the release date or column c in the data worksheet is intentionally set up as a circular reference but is not called in the array in Databae(),butrather enters todays date when the corresponding "b" column has data put in it by the array.
Ok, I am finding that the code has many problems. I can try to fix it for you but considering the time spent on it so far and the time to fix it please make an offer if you want me to proceed further. Just letting you know that so far all assistance has been for free since you have not clicked the accept button yet.
Vast experience in Excel programming and business solutions
Eventually i will have my "print" button also email a copy of the invoice to my boss as well as putting it in the database and saving a pdf copy in a directory as it does now.if i can ever get my database to record a total for the transaction,by the way, I don't want the save button to save anything, I want the user to think all he's done is print out an invoice when he hits print, thus all the programing and calling from theprint button.If you think you can help me while still allowing me to do this than great,ps. I am doing this for free, if you can believe it .I can pay you Fifty now and additional if you have any ideas about automatic emailing to an msn account. I have some ideas and have also surfed extensively as you can tell by some of the code. If this is acceptable let me know. thanks
Thanks for the accept and the offer.
I will first try to fix the database() macro, and you don't have to pay until it has been fixed to your satisfaction.
We will then move to the email issue.
I will be start rewriting the database macro in a short while. Will keep you posted.
Thanks.I will be headed home soon, reachable by e-mail from then on...I check the email at least once or twice a day, I will check more often till I hear from youXXX@XXXXXX.XXX, but I'm sure you have that.
Ok, here goes.
First, the confusion is coming from the way the variables are assigned to the array elements. Then when using the 'Copy' command in the code the the whole 'content' of the cell is copied with the formula. So for cell AQ42 it copies the formula itself , i.e, "=SUM(AQ21,AQ24,AQ27,AQ30,AQ33,AQ36,AQ39)" and places it in the first available row in M of Data sheet. Naturally placing this formula in, say, cell M5, will produce a ref error since there aren't 20 or so rows available above row 5 in that sheet (try copying cell AQ42 manually and pasting it in cell M5 or M6 in Data sheet, you will get the same error)
To fix the problem just assign one cell to be equal to the other cell (i.e. using a=b) instead of copying the contents of the cell.
Here is that command as used in a simplified version of the code. Just copy the code below and paste it in place it to replace your entire Sub Database() code, and it should work fine.
Sub database()Dim sh1 As WorksheetDim sh2 As WorksheetDim r1 As Range, r2 As RangeSet sh1 = Worksheets("office")Set sh2 = Worksheets("data")rw = sh2.Cells(Rows.Count, "b").End(xlUp).Offset(1, 0).Rowsh1.Range("AQ5").Copy sh2.Range("B" & rw)sh1.Range("K5").Copy sh2.Range("D" & rw)sh1.Range("O5").Copy sh2.Range("E" & rw)sh1.Range("K7").Copy sh2.Range("F" & rw)sh1.Range("L23").Copy sh2.Range("G" & rw)sh1.Range("C23").Copy sh2.Range("H" & rw)sh1.Range("C30").Copy sh2.Range("I" & rw)sh1.Range("G30").Copy sh2.Range("J" & rw)sh1.Range("P30").Copy sh2.Range("K" & rw)sh1.Range("L32").Copy sh2.Range("L" & rw)sh2.Range("M" & rw) = sh1.Range("AQ42")End Sub
Hope it is ok. Let me know how it goes.
this seems to work fine, will take to work tommorrow just to be sure,
Sure. Let me know how it goes.
Thanks for the accept and bonus :)
Now for the automated emailing procedure I will need some details, such as which sheet do you want to send, what is the email address to send it to, and what text do you want to have in the email subject line. Also I am assuming that you have Outlook already set up as your default email program on your system.
now it gets complicated, at least for me.My pdfPrint() macro uses the free program Pdfcreator to copy the INVOICEworksheet to the tow invoices directory as a pdf file the same time as the invoice is printed.I have a macro from the net that will email an excel file automatically, but i have not tested it and would much rather email the pdf version instead.the idea is the the user pushes "print" and an invoice is printed, copied to a database,and then emailed to my bosses computer at home. All this without the user knowing or having to press any additionalbuttons to make it happen.so far i am two for threethe saving the excel sheet as pdf and to a folder works fine.the saving to a database works fine thanks to you.The reason to send the pdf file is for the unchangible nature of it.the email subject line could be blank or the invoice number.the email is "XXXXX@XXXXXX.XXX"the worksheet i would like to send is the INVOICE2, since it has only one copy of the invoice on it.thanks,
I am not sure if the pdf file can be sent through the sendmail procedure, but here is what I can write the macro to do:
When you issue the command the macro will first create a temporary copy of Invoice2, in that same workbook, then it will convert the formulas in all cell into fixed values so the sheet will be totally independent of the other sheets and the recipient won't have to have to worry about any links to other sheets (much like a pdf version). Then it will create a new temp workbook and put the copy of Invoice 2 in it. It will then send that temp workbook (which will have one sheet in it) as an attachment to he specified recipient.Then it will go back to the main workbook and remove the temp Invoice 2 from it.
If that is ok let me know so I can write that macro for you.
how about the pdf as an attachment, look at the following code
I can't make it work, maybe you can ???
if not let me know
Private Sub SendEmail()
Dim olApp As Outlook.ApplicationDim myNameSp As Outlook.NamespaceDim myInbox As Outlook.MAPIFolderDim myExplorer As Outlook.ExplorerDim NewMail As Outlook.MailItemDim OutOpen As Boolean
' Check to see if there's an explorer window open ' If not then open up a new one info line only OutOpen = True Set myExplorer = olApp.ActiveExplorer If TypeName(myExplorer) = "Nothing" Then OutOpen = False Set myNameSp = olApp.GetNamespace("MAPI") Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox) Set myExplorer = myInbox.GetExplorer End If myExplorer.Display ' You don't have to show Outlook to use it
' Create a new mail message item. info line only Set NewMail = olApp.CreateItem(olMailItem) With NewMail '.Display ' You don't have to show the e-mail to send it hokey display line only .Subject = "Look at this!" .To = "XXXXX@XXXXXX.XXX" .Body = "new release" .attachments.add ("c:\documents and settings\compaq_administrator\mydocuments\tow invoices\email\invoice2.pdf")
NewMail.Send If Not OutOpen Then olApp.Quit
'Release memory. Set olApp = Nothing Set myNameSp = Nothing Set myInbox = Nothing Set myExplorer = Nothing Set NewMail = Nothing
No I do think can make it work. Many errors come up. Besides it is more efficient to write a new code than try to debug somebody else's code.
If you want to go with my suggestion I will be happy to comply.
okay then, go ahead
Ok, I should have it ready by noon tomorrow (pacific time).
Ok, here is the file with the macro called SendInvoice2() which is triggered by pressing Ctrl + Q from any sheet. In this file I removed all other macros so you can clearly focus on this one.
Before running the macro please make sure you enter the recipient's email address in this line of code, between the quotes .
Recpt = ""
You can also change the text for the Subject line in you like. It is the line that follows
The macro does the same tasks that I mentioned before, i.e.
"...When you issue the command the macro will first create a temporary copy of Invoice2, in that same workbook, then it will convert the formulas in all cell into fixed values so the sheet will be totally independent of the other sheets and the recipient won't have to have to worry about any links to other sheets (much like a pdf version). Then it will create a new temp workbook and put the copy of Invoice 2 in it. It will then send that temp workbook (which will have one sheet in it) as an attachment to he specified recipient.Then it will go back to the main workbook and remove the temp Invoice 2 from it.."
Hope it is ok. Try it and let me know if it needs any tweaking or if you have any questions.
I install your macro in module4 , i ran it off my pdfprint macro,i made sure that outlook is the default e-mail program thru control panel / internet optionsbut when i run the macro, enternet explorer opens, msn live opens the msn e-mail. the e-mail IS addressed correctly to the recipient but has no attachments and is waiting for me to press "Send"We were trying to email with attachment and then return to the program, so far its not working unless I've done something wrong.
That is most likely because you still have msn Windows Live as your default email program.
Open IE, go to Tools > Internet Options > Programs tab, then make sure the email is set to Microsoft Office Outlook as shown below.
for some reason when i changed outlook thru the control panel, it did not work,butwhen i went thru the motions you described (outlook was already picked so i didn't need to change anything. it worked go figure.one last question ,,,everything works, BUT is there anything to do to stop the outlook security window from popping up, making mewait 5 seconds, than making me press "yes" to send my email. anyway to answer yes automaticallythanks,
That is a security alert which is dependent on your Outlook settings and your system security in general. Unfortunately it is not that easy to get rid off this alert if you are using Outlook - you may even need a third party utility to disable it. Here is a good article explaining how to do remove it.
Hope this helps.