Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel macro protect problem | Excel Discussion (Misc queries) | |||
Excel 2007 Macro Problem | Charts and Charting in Excel | |||
Excel-Macro Problem | Excel Discussion (Misc queries) | |||
Macro problem in Excel | Excel Discussion (Misc queries) | |||
problem with macro on Excel | Excel Worksheet Functions |