macro crashes

[daveyc18]

it's a simple macro that simply opens a workbook then copy and pastes to the spreadsheet

if i just run it, it often crashes...but if I run it step by step till the end, it's fine.

it's driving me insane.

 

[NdNoviceHlp]

Hi daveyc18. U will need to post the macro if U want help. One frustrating problem that can occur is that the clipboard crashes. Avoiding copy and paste is best and quickest ie. load your info into an array(s) and then transfer it using the array. HTH. Dave

 

[Yongle]

Are you simply moaning or do you have a question?

Post your macro (click on # icon above post window and paste between the tags) if you want some help

[CODE ] paste code here [/CODE]

 

[daveyc18]

'initialize dates

start_dates






   Sheets("menu").Range("E14:F14").Interior.Color = 65535


Application.DisplayAlerts = False







Sheets("cap").Select


finalrow = Cells(Rows.Count, "R").End(xlUp).Row
Range("a1:r" & finalrow).Clear










Path = "\\a\b\c\" & p_show_Year & p_show_Month & p_show_Day & "\"


fname = "cap_" & p_show_Year & p_show_Month & p_show_Day & ".csv"






Set cap_file = Workbooks.Open(Path & fname)


finalrow = Cells(Rows.Count, "R").End(xlUp).Row






Range("a1:r" & finalrow).Copy






ThisWorkbook.Sheets("cap").Activate



Range("a1").PasteSpecial xlPasteValues




Range("a1").Select








'close


cap_file.Close False






Sheets("menu").Select


ActiveWorkbook.Save

 

[Yongle]

Please post the WHOLE of the macro including declaration of variables

 

[daveyc18]

'initialize dates

start_dates






   Sheets("menu").Range("E14:F14").Interior.Color = 65535


Application.DisplayAlerts = False







Sheets("cap").Select


finalrow = Cells(Rows.Count, "R").End(xlUp).Row
Range("a1:r" & finalrow).Clear










Path = "\\a\b\c\" & p_show_Year & p_show_Month & p_show_Day & "\"


fname = "cap_" & p_show_Year & p_show_Month & p_show_Day & ".csv"






Set cap_file = Workbooks.Open(Path & fname)


finalrow = Cells(Rows.Count, "R").End(xlUp).Row






Range("a1:r" & finalrow).Copy






ThisWorkbook.Sheets("cap").Activate



Range("a1").PasteSpecial xlPasteValues




Range("a1").Select








'close


cap_file.Close False






Sheets("menu").Select


ActiveWorkbook.Save

that is the whole macro...regarding the dates..

Public p_show_Day As Variant


Public p_show_Day_short As Variant


Public p_show_Month As Variant


Public p_show_month_short As Variant


Public p_show_month_long As Variant


Public p_show_Year As Variant


Public Sub start_dates()










d1 = Sheets("menu").Range("T_1").Value


    p_show_Day_short = format(d1, "D")
    
    p_show_Day = format(d1, "DD")
    
    p_show_month_short = format(d1, "M")
    
    p_show_Month = format(d1, "MM")
    
    p_show_Month_mid = format(d1, "MMM")
    
    p_show_month_long = format(d1, "MMMM")
    
    p_show_year_short = format(d1, "YY")
    
    p_show_Year = format(d1, "YYYY")

tbh, it shouldn't matter as im using the dates macro to pull in other files no problem...it just so happens this specific file is causing me issues.

 

[Yongle]

Here are some comments
- declaring variables properly helps with solving crashes!!
- I have added some that were not included in what you posted but should have been declared by you
- there is nothing obvious in your code that would make it crash but I do not think I am seeing it all

When using more than one workbooks and hopping between the 2 it is much safer to declare variables and use them to refer to workbooks, sheets and ranges
- and it avoids having to select anything!

Use my suggestions to tighten your code and check that it works when stepping through it
(I am guessing a few things so you need to follow the logic and make sure it is correct!)

If it is still crashing after that when running automatically, let us know and we can give you methods to determine how far the code is running to discover exactly what is failing
We will need to see the whole procedure

Above may not solve the current issue but may help with future coding

Public Sub start_dates()
[I][COLOR=#006400]'declared variables already used in your code ...[/COLOR][/I]
  Dim Path As String, fName As String, cap_file As Workbook
  Dim finalrow As Long

[COLOR=#006400]'variables added by me[/COLOR]
  Dim wb As Workbook: Set wb = ThisWorkbook
  Dim ws As Worksheet: Set ws = wb.Sheets("cap")
  Dim ws2 As Worksheet

[COLOR=#006400]  wb[/COLOR].Sheets("menu").Range("E14:F14").Interior.Color = 65535
  Application.DisplayAlerts = False
                    [COLOR=#006400]ws[/COLOR].Select   'no need to select if you use variables - [COLOR=#ff0000]DELETE this line[/COLOR]
  finalrow = ws.Cells(Rows.Count, "R").End(xlUp).Row
[COLOR=#006400]  ws[/COLOR].Range("a1:r" & finalrow).Clear

  Path = "\\a\b\c\" & p_show_Year & p_show_Month & p_show_Day & "\"

  fName = "cap_" & p_show_Year & p_show_Month & p_show_Day & ".csv"

  Set cap_file = Workbooks.Open(Path & fName)
  Set [COLOR=#006400]ws2[/COLOR] = cap_file.Sheets(1)

[I][COLOR=#006400]'which sheet in which workbook do these ranges refer to?? - I have guessed 1st sheet in cap_file[/COLOR][/I]
    finalrow = ws2.Cells(Rows.Count, "R").End(xlUp).Row
    ws.Range("a1:r" & finalrow).Copy
    
                [COLOR=#006400]ws[/COLOR].Activate 'no need to activate if VBA knows what ws means - [COLOR=#ff0000]DELETE this line[/COLOR]
  With [COLOR=#006400]ws[/COLOR]
    .Range("a1").PasteSpecial xlPasteValues
    .Range("a1").Select
  End With

'close

  cap_file.Close False
[COLOR=#006400]  wb[/COLOR].Sheets("menu").Select
[COLOR=#006400]  wb[/COLOR].Save

End Sub

 

[offthelip]

This sounds very much like a timing issue to me, I have had this sort of probelm with EXCEL before.
What you could try is forcing a recalculation of the workbook when you open it ( after you have opened it) by putting this code in

Set cap_file = Workbooks.Open(Path & fname)

Activesheet.EnableCalculation = False
Activesheet.EnableCalculation = True
finalrow = Cells(Rows.Count, "R").End(xlUp).Row

 

[daveyc18]

This sounds very much like a timing issue to me, I have had this sort of probelm with EXCEL before.
What you could try is forcing a recalculation of the workbook when you open it ( after you have opened it) by putting this code in

Set cap_file = Workbooks.Open(Path & fname)

Activesheet.EnableCalculation = False
Activesheet.EnableCalculation = True
finalrow = Cells(Rows.Count, "R").End(xlUp).Row

still crashed both methods



i jsut ran the code like 10 times and it didn't crash without any modifcations to the code....so sometimes it does, sometimes it doesn't

actually, i remember another macro where saving the workbook at the end caused it to crash....eliminating that line fixed the problem...anybody encounter that, too?

 

[NdNoviceHlp]

Again, "One frustrating problem that can occur is that the clipboard crashes".... when U use the clipboard to repetitively copy and paste U can expect to have unexplained crashes. To fix this U can add some API code to Open, Empty and Close the clipboard after using copy and paste. I'm guessing this will fix your "unexplained" crashes if U insist on using copy and paste rather than an array based solution. HTH. Dave