Macro error

G

garyd1234

Board Regular
Joined
Apr 17, 2003
Messages
103
Hi,
I getting an error while running a macro and I am lost. What the macro does is to reorder some columns in a spreadsheet of data that I imported from a flat file. Then it runs a sort and a filter that puts some column heading with a pull down box so it can filter out data by department. I can do the macro ok by hand and it works fine. I can record the macro just fine. When I play it back it stops part way through the ordering of the columns and says: Run-time error 1004

The selection is not valid. There are several possibile reasons:

Copy and paste areas cannot overlap unless they're the same size and shape.

If you're using the Create command on the Name submenu of the Insert menu, the row or column containing the proposed name ...

I read the help but it didn't help.

Any ideas? Thanks for any help

Gary
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Gary

Not really without seeing the code.:)
 
Upvote 0
here is the code for the macro error problem

Code: 
Sub LoadProcess()
'
' LoadProcess Macro
' Macro recorded 2007-04-23 by Gary Deromedi
'

'
    Cells.Select
    Selection.ClearContents
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;D:\CITY_OF_CANTON 3-5-07.nrg", Destination:=Range("A1"))
        .Name = "CITY_OF_CANTON 3-5-07"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 5
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
    Rows("1:2").Select
    Selection.Insert Shift:=xlDown
    Application.Run "'Phone Bill.xls'!Titles"
    Range("D2:Z2").Select
    Range("Z2").Activate
    Selection.Cut Destination:=Range("C2:Y2")
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:N").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:K").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.NumberFormat = "General"
    Columns("E:E").Select
    Selection.Cut

//////dies right around here
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Columns("I:J").Select
    Selection.Cut
    Columns("I:J").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.NumberFormat = "$#,##0.00"
    Selection.ColumnWidth = 8.11
    Range("A1:I3619").Select
    Selection.Sort Key1:=Range("I2"), Order1:=xlAscending, Key2:=Range("C2") _
        , Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
    Selection.AutoFilter
    ActiveWindow.ScrollRow = 3574
    ActiveWindow.ScrollRow = 3687
    ActiveWindow.ScrollRow = 3627
    ActiveWindow.ScrollRow = 3554
    ActiveWindow.SmallScroll Down:=51
    Range("H3620").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-3618]C:R[-1]C)"
    ActiveWindow.ScrollRow = 1
    Range("I2").Select
End Sub

EDIT: Added Code Tags - Moderator
 
Upvote 0
Your error is most likely the result of selecting, which is generally unnecessary, when you see Select followed by selection you can usually eliminate both:

Code: 
    Range("D2:Z2").Cut Destination:=Range("C2") 
    Rows("1:1").Delete Shift:=xlUp

Also note that screen navigation (ActiveWindow.ScrollRow = 3574 ) can be eliminated altogether.

HTH,

Smitty
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Application.Run returning error "400"
Replies
9
Views
574
Peter_SSs
Peter_SSs
M
  • Question
OLE Error / Excel Not Responding Error
Replies
0
Views
272
MrHydrant1857
M
B
  • Question
Autofilter Returns No Results to Set Range
Replies
1
Views
259
davesexcel
davesexcel
P
  • Question
PivotTable Error 1004
Replies
1
Views
195
DanteAmor
DanteAmor
K
  • Question
Macro will work with multiple rows but sends a Run-time Error '1004' if only one row is filtered
Replies
2
Views
257
Skyybot
Skyybot

Forum statistics

Threads
1,214,983
Messages
6,122,583
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top