Sort Macro: Run-time Error 1004
Oct 25, 2006
I have created a macro which sorts 4 columns of data in asceding order numerically. I have assigned this macro to a button so that when the button is clicked, the columns of data are sorted.
Unfortunately when the button is pressed i get a vba run-time error 1004. I havent a clue how to resolve this. I recorded the macro useing the excel recorder function as opposed to typing it out manually.
The code created is given below. If i click debug when the error pops up, the code referring to column f:f is highlighted yellow.
View 4 Replies
ADVERTISEMENT
Jun 9, 2007
I encounter a runtime error '1004' if the "Invoices" sheet is not selected when I run this procedure. The last line of the code is one which is highlighted when I debug.
Sub ProcessData()
Dim aiOldRows() As Integer, aiNewRows() As Integer ' Arrays of new/old rows
Dim rngRaw As Range 'Data entry area
Dim rngInvoices As Range 'Invoices range
Dim rngOpenPoint As Range 'Top-left corner of data entry area
Set rngOpenPoint = ThisWorkbook.Worksheets("Data Entry").Range("a3")
Set rngRaw = Range(rngOpenPoint, rngOpenPoint.End(xlDown).End(xlToRight))
FindNew aiOldRows, aiNewRows, rngRaw
InvoiceSequence aiOldRows, rngRaw
Set rngInvoices = Range(ThisWorkbook.Worksheets("Invoices").Range("A2"), _
ThisWorkbook.Worksheets("Invoices").Range("A3").End(xlDown).End(xlToRight))
rngInvoices.Sort Key1:=Range("M2"), Order1:=xlAscending
End Sub
You'll notice that there are two other procedures (FindNew & InvoiceSequence) being called by this procedure. I don't think those have anything to do with the error, but I can provide the code for those if needed. Oh, and one other secondary question. To declare the ranges rngRaw & rngInvoices I pick the top-left cell of the data and then do:.....................
View 3 Replies
View Related
May 9, 2008
This piece of code runs perfectly on its own but when called at the end of another code it fails and I haven't a clue why. The reference wsTmp is dim'd globally and defined in the main component where it is simply - worksheets("somename")
Run-time error '1004':
The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.
Private Sub FinalSort()
wsTmp.Cells.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub
View 9 Replies
View Related
Dec 8, 2008
I have a vlookup table with will define the Job description based on the job number & cost code. The function is working fine but the problem come when i try to convert it into macro.
View 10 Replies
View Related
May 6, 2009
Is there a setting within excel that could cause a user to get a run-time error 1004? I have a workbook with a macro that adds additional sheets to the workbook when a button is clicked. The thing works fine on my machine and 3 or 4 other machines that I have had guys test it out, but I have a user that it will not work for. He is at a location about 3 hours away so I cant see exactly what he is doing, but he says after he clicks the button he gets a run-time error 1004 that says "Unable to set the PrintQuality property of the Page Setup class". I'm thinking that it is a setting within excel?
View 5 Replies
View Related
Aug 26, 2009
I am getting a run time error 1004 when i am running this macro. I got this macro to my earlier thread. As i am a beginner in a macro, I am unable to identify the problem. I have attached the workbook for the same
View 5 Replies
View Related
Sep 21, 2009
I have a macro, operating in my excel document. It works and does it's function. The only problem is every time this macro is running I get the run time error 1004, Method 'SaveAs' of object '_Workbook' failed. After clicking End in the error window it opens up a newly created sheet that I need. I don't need to save the file in the desktop, I need it just to open up like it does right now.
View 5 Replies
View Related
Jan 28, 2009
I am using the following code to enable users of a form to made make selections by adding a tick in one of the boxes by clicking on it. It works fine with thesheet unpotected but when I protect the sheet with cells E33:k33 unlocked i get Run Time Error '1004'
Unable to set the Name property of the font class.When I debug, Target.Font.Name = "Marlett" is highlighted.
View 4 Replies
View Related
Nov 19, 2008
i am running a macro thru vba (beiing called from a ms access module) and am getting a RuntTimeError 1004.
the code opens a workbook...then open a second workbook (which houses the macro) then activates the desired worksheet and call the Maco via the run command but errors out.
if i open the workbook and set focus on the desired sheet ....tool>macro>desired macro name it runs fine.
the line of code in the macro is: "ActiveCell.Offset(1, 0).Activate"
View 9 Replies
View Related
Feb 11, 2010
I've been trying to use the below code,
Sub combine()
'This will copy data from all sheets of the selected workbooks
'To a sheet named 'Data' in the sheet in which the macro is run from
Dim pasterow As String
mainsheetname = ActiveWorkbook.Name
MsgBox ("Please select spreadsheets to combine")
filestoopen = Application.GetOpenFilename(MultiSelect:=True)...
View 9 Replies
View Related
Jun 16, 2007
This code works fine in excel 2003 in excel 97 it gives error. Run-time error '1004'
Application-defined or object defined error.
Sub Extract_review()
With Sheets("Review")
. Range("C2") = InputBox("Scan ID Card")
.Range("A2") = ">=" & Date - 1
.Range("B2") = "<=" & Date
End With
Sheets("Punches").Range("A:H"). AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Review").Range("A1:H2"), CopyToRange:=Sheets("Review").Range("A5:H5"), Unique:=False..................
View 2 Replies
View Related
Dec 14, 2008
Below is the code in which the macro is showing error
Run time error '1004'
Application-defined (or) Object defined error
View 8 Replies
View Related
Aug 27, 2009
Basically my code is bombing at the red highlighted code. Error: "Run-time error '1004': Copy method of Worksheet class failed"
Sheet "Blank Form" is a hidden sheet. Can I alter the red section of code to work while being a hidden sheet? I tried a few variations but nothing seems to work..
View 5 Replies
View Related
Apr 6, 2012
My code is :
Public Const GRAPH_PutData_COL1 = "A"
Public Const GRAPH_PutData_COL2 = "B"
Range(GRAPH_PutData_COL1 & "1").Sort Key1:=Range(GRAPH_PutData_COL2 & "1"), _
Order1:=xlAscending, _
DataOption1:=xlSortTextAsNumbers
it is showing error, "sort method of range class failed error 1004"
Above error only showing when first record is empty...
View 2 Replies
View Related
May 9, 2007
i added a "Microsoft office spreadsheet 11.0 " object using additional control and used it in my form of Excel Macro. then i added some data from the combo box to the embedded excel object dispalyed. the form displays correctly and am even able to add data to cells.
but am not able to sort a column. gives me the error message "Sort method of range class failed"
have tried to make sure the sheet is active using the ".Activate" with the object name. this is the only solution available on the existing forums.
View 9 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
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
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 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 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
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
Jan 12, 2009
I have 4 command buttons (A-D) so that the users can update after inputting new or revised Data.
My problem is that if updated in order, button D Macro below stops at the first "Selection.AutoFilter..." as if it cannot turn on the Auto Filter.
It will run if I manually set the AutoFilter on the sheet.
I think, and as I am completely new to this please feel free to correct me, the reason that it stops is that on the Button B is a "AdvancedFilter" Macro.
View 9 Replies
View Related
Apr 5, 2007
I have a piece of VB code that works perfectly on my pc. The spreadsheet has been emailed to a partner (different company) and the code no longer works and they get the run-time error.
Private Sub cmdPrintandsave_Click()
Dim Company As String
Dim irow As Long
Dim MyDate
MyDate = Date
Dim wsD As Worksheet
Dim wsD1 As Worksheet
Set wsD = Worksheets("AppBDataSheet")
Set wsD1 = Worksheets("AppBPrintable")
Company = Application.OrganizationName
Application.ScreenUpdating = False................
View 9 Replies
View Related
Sep 24, 2007
I have inherited a spreadsheet and code that I don't understand much about. I have code
Range("B4").Select
ActiveSheet.PivotTables("Kh07Pivot").PivotFields("Specialty").Subtotals = Array(False, _
False, False, False, False, False, False, False, False, False, False, False, _
False, False, False, False, False, False, False, False, False, False, False, _
False, False, False, False, False, False, False)
which error (run time 1004) and says 'unable to set the SubTotals property of the PivotField class'. If I make the number of False's equal to 12, then it doesn't error. However I don't know whether it is having an adverse effect on the data?
View 9 Replies
View Related
Jul 15, 2008
Every time I run it I get a Run Time Error Message 1004 when trying to custome sort on D4.
Rng2.Select
With Selection
.Validation.Delete
.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlYes
.Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:="L,M,H,Contract", DataOption:=xlSortNormal
End With
View 9 Replies
View Related
Dec 26, 2008
Range(Cells(1, 1), Cells(257, 257)).Select
This gives the Run-time error '1004'
I searched these forums for any old posts to work around this but did not find any threads. If anybody knows a good thread about this, I would be greatful.
Basically I run some code to generate Startcolumn, StartRow, EndColumn, EndRow. If there is a limitation of 256, the code is basically useless. I don't know of a way to use the A1 range method whilst using the Range("A1:....")
View 9 Replies
View Related
Jun 30, 2009
I have been working on the following worksheet, which has A3-Z27 cells right now and will grow/shrink with time. The spreadsheet shown below is just a portion of the entire worksheet. My vbscript is below the worksheet. I have two worksheets PL Dbase and Waiting List. I am copying columns B thru G from Waiting List if Column J is = to "Yes". To the next available row in worksheet PL Dbase columns B thru G...These columns have the same headings on both worksheets. I am getting the following error, when I click the button I assigned the macro to: Run-Time Error 1004 Copy Method of Range Class Failed.
Waiting List .....
View 11 Replies
View Related
Jan 14, 2010
Sub paste()
' paste Macro
' Macro recorded 2/20/2009 by lawryad
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.paste
ActiveSheet.paste
Application.CutCopyMode = False
ActiveSheet.Range("p1").Copy
ActiveSheet.Range("a1").Select
End Sub
Now everything works good with this code unless there is nothing copied to paste. In that case it will go to a "run-time error'1004' " I would like instead for a msg box to appear saying "You must copy required data first"
View 9 Replies
View Related
May 20, 2006
I want to paste data from a table without the header row from one workbook (Test1) into another (Test2), from Test2. Test1 is closed before this operation starts. I'm using the following
Sub Macro2()
Dim myTable As Range
Set myTable = Range("A1").CurrentRegion
Workbooks.Open "C:ExcelTest1"
myTable.Offset(1).Resize(myTable.Rows.Count - 1).Select
Selection.Copy
Workbooks("Test2").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
End Sub
When I have both workbooks open, comment out the line that opens Test1 and step through the code, it works fine but when I close workbook Test1, and run the macro, it generates the following error message: Run-time error 1004 Application defined or object defined error. When I click 'Debug' it has highlighted the row that has the offset instruction so there is no trouble with opening the workbook Test1.
View 6 Replies
View Related
Jan 8, 2007
im having problems calling the hlookup function in vba. Here is the code im using. I get run time error '1004'. Ive tried both setting the range, and manually inputting it. The User Defined Function takes no inputs, but is designed to get the value based on the relative position of the cell in the spreadsheet.
Option Explicit
Option Base 1
Function lastrow()
Sheets("template").Select
Range("A65536").Select
Selection.End(xlUp).Select
lastrow = ActiveCell.Row
End Function
Function summary1()...............
View 6 Replies
View Related
