Excel Macro Paste Special does not work
Posted on 2010-09-21
I am trying to open a workbook, pick a sheet within the workbook and copy that data to a new workbook. My code works fine as long as there is no garbage in the data fields (i.e. fomulas in rows/columns without data). If there is garbage the paste function to the new worksheet just locks up. I don't get an error message but the program just stops responding and I have to manually go to the task manager and end task.
Since I have several users and the amount of data varies .... I can't always determine at a glance it garbage is in a spreadsheet.
Could someone show me how to read just the records in the worksheet and then do the paste for just those records and not the entire worksheet. Thank You.
Here's my current code which begins when I open the new worksheet.
Private Sub Workbook_Open()
' remove borders clear fields then delete
Dim x As String, y As String
Dim xpath As String
Dim rg As Range
Dim wb As Workbook
Dim wsDest As Worksheet, wsSource As Worksheet
Application.EnableEvents = False
Application.DisplayAlerts = False
Set wsDest = ActiveWorkbook.Worksheets("Sheet1")
xpath = "h:\1-TYLER MUNIS\Conversion\Conversion Districts"
xpath = Application.GetSaveAsFilename(xpath, FileFilter:="Microsoft Excel workbooks (*.xls), *.xls", _
Title:="Please pick desired workbook, then click 'Save'")
Set wb = Workbooks.Open(xpath)
'Let user pick the source worksheet
On Error Resume Next
Set rg = Application.InputBox("Please pick any cell in source worksheet", "Use Window menu to change workbooks", Type:=8)
On Error GoTo 0
If rg Is Nothing Then Exit Sub
Set wsSource = rg.Worksheet
Application.ScreenUpdating = False 'Don't move this statement if you want to see dialogs!
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True