Macro causes Excel to stop Responding

[AK30]

Hola - due to company security can't post from the computer I'm actually working on this so here is the snip of code Excel is hanging up on:

newfn = Application.GetOpenFilename(FileFilter:="Scrub File (*.xls), *.xls", Title:="Please select yesterday's results scrub file")
If newfn = False Then
MsgBox "Process cannot continue without selection of a scrub file"
Exit Sub
Else
Set srcwrkbk = Workbooks.Open(newfn)
srcwrkbk.Activate
Columns("A:R").Copy
ThisWorkbook.Activate
Sheets("ScrubSheet").Paste Destination:=Range("A1")
srcwrkbk.Close True
End If

I commented out these lines:
'Columns("A:R").Copy
'ThisWorkbook.Activate
'Sheets("ScrubSheet").Paste Destination:=Range("A1")
and it works (sans copy / paste) but when they are added back in Excel just locks up. Thoughts?

(Using Excel 2003)

 

[jbeaucaire]

Try this:

Option Explicit

Sub TestMacro()
Dim newFN As String
Dim srcWB As Workbook
Dim destWS As Worksheet

newFN = Application.GetOpenFilename(FileFilter:="Scrub File (*.xls), *.xls", Title:="Please select yesterday's results scrub file")
If newFN = False Then
    MsgBox "Process cannot continue without selection of a scrub file"
    Exit Sub
End If

Set destWS = ThisWorkbook.Sheets("ScrubSheet")
Set srcWB = Workbooks.Open(newFN)
Activatesheet.Range("A:R").Copy destWS.Range("A1")
srcWB.Close True

End Sub

 

[AK30]

Thanks for the reply jbeaucaire. For some reason that gave me a Type Mismatch error on the "If newFN = False Then" line. I've got it working now though with this:


newFN = Application.GetOpenFilename(FileFilter:="Scrub File (*.xls), *.xls", Title:="Please select yesterday's results scrub file")
If newFN = False Then MsgBox "Process cannot continue without selection of a scrub file"
Exit Sub
End If

Set destWS = ThisWorkbook.Sheets("ScrubSheet")
Set srcWB = Workbooks.Open(newFN)
srcWB.Sheets("Distribution").Range("A:R").Copy destWS.Range("A1")
srcWB.Close True


Thanks again!

 

[jbeaucaire]

Sorry, my bad, it was supposed to be "False", not False.

Option Explicit

Sub TestMacro()
Dim newFN As String
Dim srcWB As Workbook
Dim destWS As Worksheet

newFN = Application.GetOpenFilename(FileFilter:="Scrub File (*.xls), *.xls", Title:="Please select yesterday's results scrub file")
If newFN = "False" Then
    MsgBox "Process cannot continue without selection of a scrub file"
    Exit Sub
End If

Set destWS = ThisWorkbook.Sheets("ScrubSheet")
Set srcWB = Workbooks.Open(newFN)
Activatesheet.Range("A:R").Copy destWS.Range("A1")
srcWB.Close True

End Sub