Macro Stops for no apparent reason

[Brendan63]

Hi, our system generates reports as CSV files. I have a macro in a spreadsheet to open these, copy them and paste them into this sheet, (it then goes on and does other stuff.)

The macro just stops after opening the first CSV file, but if i step through, it works fine.

It even works right to the end if I step through to the copy line then run the macro.


Workbooks.Open Filename:= _ "C:\Reports\ Report1_Oct-2017.CSV"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("Reports.xlsm").Activate
Sheets("CSV Report 1").Select
ActiveSheet.Paste
Range("A4").Select
Workbooks.Open Filename:= _
"C:\Reports\ Report2_Oct-2017.CSV"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Reports.xlsm").Activate
Sheets("CSV 2").Select
ActiveSheet.Paste
Range("A4").Select

 

[BrianJN1]

Hi, our system generates reports as CSV files. I have a macro in a spreadsheet to open these, copy them and paste them into this sheet, (it then goes on and does other stuff.)

The macro just stops after opening the first CSV file, but if i step through, it works fine.

It even works right to the end if I step through to the copy line then run the macro.


Workbooks.Open Filename:= _ "C:\Reports\ Report1_Oct-2017.CSV"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("Reports.xlsm").Activate
Sheets("CSV Report 1").Select
ActiveSheet.Paste
Range("A4").Select
Workbooks.Open Filename:= _
"C:\Reports\ Report2_Oct-2017.CSV"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Reports.xlsm").Activate
Sheets("CSV 2").Select
ActiveSheet.Paste
Range("A4").Select

The first thing I'd suggest is to check for consistency in your file naming. It would appear that you have a potential typo, see the red highlights.

How many reports would this macro have to handle? And then, would you have to edit this macro for to prepare reports for next month?
If so, report back on the above comment and advise if you need assistance to follow up on my second query.

 

[Fluff]

How about

Workbooks.Open Filename:="C:\Reports\ Report1_Oct-2017.CSV"
ActiveSheet.UsedRange.Copy
Windows("Reports.xlsm").Sheets("CSV Report 1").Paste
Range("A4").Select
Workbooks.Open Filename:= _
"C:\Reports\ Report2_Oct-2017.CSV"
ActiveSheet.UsedRange.Copy
Windows("Reports.xlsm").Sheets("CSV 2").Paste
Range("A4").Select

 

[RoryA]

How do you run the macro initially - anything involving the Shift key?

 

[Brendan63]

Hi Brian, the red highlighted are the tab names, which are correct, although I probably should rename to make it less messy. There are 4 reports in all, but the macro stops after opening the first.
I do a Ctrl H each month to change the months.

 

[Brendan63]

How do you run the macro initially - anything involving the Shift key?

Yes I have Ctrl Shift S to run the macro

 

[Brendan63]

How about

Workbooks.Open Filename:="C:\Reports\ Report1_Oct-2017.CSV"
ActiveSheet.UsedRange.Copy
Windows("Reports.xlsm").Sheets("CSV Report 1").Paste
Range("A4").Select
Workbooks.Open Filename:= _
"C:\Reports\ Report2_Oct-2017.CSV"
ActiveSheet.UsedRange.Copy
Windows("Reports.xlsm").Sheets("CSV 2").Paste
Range("A4").Select

Thanks Fluff, but no difference, although the line "ActiveSheet.UsedRange.Copy" is much better than my 3 lines.

 

[RoryA]

Try using a key combo that doesn’t involve Shift and see if that fixes it.

 

[Brendan63]

Yes I have Ctrl Shift S to run the macro

I changed it to Ctrl Shift Z and it works!!! What the ???

Thanks everyone