Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Error
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Error
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,1 01: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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Error
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" wrote in message ... 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,1 01: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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Error
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Error
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" wrote in message ... 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Error
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. Thanks again Roger "GS" wrote in message ... 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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Macro error please help | Excel Discussion (Misc queries) | |||
Macro error : Application-defined or object-defined error | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |