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