Macro Error

Roger Dodger · Oct 5, 2011

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.

Don Guillett · Oct 5, 2011

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??? fill>copy>clear>copy>>>>>
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

GS · Oct 5, 2011

Don Guillett pretended :

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

Possible alternative...

Const sRowsToDelete As String = _
"401:406,351:356,301:306,251:256,201:206,151:156,101:106,51:56,1:6"
'use if Resize is NOT constant

Const sRowsToDelete As String = "401,351,301,251,201,151,101,51,1"
'use if Resize is constant

Dim vRowsToDelete
vRowsToDelete = Split(sRowsToDelete, ",")
For i = LBound(vRowsToDelete) To UBound(vRowsToDelete)
Rows(vRowsToDelete(i)).Delete
'Rows(vRowsToDelete(i)).Resize(5).Delete
Next 'i

Roger Dodger · Oct 7, 2011

Its like your talking another language.

I'll give it a try when I get back to work on Monday.

Thanks for your help

Roger

GS · Oct 7, 2011

Roger Dodger explained on 10/7/2011 :

Its like your talking another language.

I'll give it a try when I get back to work on Monday.

Thanks for your help

You're welcome! I assure you the language is VB[A]! I also hide/unhide
rows/cols the same way...

Const sColsToHide As String...
Const sColsToUnhide As String...
Const sRowsToHide As String...
Const sRowsToUnhide As String...

vColsToHide = Split(sColsToHide, ",")
vColsToUnhide = Split(sColsToUnhide, ",")
vRowsToHide = Split(sRowsToHide, ",")
vRowsToUnhide = Split(sRowsToUnhide, ",")

This works for fixed cols/rows. For unknown cols/rows I build the
strings at runtime.

Might help if you post the actual code you have now.

Roger Dodger · Oct 11, 2011

Garry Mate,

All of your cleverness is wasted on me. Honest I don't have a clue when it
comes to VBA.

I pasted the text in and still couldn't get it to work.

I think that I will have to buy a book and start way back at the beginning.

I re did the work using vlookup and other formulas and for the moment it
works. Unfortunately it is much slower.

Has VBA changed much since excel 2003?. That is all we have at work.

Thanks for you help, any book or web recommendations for learning VBA are
most welcome.

Thanks again

Roger

GS said:
Roger Dodger explained on 10/7/2011 :

Its like your talking another language.

I'll give it a try when I get back to work on Monday.

Thanks for your help

Click to expand...

You're welcome! I assure you the language is VB[A]! I also hide/unhide
rows/cols the same way...

Const sColsToHide As String...
Const sColsToUnhide As String...
Const sRowsToHide As String...
Const sRowsToUnhide As String...

vColsToHide = Split(sColsToHide, ",")
vColsToUnhide = Split(sColsToUnhide, ",")
vRowsToHide = Split(sRowsToHide, ",")
vRowsToUnhide = Split(sRowsToUnhide, ",")

This works for fixed cols/rows. For unknown cols/rows I build the strings
at runtime.

Might help if you post the actual code you have now.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS · Oct 11, 2011

Roger Dodger explained :

Garry Mate,

All of your cleverness is wasted on me. Honest I don't have a clue when it
comes to VBA.

Maybe I could review your file if it contains clearly what you want and
an example of the expected results.

gesansomATnetscapeDOTnet

I pasted the text in and still couldn't get it to work.

Pasted it into what/where?

I think that I will have to buy a book and start way back at the beginning.

I re did the work using vlookup and other formulas and for the moment it
works. Unfortunately it is much slower.

Has VBA changed much since excel 2003?. That is all we have at work.

It hasn't changed (VBA6.x)since 2003 for x32 Office. VBA for x64 Office
is new (VBA7).

Thanks for you help, any book or web recommendations for learning VBA are
most welcome.

Anything by John Walkenbach is a good place to start. Also, google
Excel VBA and see what links pop up for tips/samples from Excel MVP
websites.

GS said:
Thanks again

Roger

GS said:

Roger Dodger explained on 10/7/2011 :

Its like your talking another language.

I'll give it a try when I get back to work on Monday.

Thanks for your help

Click to expand...

You're welcome! I assure you the language is VB[A]! I also hide/unhide
rows/cols the same way...

Const sColsToHide As String...
Const sColsToUnhide As String...
Const sRowsToHide As String...
Const sRowsToUnhide As String...

vColsToHide = Split(sColsToHide, ",")
vColsToUnhide = Split(sColsToUnhide, ",")
vRowsToHide = Split(sRowsToHide, ",")
vRowsToUnhide = Split(sRowsToUnhide, ",")

This works for fixed cols/rows. For unknown cols/rows I build the strings
at runtime.

Might help if you post the actual code you have now.

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Click to expand...