Solved

Excel Macro Paste Special does not work

Posted on 2010-09-21
20
761 Views
Last Modified: 2012-05-10
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()
   
    Columns("A:CZ").Select
    Selection.ClearContents
    Selection.Delete Shift:=xlToLeft
    ' 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!
    wsSource.UsedRange.Copy
   
    With wsDest
        .Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        .Cells.EntireColumn.AutoFit
    End With
    wb.Close False
 
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub
0
Comment
Question by:guser1
  • 8
  • 5
  • 3
  • +1
20 Comments
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33727202
You code isn't coping the entire worksheet.  Only the usedrange.
 wsSource.UsedRange.Copy
Can you provide a sampel workbook?
 
0
 

Author Comment

by:guser1
ID: 33733526
Good Morning and thank You for your comment... your right I misspoke.

I've attached a sample work book the Master-org-longacct sheet is the data I'm trying to copy.  Please note: there is a hidden column - column J - in this sheet which is where you'll see the extra rows of formulas.


Sample.xls
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33734420
Can you provide a workbook with the expected end result?  I'm still not quite sure what the issue is.
0
 

Author Comment

by:guser1
ID: 33735306
Please review the attached Sample.  My end result is to have a copy of Master-org-longacct sheet in a new worksheet where the user can then click a button to convert the data to fixedwidth.  

I am using Excel 2003 and I am a novice... this originally started as a simple quick and dirty macro to convert data to fixed width lengths to then be imported into Access... but this has gradually moved out of my ream of expertise.

The problem is that the copy does not take place. the system hangs up and I have to end the run.  If I go in and manually delete/clear all the extra lines (columns and rows)  in the data and unhide and/or delete the hidden column then run this macro it works fine.

I guess my question is .... is there a way to add code to clean the data up prior to the copy or just read only the lines of valid data.

Thanks again for your input
Sample-2.xls
0
 
LVL 33

Expert Comment

by:Norie
ID: 33741132
Can you not just copy the entire worksheet?

If you just need the values then you can do that and then a copy pastespecial on the copied sheet.
0
 

Author Comment

by:guser1
ID: 33788648
Good Morning, I made the change to copy and I am still experiencing the same hangup.  Any suggetions would be greatly appreciated.
0
 

Author Comment

by:guser1
ID: 33822034
OK ... so no response to my issue. since the 22nd...
Should I close this session and open another request for more points?
Is there a better way to write this to get what I need?
Does someone have code I can use to copy and paste the sample data I provided to a new worksheet.?
Could my problem with copying/pasting the data with garbage be that I'm using Excel 2003?

I'm at a lost any help would be greatly appreciated.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33822517
I think if you explained further what you want to do it might help.
Also what do you mean by 'hang-up' exactly?
When that happens can you stop the code with CTRK+BREAK?
0
 

Author Comment

by:guser1
ID: 33823666
I am trying to open an existing workbook, pick a sheet within the existing workbook
Select all the data in that selected worksheet
Copy that data
Then Paste that data to a new workbook.

The process:
1. The user doubke clicks on the blank/new excel spreadsheet with the macro code  (this is where the data should end up)
2. A Dialog box appears and asks the User to find the existing file with the workbook they need to pull   data from.  

3. In the dialog box: The User selects the file/directory then selects the workbook then Clicks OK
4. The existing workbook is opened
5. The user is asked to select a sheet with in the existing workbook by highlighting a cell
6. The User selects a cell then clicks OK
7. The data is then copied and then pasted into the blank/new excel spreadsheet with the original macro code,.stated in #1.

When I say the job Hangs-up ... I mean:

I don't get an error message but the program just stops responding and I have to manually do a CNTRL-ALT-DEL , go to the task manager and do an "end task" to end everything.


****    When I manually Unhide hidden columns then manually DELETE all  blank rows and columns save the updated file then run the process, the macro works.and the data is copied and pasted to the new spreadsheet successfully
 
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 33857674
Hi Guser1,

