Thread: Macro Error
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Don Guillett[_2_] Don Guillett[_2_] is offline
external usenet poster
  
Posts: 1,522
Default Macro Error
You do not need or want the scrolls and selections

Sub Load_Reports()
Workbooks.OpenText FileName:="U:\BruckWRK\DYE905.LST",
Origin:=xlWindows, StartRow
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(5, 9), Array( _
10, 1), Array(39, 9), Array(41, 1), Array(54, 1), Array(67,
1), Array(80, 1), Array(92, 1), _
Array(104, 1), Array(116, 9))
'DELETING FROM THE TOP UP MAY NOT??? GIVE THE DESIRED RESULTS.
Rows("1:6").Delete
Rows("51:56").Delete
Rows("101:106").Delete
Rows("151:156").Delete
Rows("201:206").Delete
Rows("251:256").Delete
Rows("301:306").Delete
Rows("351:356").Delete
Rows("401:406").Delete
'YOU MAY WANT SOMETHING LIKE this instead. Check CAREFULLY
'for i = 401 to 1 step -50
'rows(i).resize(5).delete
'next i


Columns("D:E").Insert Shift:=xlToRight
Range("A1:J600").Copy
Windows("Dyehouse Order Schedule.xls").Activate
Range("B6").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

Workbooks.OpenText FileName:="U:\BruckWRK\xjob1110.csv",
Origin:=xlWindows, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(5, 9), Array( _
10, 1), Array(34, 9), Array(67, 1), Array(78, 9), Array(81,
1), Array(92, 1), Array(103, 1), _
Array(114, 9))
Rows("1:2").Delete
'?????? what to do here??? fillcopyclearcopy
Range("A1:F1").AutoFill Destination:=Range("A1:a600"),
Type:=xlFillDefault
' Range("A1:F600").Select
' Range("A600:F600").Select
'Selection.ClearContents

Range("A1:F599").Copy
Windows("Dyehouse Order Schedule.xls").Activate
Sheets("Data").Range("A1").PasteSpecial Paste:=xlValues

'==========
' Sheets("Dye Schedule").Select
' Range("B6").Select



On Oct 5, 12:36*am, "Roger Dodger" wrote:
Hi,

I have the following macro that I inherited. It used to work well but stopped working a couple of years ago. I would like to get it working again but I can't fix the error. Can anyone please help. My company changed its drives recently and I think that I have fixed that part but with no real idea in VBA I am out of my depth.

Sub Load_Reports()
'
' Load_Reports Macro
' Macro recorded 9/11/01 A.Scholte
'

