Getting Error 1004 When Saving And Emailing?
Jun 25, 2014
I've created a userform below and I have a code to write the data entered in the form do the following:
1. Save the data in a worksheet (Asset History) within the same workbook,
2. Copy the newly entered data into a another worksheet (Asset Form).
3. Take an image of the data from worksheet (Asset Form) and
a) Create a HTML image on out look
b) Send out an email (Get the email distribution from worksheet (EmailList)
4. I also want to be able to select the data nad change if any of the information changes using the userform.
Not sure why my code is getting stuck and getting the attached error "1004" when I get to saving and emailing. My guess is that the error is on the Checklist option explicit but I cants fix.
Here is the full coding
Private Sub cmdsave_Click()
Dim cell As Range
Dim rowoffset As Integer
[Code]..............
View 14 Replies
ADVERTISEMENT
Feb 7, 2013
Refining the below code which repeats itself for 60 agents per worksheet, for 7 worksheets. Is there a way to loop the code so it counts up 68 cell references until the 'blank cell' condition is met?
[code]'## Agent 1
If Sheets("CSR Dashboards").Range("M3").Value = "" Then sResult = MsgBox( _
Prompt:="CSR Dashboards have been sent.", _
Buttons:=vbOKCancel)
If sResult = vbOK Then
[Code] ........
View 9 Replies
View Related
Aug 26, 2013
How/ where to save macros. If I save a macro "normally" ("in this workbook") then it only applies to that one file, right? And I can't use it in any new files.
So what do I do if I want a macro I can use in "all" my Excel files?
And can I email a file containing a macro to a colleague, so he can work on the file, using the macro too?
I ask, because I've had trouble with this in the past (tho in MS Word) where I couldn't get the macro to be emailed along with the file...
Also - how can I get an .xls file with a macro to work in newer versions of Excel? And can I use Excel 2010 to save a file with a macro in an .xls format, so it can be opened in older Excel versions too?
View 3 Replies
View Related
Oct 13, 2009
I have an original workbook that I use daily stored in a folder called 'Live'. I also have this same workbook saved in a different folder call 'In Progress' because I am constantly improving and making changes to it. While I'm in the revision mode, I keep it in this folder. Once I've completed my revisions, I move it to the folder where the live workbook is located and replace the old version. Therefore, I have two copies of the workbook at all times. The name of the workbook in both folders is the same (IRQT)
I have a macro in this workbook that will allow you to save a copy of the workbook in a folder called IRQTArchive. The macro will create the folder if it doesn't exist, then store a copy of the workbook using a name in a cell. After that is complete it renames the workbook back to the original name (IRQT).
This macro works perfect in my live version, but when I execute the macro in the 'In Progress' version, I get a Compile error: Sub or Function not Defined when it reads the dirExists command in the first line of code. The code in both sheets is identical. Here are the first few lines of code. The command dirExists is highlited in yellow when it errors out.
View 11 Replies
View Related
Jul 2, 2006
the if stattement works perfectly and does exactly what i want except when it comes to the else part. if there is no error the statements are run perfectly but if there is an error (in this case the error is generated when a match cannot be found in the spreadsheet) the else statement doesnt kick in and post the msgbox.
the code just crashes. and returns an error 1004 on the line i have highlighted in yellow
res = WorksheetFunction.Match(invvar, Columns(1), 0)
If Not IsError(res) Then
Me.txtClientID.Value = ws13. Cells(res, 7)
Me.txtNumber.Value = ws13.Cells(res, 7)
Me.txtDate.Value = ws13.Cells(res, 8)
'save client id as a variable
'Print to invoice------------------------------------------------------------------.....................
View 6 Replies
View Related
Aug 19, 2009
Before I go into details I am working in one workbook with several worksheets(Tabs) in the workbook. I have three Buttons on one tab. the button I am having trouble with is the third. I want to insert a column in a separate tab that contains approximately 87,000 rows of data. I then want to do a row count and select the empty cells in the inserted column and put a formula in there. I have tried a loop VBA code and it works, but it takes about 30 minutes to run all the way through. The underlined portion is what is higlighted with error 1004: Application-defined ro object-defined error. I am not sure how to fix this. This is what I have now:
View 13 Replies
View Related
Jan 17, 2008
I'm using VB 6.5 and Excel 2007. I've got a workbook that has code that creates another workbook (by copying sheets out of the current workbook) and drops a button on that workbook and creates a macro for that button. Everything works except one thing which gives me
Runtime Error 1004: Unable to update the Caption Property of the Button Class.
If I run the code manually (rather than calling the subroutine from from within another subroutine) it runs without error but if I call it from within the subroutine that copies sheets from the original workbook to a new workbook I get the error. Any idea why this might be? What I might be doing wrong? The code for this subroutine is as follows:
Sub CreateButton()
Dim btnMyButton As Button
Set btnMyButton = ActiveSheet.Buttons.Add(460, 75, 140, 30)
btnMyButton.Caption = "Delete and Update Charts and Lists"
End Sub
View 9 Replies
View Related
Mar 5, 2013
I have a relatively complex report that I work with and a worksheet is no longer required. I have deleted the worksheet and reference to it hwoever when running the macro to pull all the data, it gets to the summary of all the data and i get the Run Time Error 1004 Application-defined or object-defined error pop up. ON reviewing it, it is on this line ActiveCell.Offset(0, 0).Range("a1:a" & Range_Height).Select of the below code...
VB:
Sub GetRangeName()
Sheets("TOTAL").Select
[Code].....
use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.
View 1 Replies
View Related
Apr 26, 2009
I have a simple function below to put in different forumlas in different cells to get stock quotes. When I run this I get runtime error 1004 application-defined or object-defined error. The first formula goes through but vba chokes on the next formula: ActiveCell.Offset(I - 1, 4).Formula = username
View 10 Replies
View Related
Aug 12, 2009
Run-time error '1004' Application-defined or object-defined error. I am trying to use this
View 2 Replies
View Related
Oct 4, 2009
I was trying to use the below code
View 4 Replies
View Related
Dec 4, 2009
I keep getting a Run Time Error 1004 (Application Defined or Object Defined Error) when my sub reaches this line:
ActiveCell.Formula = "=SUM(D222,D224,D226,D227,D229,...)"
In the actual line of code the "..." above is another 20-30 or so cells in column "D". Probably no more than 150-170 characters in the line.
If I remove half of the cell range names it works, but I need all of the cell ranges for the equation.
View 9 Replies
View Related
Jul 24, 2007
I am trying to copy the info from one workbook to another workbook.
I keep getting the above referenced error...
Private Sub CommandButton1_Click()
Dim filepath As String
filepath = Range("A100")
MsgBox ("File Appended")
Workbooks.Open (filepath)
Windows("CorrespondenceMaster.xls").Activate
Sheets("Sheet2").Select
Range("DesNo", "LocationPath").Select
Selection.Copy
Windows(filepath).Activate
Range("A2").Select
ActiveSheet.Paste
End Sub
View 3 Replies
View Related
Apr 9, 2014
I have a sheet that will force user to enable macro before revealing the sheets and enabling them to key in data. But because I need to protect the workbook from user deleting sheets and also having some locked cells. I got the run-time error 1004 unable to set visual property of worksheet class
View 1 Replies
View Related
Jan 12, 2007
Is there a limit on the amout of pictures you can use with the statement me.pictures.visible? Reason being, I am trying to import 119 pictures into a spreadsheet. When the information is selected form a drop down list, it pulls up the 1 of the 119 pictures. I was able to get 54 pictures input. Everything was going great until the 55th. When it stared giving me an error...
______________________________________________
Runtime Error '1004':
Unable to set the Visible property of the Pictures class
______________________________________________
When I select Debug, it takes me to the line with Me.Pictures.Visible = False
Everything seems to work fine until I enter the 55th picture. If it is that, is there a viable work around?
View 9 Replies
View Related
May 5, 2014
the problem I'm having is that when trying to copy the value of cell A2 and paste it in cell A4 with (INSERT with the second button of the sheet). this give me the error RUN TIME ERROR '1004 '.
View 7 Replies
View Related
Feb 22, 2007
Getting a error message when opening a workbook "Runtime error 1004" I dont know much about excel and the user explained it to me as best she could . Here is where the script bugs:
Sub auto_open()
fdist = ActiveWorkbook.Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("f_cumul").Cells.ClearContents
Worksheets("f_cumul").Cells.ClearFormats
Worksheets("vtes_dj").Select
Range("a1").Select
Selection.End(xlDown).Select..............
View 9 Replies
View Related
Jul 24, 2008
having a nightmare with a pivottable in VBA. I cannot give it a dynamic datasource. Surely i must be coding this wrong.
it breaks on setting the range for PTRange with the error
Run Time Error '1004'
application-defined or object-defined error
View 10 Replies
View Related
Dec 3, 2009
When I run the following macro i receive the following error
Runtime Error '1004'
Method 'Range' of object '_Global' failed
View 7 Replies
View Related
Dec 7, 2009
I'm trying to create a macro that will take a worksheet from an Excel workbook, and save it as a text file, with the name determined from user input, and the location being the desktop of the currnet user's computer.
First I use a function, (provided by RoyUK), to get the name of the current desktop, then I try to use that name, along with my user input, so save the file. Obviously, it's not working, or I wouldn't be here. I get a "Run-time error '1004': The file could nto be accessed". I think it's just a syntax issue with the file name, because debug takes me to the "SaveAs" portion of the macro.
View 2 Replies
View Related
Dec 8, 2009
I have a freaking problem coming from a Web Query. Actually, I get an "Execution Error 1004" because of the following piece of
View 7 Replies
View Related
Dec 10, 2012
Im almost finished my first code where the basic idea was to make activecell.name=activecell.value in the first column for the first 3 sheets. After messing with it i figured i couldnt have spaces in the cell name...then i learned i couldnt have random '()-'/ either. So i have the code formatted to do that and then its supposed to call to rename everything as well. but after the 1 sheets column is renamed the 2nd sheet gets error 1004 when range("A3").select is hit. What gives?
This is what ill be using the code on
[URL]
Here is the code
Code:
Sub RemoveChars()
' Works through first 3 sheets '
Sheets(1).Select
[Code]....
View 2 Replies
View Related
Apr 21, 2014
When I run the following code it keeps giving me a runtime error 1004.
Code:
Sub ImportData()
Dim fNameAndPath As Variant
Dim wb As Workbook
Dim cNextRow As Long
Dim Ans As String
NextRow = ThisWorkbook.Worksheets("Errors").Range("B" & Rows.Count).End(xlUp).Row + 1
cNextRow = ThisWorkbook.Worksheets("Compare").Range("A" & Rows.Count).End(xlUp).Row + 1
[code]...
View 3 Replies
View Related
Feb 11, 2007
Windows("VCS.xls").Activate
Range("C6:C20").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=aantal.als($C$6:$C$20;C6)=1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
When i start this macro i get an error in line ".Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=aantal.als($C$6:$C$20;C6)=1" (aantal.als = count.if)
The error i get is 1004, "Door de toepassing of door object gedefineerde fout".
Sorry i don't know what the english translation is.
When i put this validation manualy it's working fine, what's wrong in the code, am i missing something?
View 9 Replies
View Related
Aug 16, 2007
When running a Macro I am getting an error message as follows:
Run-time error '1004':SQL Syntax error
The line of code which appears to be effected is:
.refresh Backgroundquery:=False
I dont really know what this part of the code is trying to do and why it is highlighted yellow when I try and run the code. I am basically importing data from Access to Excel and this line is the last line of the code. If I remove this line, the error does not occur and the code completes. However, the data from Access is not imported so I am assuming it is an important part of the code!
View 9 Replies
View Related
Feb 15, 2008
I have this macro and I keep getting a 1004 runtime error 'PasteSpecial method of range class failed' on the highlighted line,
Sub Print_()
'
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Sheets("Print").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = xlAutomatic
End With
View 9 Replies
View Related
Apr 15, 2009
I am receiving the error:
Run-time error 1004 Application-defined or object-defined error.
It is occuring on the line of
Selection.QueryTable.Refresh BackgroundQuery:=False.
I am using SQL '05, Excel '03, and VB 6.3. I am trying to run a SQL stored procedure into Excel. So far all of my VB code seems to be running fine.
View 9 Replies
View Related
Oct 26, 2009
I work a lot with PCOMM and I have a macro that I can't get to work for the life of me. I have tried error handling, but unless the code passes the test it just gives me the 1004 error.
HTML Sub Testvlook()
Dim Calc1 As String
Dim Res As Variant
Dim scalc1 As String
'Find the TOPS window
If Loops = 0 Then
Set TOPS = New AutSess
TOPS.SetConnectionByName "A"
End If
Loops = Loops + 1
scalc1 = TOPS.autECLPS.GetText(13, 27, 6)
Calc1 = Application.WorksheetFunction.VLookup("scalc1", Range("p:q"), 2, False)
If Err.Number = 0 Then
MsgBox "Works"
Else
MsgBox "Didn't Work"
End If
View 9 Replies
View Related
Jun 20, 2006
why the following code would error out in line 2 on one workbook but not another? I copied the one line, then the two and then the entire code from one workbook to another. It works in one, but not in the other. (Error 1004)
Dim FirstBlank As Range
Set FirstBlank = Range("A5").End(xlDown).Offset(1, 0)
View 9 Replies
View Related
Mar 23, 2007
I am using the below code to select some spreadsheets under a folder, open them, select the entire font, change it to Arial and close the file. I am passing the file names from Column A and calling the below procedure in a loop. Everything works fine except the below problem.
If the file name I passed doesn't exist or the file is not accessable, I am getting an error message "File cannot be accessed. The file may be read only......" with two options "Retry" and "Cancel". When I click Cancel , it takes me to my code and stops at the line marked in red with Run-Time error '1004', Method 'Open' of Object 'Workbooks' failed. When the above error occurs , I need to just pass the value "ERROR" to ErrMsg filed and proceed further with the next file name.
Sub ChangeFont(FileName As String)
Dim objXL, objWb, objR ' Excel object variables
Dim Title, Text, tmp, i, j, file, name
Dim strPathToSaveTo As String
Dim strFileName As String
Dim wkb As Workbook
Dim wkb2Print As Workbook
strFileName = FileName
Application. ScreenUpdating = False
Set wkb2Print = Workbooks.Open(strFileName)
Cells.Select
With Selection.Font
.name = "Arial" .............
View 7 Replies
View Related