I have tried your code in Excel 2003 and Excel 2007 and I cannot fail it! It works every time for me. The problem must be with the file you are trying to pull the data from. Without knowing what's in that file it will be very hard to work out what your problem relates to.
1. Can you upload a copy of this file without any confidential data?
2. Does your code work for other files?

Regards,
Curt
0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 33857731
I should also have mentioned that I tried this with a file containing a mix of hidden columns, blank lines, formulas, text and numbers. I encountered no problems using this file.

Regards,
Curt
0
 
LVL 33

Expert Comment

by:Norie
ID: 33857813
If the code works when you manually unhide columns etc why not add code to do that?
Did you ever try my suggestion of copying the whole worksheet into the destination (or a new) workbook?
This works but is a wee it slow.
There also seems to be a lot of #REF! errors, but they are also in the original file.
Should rows with those errors be copied.
 



Sub test()

Dim wbNew As Workbook

Dim wsDst As Worksheet

Dim wsSrc As Worksheet



    Set wsSrc = ThisWorkbook.Worksheets("Master-Org-LONGACCT")



    wsSrc.Copy



    Set wbNew = ActiveWorkbook



    Set wsDst = wbNew.Worksheets(1)



    With wsDst

        .Range("J:J").Value = Range("J:J").Value

        .Cells.AutoFilter

        .Cells.EntireRow.Hidden = False

        .Cells.EntireColumn.Hidden = False

    End With



End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
ID: 33857845
I also tried this.
Option Explicit



Sub test()

Dim wbNew As Workbook

Dim wsDst As Worksheet

Dim wsSrc As Worksheet



    Set wsSrc = ThisWorkbook.Worksheets("Master-Org-LONGACCT")



    Set wbNew = Workbooks.Add(xlWBATWorksheet)

    

    Set wsDst = wbNew.Worksheets(1)



    wsSrc.UsedRange.SpecialCells(xlCellTypeVisible).Copy

    

    With wsDst

        .Range("A1").PasteSpecial xlPasteValues

        .Cells.EntireRow.Hidden = False

        .Cells.EntireColumn.Hidden = False

    End With



End Sub

Open in new window

0
 

Author Comment

by:guser1
ID: 33879332
Hello again and Thank you for your responses.  I tried the FIRST sample code above and it opens up a 'NEW' worksheet and copies/paste the data sucessfully.... but again when I try and copy/paste the data to the already 'Opened'/Active sheet as in the "SECOND" sample code you gave me then the system locks up/stops responding.

Epacm - you will find my sample data attached to the 2nd and 4th comments above.

Thank you for your help not sure what else I can try...
0
 
LVL 18

Accepted Solution

by:
Curt Lindstrom earned 125 total points
ID: 33880375
Hi guser1,

The Application.DisplayAlerts = False is indirectly causing this problem.
The problem happens because a display alert is asking if you want to copy data to the clipboard is answered by the default reply which unfortunately is YES for this question. This happens at the line wb.close False

It appears to be a Microsoft bug that makes this question to lock up when you answer YES for a very large amount of data to be saved to the clipboard. It doesn't happen when you have a sheet with less amount of data. This is why it works when you edit the sheet before copying the data. It is not what you delete it's how much you delete which fixes the problem.

To see what I mean just comment out
'Application.DisplayAlerts = False
and then run your macro and select your sheet that fails.
When you get the clipboard question answer NO and everything will work fine.
If you answer YES you will get the lockup.

Cheers,
Curt

Alert.bmp
0
 

Author Closing Comment

by:guser1
ID: 33884455
I was a little frustrated at first but once I asked for more help the responses were great.  
0
 

Author Comment

by:guser1
ID: 33884477
Thank you soooo much !!!!! Epaclm Thank you....
0
 
LVL 33

Expert Comment

by:Norie
ID: 33884598
Well glad you've got it sorted.
Mind you, I don't know why but I think if you had tried the code I posted, as is, you might have seen
the alert that Curt pointed out
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now