macro stops midstream

[dvt]

I have written a simple macro that opens a .csv file, copies the data
from the file and pastes them in another worksheet, then closes the .csv
file. The macro works perfectly when I step through the code one step at
a time. But when I try to run it normally, it stops after I open the
..csv file. I get no error messages, it just quits with my cursor in cell
A1 of the .csv file. The code is below with a few comment lines that
show you where the error seems to happen.

Thanks for any help you can give me.

Dave
dvt at psu dot edu

Sub get_raw_data()
CurrentFile = Windows(1).Caption
' filenames follow the format c:\testNNN.csv
DirName = "c:\"
FileNum = InputBox("Enter the data file number")
Filename = "test" & FileNum & ".csv"
Workbooks.Open Filename:=DirName & Filename
' macro works up to this point, then stops
' select all the data in the sheet and copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
' Open the original file and paste the data into that file
Windows("filename.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' close the .csv file
Windows(Filename).Activate
ActiveWorkbook.Close
End Sub

 

[Tom Ogilvy]

Try using it with the line I changed.

Sub get_raw_data()
CurrentFile = Windows(1).Caption
' filenames follow the format c:\testNNN.csv
DirName = "c:\"
FileNum = InputBox("Enter the data file number")
Filename = "test" & FileNum & ".csv"
Workbooks.Open Filename:=DirName & Filename
' macro works up to this point, then stops
' select all the data in the sheet and copy
Activesheet.UsedRange.Select '<== new line
Selection.Copy
' Open the original file and paste the data into that file
Windows("filename.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' close the .csv file
Windows(Filename).Activate
ActiveWorkbook.Close
End Sub

 

[dvt]

Try using it with the line I changed.

That works. Thanks a ton, Tom!

I don't want to impose on you, but if you could explain why that worked
I'd be even more grateful. You replaced this line:

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

with this:

Activesheet.UsedRange.Select

The macro used to run using the "step into" in debug mode, but wouldn't
run straight through. Now it runs just fine. I don't understand.

Dave
dvt at psu dot edu

 

[dvt]

That works. Thanks a ton, Tom!

Correction: it works when I use Alt-F8 to select and run the macro. It
works with either version of the code (my original or Tom's modified)
When I use the keyboard shortcut assigned to the macro, it still stops
at the designated line. I had not noticed this before; I was always
trying to use the shortcut key.

I'll finish this project using the Alt-F8 technique. There is no urgency
to this problem now that I've realized that this works.

Dave
dvt at psu dot edu