Excel XP macro problem
 

Hi all,

Apologies if this is the wrong forum & for the crossposting.

I have recently started having problems with code in excel XP which I use at
work.

For example, the following brief code opens up to 14 files in turn, extracts
2 lines of data and puts them in the right place in a summary workbook:

Sub import_stats_data()
'
Dim pds(14) As Variant
Sheets("Checksheet").Select
For a = 1 To 14
pds(a) = Cells(a, 1).Value 'the 4-weekly period number
Next a
For b = 1 To 14
If Cells(b, 1).Value = "" Then Exit Sub
Next b
numperiods = b - 1
'
file_place = "D:\work files\"
For c = 1 To numperiods
file_name = "stat" & pds(c) & ".xls"
Workbooks.Open (file_place & file_name)
Sheets("Summary").Select
Range("C19:Z19").Select
Selection.Copy
ThisWorkbook.Activate
Sheets("Data").Select
Cells(c + 2, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Workbooks(file_name).Activate
Sheets("Summary").Select
Range("C40:Z40").Select
Selection.Copy
ThisWorkbook.Activate
Sheets("Data").Select
Cells(c + 16, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Workbooks(file_name).Close savechanges:=False
Next c
'
End Sub

This code does not run in my copy of XP - but runs fine in xl97 or 2000,
both of which I have at home.
This is not the first time this has happened and is getting very
frustrating.

Any help would be very gratefully received! Thanks in advance.

--
Paul
You may need to replace the dot with a dot to reply

Excel XP macro problem
 

Paul,

For b = 1 To 14
If Cells(b, 1).Value = "" Then Exit Sub
Next b
numperiods = b - 1

numperiods will ALWAYS be 14 unless you've exited the Sub, when it's value
won't matter.
I think you mean Exit For in the line above.


HTH
Henry


"Paul Reeve" wrote in message
...
Hi all,

Apologies if this is the wrong forum & for the crossposting.

I have recently started having problems with code in excel XP which I use
at
work.

For example, the following brief code opens up to 14 files in turn,
extracts
2 lines of data and puts them in the right place in a summary workbook:

Sub import_stats_data()
'
Dim pds(14) As Variant
Sheets("Checksheet").Select
For a = 1 To 14
pds(a) = Cells(a, 1).Value 'the 4-weekly period number
Next a
For b = 1 To 14
If Cells(b, 1).Value = "" Then Exit Sub
Next b
numperiods = b - 1
'
file_place = "D:\work files\"
For c = 1 To numperiods
file_name = "stat" & pds(c) & ".xls"
Workbooks.Open (file_place & file_name)
Sheets("Summary").Select
Range("C19:Z19").Select
Selection.Copy
ThisWorkbook.Activate
Sheets("Data").Select
Cells(c + 2, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Workbooks(file_name).Activate
Sheets("Summary").Select
Range("C40:Z40").Select
Selection.Copy
ThisWorkbook.Activate
Sheets("Data").Select
Cells(c + 16, 3).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Workbooks(file_name).Close savechanges:=False
Next c
'
End Sub

This code does not run in my copy of XP - but runs fine in xl97 or 2000,
both of which I have at home.
This is not the first time this has happened and is getting very
frustrating.

Any help would be very gratefully received! Thanks in advance.

--
Paul
You may need to replace the dot with a dot to reply