'
* * Workbooks.OpenText FileName:="U:\BruckWRK\DYE905.LST", Origin:=xlWindows, StartRow _
* * * * :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 9), Array( _
* * * * 10, 1), Array(39, 9), Array(41, 1), Array(54, 1), Array(67, 1), Array(80, 1), Array(92, 1), _
* * * * Array(104, 1), Array(116, 9))
* * Rows("1:6").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 2
* * ActiveWindow.ScrollRow = 3
* * ActiveWindow.ScrollRow = 8
* * ActiveWindow.ScrollRow = 11
* * ActiveWindow.ScrollRow = 16
* * ActiveWindow.ScrollRow = 21
* * ActiveWindow.ScrollRow = 22
* * ActiveWindow.ScrollRow = 25
* * ActiveWindow.ScrollRow = 30
* * ActiveWindow.ScrollRow = 33
* * ActiveWindow.ScrollRow = 34
* * ActiveWindow.ScrollRow = 36
* * ActiveWindow.ScrollRow = 40
* * ActiveWindow.ScrollRow = 42
* * Rows("51:56").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 44
* * ActiveWindow.ScrollRow = 45
* * ActiveWindow.ScrollRow = 46
* * ActiveWindow.ScrollRow = 47
* * ActiveWindow.ScrollRow = 48
* * ActiveWindow.ScrollRow = 50
* * ActiveWindow.ScrollRow = 52
* * ActiveWindow.ScrollRow = 54
* * ActiveWindow.ScrollRow = 55
* * ActiveWindow.ScrollRow = 58
* * ActiveWindow.ScrollRow = 59
* * ActiveWindow.ScrollRow = 60
* * ActiveWindow.ScrollRow = 62
* * ActiveWindow.ScrollRow = 64
* * ActiveWindow.ScrollRow = 65
* * ActiveWindow.ScrollRow = 66
* * ActiveWindow.ScrollRow = 68
* * ActiveWindow.ScrollRow = 69
* * ActiveWindow.ScrollRow = 70
* * ActiveWindow.ScrollRow = 71
* * ActiveWindow.ScrollRow = 73
* * ActiveWindow.ScrollRow = 74
* * ActiveWindow.ScrollRow = 75
* * ActiveWindow.ScrollRow = 76
* * ActiveWindow.ScrollRow = 77
* * ActiveWindow.ScrollRow = 78
* * ActiveWindow.ScrollRow = 79
* * ActiveWindow.ScrollRow = 80
* * ActiveWindow.ScrollRow = 82
* * ActiveWindow.ScrollRow = 83
* * ActiveWindow.ScrollRow = 84
* * Rows("101:106").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 87
* * ActiveWindow.ScrollRow = 89
* * ActiveWindow.ScrollRow = 93
* * ActiveWindow.ScrollRow = 97
* * ActiveWindow.ScrollRow = 100
* * ActiveWindow.ScrollRow = 102
* * ActiveWindow.ScrollRow = 105
* * ActiveWindow.ScrollRow = 107
* * ActiveWindow.ScrollRow = 110
* * ActiveWindow.ScrollRow = 111
* * ActiveWindow.ScrollRow = 113
* * ActiveWindow.ScrollRow = 115
* * ActiveWindow.ScrollRow = 118
* * ActiveWindow.ScrollRow = 119
* * ActiveWindow.ScrollRow = 122
* * ActiveWindow.ScrollRow = 124
* * ActiveWindow.ScrollRow = 127
* * ActiveWindow.ScrollRow = 129
* * ActiveWindow.ScrollRow = 130
* * ActiveWindow.ScrollRow = 131
* * ActiveWindow.ScrollRow = 132
* * ActiveWindow.ScrollRow = 133
* * Rows("151:156").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 137
* * ActiveWindow.ScrollRow = 143
* * ActiveWindow.ScrollRow = 148
* * ActiveWindow.ScrollRow = 160
* * ActiveWindow.ScrollRow = 162
* * ActiveWindow.ScrollRow = 163
* * ActiveWindow.ScrollRow = 165
* * ActiveWindow.ScrollRow = 166
* * ActiveWindow.ScrollRow = 167
* * ActiveWindow.ScrollRow = 170
* * ActiveWindow.ScrollRow = 173
* * ActiveWindow.ScrollRow = 175
* * ActiveWindow.ScrollRow = 176
* * ActiveWindow.ScrollRow = 178
* * ActiveWindow.ScrollRow = 179
* * ActiveWindow.ScrollRow = 181
* * ActiveWindow.ScrollRow = 182
* * ActiveWindow.ScrollRow = 183
* * ActiveWindow.ScrollRow = 184
* * Rows("201:206").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 187
* * ActiveWindow.ScrollRow = 189
* * ActiveWindow.ScrollRow = 194
* * ActiveWindow.ScrollRow = 199
* * ActiveWindow.ScrollRow = 204
* * ActiveWindow.ScrollRow = 207
* * ActiveWindow.ScrollRow = 209
* * ActiveWindow.ScrollRow = 210
* * ActiveWindow.ScrollRow = 211
* * ActiveWindow.ScrollRow = 213
* * ActiveWindow.ScrollRow = 218
* * ActiveWindow.ScrollRow = 221
* * ActiveWindow.ScrollRow = 222
* * ActiveWindow.ScrollRow = 223
* * ActiveWindow.ScrollRow = 226
* * ActiveWindow.ScrollRow = 228
* * ActiveWindow.ScrollRow = 230
* * ActiveWindow.ScrollRow = 231
* * ActiveWindow.ScrollRow = 232
* * ActiveWindow.ScrollRow = 234
* * ActiveWindow.ScrollRow = 235
* * Rows("251:256").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 238
* * ActiveWindow.ScrollRow = 240
* * ActiveWindow.ScrollRow = 245
* * ActiveWindow.ScrollRow = 250
* * ActiveWindow.ScrollRow = 255
* * ActiveWindow.ScrollRow = 257
* * ActiveWindow.ScrollRow = 258
* * ActiveWindow.ScrollRow = 259
* * ActiveWindow.ScrollRow = 262
* * ActiveWindow.ScrollRow = 264
* * ActiveWindow.ScrollRow = 267
* * ActiveWindow.ScrollRow = 270
* * ActiveWindow.ScrollRow = 275
* * ActiveWindow.ScrollRow = 277
* * ActiveWindow.ScrollRow = 280
* * ActiveWindow.ScrollRow = 284
* * ActiveWindow.ScrollRow = 286
* * ActiveWindow.ScrollRow = 287
* * ActiveWindow.ScrollRow = 288
* * ActiveWindow.ScrollRow = 287
* * Rows("301:306").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 290
* * ActiveWindow.ScrollRow = 292
* * ActiveWindow.ScrollRow = 296
* * ActiveWindow.ScrollRow = 299
* * ActiveWindow.ScrollRow = 302
* * ActiveWindow.ScrollRow = 304
* * ActiveWindow.ScrollRow = 308
* * ActiveWindow.ScrollRow = 310
* * ActiveWindow.ScrollRow = 313
* * ActiveWindow.ScrollRow = 318
* * ActiveWindow.ScrollRow = 321
* * ActiveWindow.ScrollRow = 323
* * ActiveWindow.ScrollRow = 325
* * ActiveWindow.ScrollRow = 326
* * ActiveWindow.ScrollRow = 331
* * ActiveWindow.ScrollRow = 332
* * ActiveWindow.ScrollRow = 333
* * ActiveWindow.ScrollRow = 335
* * ActiveWindow.ScrollRow = 337
* * ActiveWindow.ScrollRow = 336
* * Rows("351:356").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 346
* * ActiveWindow.ScrollRow = 354
* * ActiveWindow.ScrollRow = 358
* * ActiveWindow.ScrollRow = 360
* * ActiveWindow.ScrollRow = 363
* * ActiveWindow.ScrollRow = 364
* * ActiveWindow.ScrollRow = 366
* * ActiveWindow.ScrollRow = 369
* * ActiveWindow.ScrollRow = 370
* * ActiveWindow.ScrollRow = 374
* * ActiveWindow.ScrollRow = 378
* * ActiveWindow.ScrollRow = 380
* * ActiveWindow.ScrollRow = 384
* * ActiveWindow.ScrollRow = 386
* * ActiveWindow.ScrollRow = 387
* * ActiveWindow.ScrollRow = 389
* * ActiveWindow.ScrollRow = 390
* * ActiveWindow.ScrollRow = 392
* * ActiveWindow.ScrollRow = 391
* * Rows("401:406").Select
* * Selection.Delete Shift:=xlUp
* * ActiveWindow.ScrollRow = 394
* * ActiveWindow.ScrollRow = 402
* * ActiveWindow.ScrollRow = 413
* * ActiveWindow.ScrollRow = 415
* * ActiveWindow.ScrollRow = 417
* * ActiveWindow.ScrollRow = 418
* * ActiveWindow.ScrollRow = 419
* * ActiveWindow.ScrollRow = 420
* * ActiveWindow.ScrollRow = 421
* * ActiveWindow.ScrollRow = 423
* * ActiveWindow.ScrollRow = 424
* * ActiveWindow.ScrollRow = 425
* * ActiveWindow.ScrollRow = 424
* * ActiveWindow.ScrollRow = 423
* * ActiveWindow.ScrollRow = 422
* * ActiveWindow.ScrollRow = 421
* * ActiveWindow.ScrollRow = 420
* * ActiveWindow.ScrollRow = 418
* * ActiveWindow.ScrollRow = 417
* * ActiveWindow.ScrollRow = 416
* * ActiveWindow.ScrollRow = 415
* * ActiveWindow.ScrollRow = 414
* * ActiveWindow.ScrollRow = 412
* * ActiveWindow.ScrollRow = 411
* * ActiveWindow.ScrollRow = 410
* * ActiveWindow.ScrollRow = 409
* * ActiveWindow.ScrollRow = 408
* * ActiveWindow.ScrollRow = 407
* * ActiveWindow.ScrollRow = 406
* * ActiveWindow.ScrollRow = 405
* * ActiveWindow.ScrollRow = 404
* * ActiveWindow.ScrollRow = 402
* * ActiveWindow.ScrollRow = 401
* * ActiveWindow.ScrollRow = 400
* * ActiveWindow.ScrollRow = 399
* * ActiveWindow.ScrollRow = 397
* * ActiveWindow.ScrollRow = 396
* * ActiveWindow.ScrollRow = 394
* * ActiveWindow.ScrollRow = 392
* * ActiveWindow.ScrollRow = 389
* * ActiveWindow.ScrollRow = 387
* * ActiveWindow.ScrollRow = 385
* * ActiveWindow.ScrollRow = 379
* * ActiveWindow.ScrollRow = 369
* * ActiveWindow.ScrollRow = 358
* * ActiveWindow.ScrollRow = 353
* * ActiveWindow.ScrollRow = 345
* * ActiveWindow.ScrollRow = 333
* * ActiveWindow.ScrollRow = 327
* * ActiveWindow.ScrollRow = 325
* * ActiveWindow.ScrollRow = 307
* * ActiveWindow.ScrollRow = 302
* * ActiveWindow.ScrollRow = 299
* * ActiveWindow.ScrollRow = 292
* * ActiveWindow.ScrollRow = 289
* * ActiveWindow.ScrollRow = 284
* * ActiveWindow.ScrollRow = 281
* * ActiveWindow.ScrollRow = 276
* * ActiveWindow.ScrollRow = 273
* * ActiveWindow.ScrollRow = 262
* * ActiveWindow.ScrollRow = 258
* * ActiveWindow.ScrollRow = 253
* * ActiveWindow.ScrollRow = 249
* * ActiveWindow.ScrollRow = 247
* * ActiveWindow.ScrollRow = 240
* * ActiveWindow.ScrollRow = 237
* * ActiveWindow.ScrollRow = 230
* * ActiveWindow.ScrollRow = 227
* * ActiveWindow.ScrollRow = 225
* * ActiveWindow.ScrollRow = 220
* * ActiveWindow.ScrollRow = 216
* * ActiveWindow.ScrollRow = 212
* * ActiveWindow.ScrollRow = 203
* * ActiveWindow.ScrollRow = 198
* * ActiveWindow.ScrollRow = 197
* * ActiveWindow.ScrollRow = 193
* * ActiveWindow.ScrollRow = 191
* * ActiveWindow.ScrollRow = 186
* * ActiveWindow.ScrollRow = 183
* * ActiveWindow.ScrollRow = 172
* * ActiveWindow.ScrollRow = 165
* * ActiveWindow.ScrollRow = 163
* * ActiveWindow.ScrollRow = 160
* * ActiveWindow.ScrollRow = 156
* * ActiveWindow.ScrollRow = 150
* * ActiveWindow.ScrollRow = 147
* * ActiveWindow.ScrollRow = 138
* * ActiveWindow.ScrollRow = 82
* * ActiveWindow.ScrollRow = 49
* * ActiveWindow.ScrollRow = 1
* * Columns("D:E").Select
* * Selection.Insert Shift:=xlToRight
* * Range("A1:J600").Select
* * Selection.Copy
* * Windows("Dyehouse Order Schedule.xls").Activate
* * Range("B6").Select
* * Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
* * * * False, Transpose:=False
* * Range("B6").Select
* * Application.CutCopyMode = False
* * Workbooks.OpenText FileName:="U:\BruckWRK\xjob1110.csv", Origin:=xlWindows, StartRow _
* * * * :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 9), Array( _
* * * * 10, 1), Array(34, 9), Array(67, 1), Array(78, 9), Array(81, 1), Array(92, 1), Array(103, 1), _
* * * * Array(114, 9))
* * Rows("1:2").Select
* * Selection.Delete Shift:=xlUp
* * Range("A1:F599").Select

* Error here == Selection.AutoFill Destination:=Range("A1:F600"), Type:=xlFillDefault

* * Range("A1:F600").Select
* * Range("A600:F600").Select
* * Selection.ClearContents
* * Range("A1:F599").Select
* * Selection.Copy
* * Windows("Dyehouse Order Schedule.xls").Activate
* * Sheets("Data").Select
* * Range("A1").Select
* * Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
* * * * False, Transpose:=False
* * Sheets("Dye Schedule").Select
* * Range("B6").Select

Thanks in advance
Roger.

Reply With QuoteReply With Quote