+ Reply to Thread
Results 1 to 7 of 7

Code to copy and paste not working

Hybrid View

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2010
    Posts
    55

    Code to copy and paste not working

    Hi,

    I have this code that worked once, but something changed and now it runs but doesn't pasted anything in my worksheet.

    I have several worksheets in a workbook that all have similar data. I need to copy the same range from each worksheet with the exception of the following sheets, Audit Temp, Audit Summary, Accuracy Report Group and Master Provider List. I want to copy the range A23:H37 from all the other worksheets into my Billing Temp worksheet separated by blank row and then need to copy range "c4" from each worksheet and place the contents of it above the first range -- this identifies the provider name. I haven't figured out this part of the code yet. Please see example file.

    Example Worksheet.xlsm

    the code I have right now is:

    Sub Billing_CopyIt()
        Dim ws As Worksheet
        Application.ScreenUpdating = False
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> "Audit Temp" And ws.Name <> "Audit Summary" ws.Name <> "Master Provider List" ws.Name <> "Accuracy Report Group" Then
                ws.Range("A23:H37").Copy
                Sheets("Billing Temp").Cells(Rows.Count, "A").End(xlUp).Offset(2).PasteSpecial xlPasteValues
            End If
        Next
        Application.ScreenUpdating = True
    End Sub
    This ran once and almost did what I needed, but I now it runs but doesn't paste anything in Billing Temp.

    Thanks for your help in advance.

    Carole

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: Code to copy and paste not working

    The code is working. In your example workbook, there is extraneous data in row 200 and below on sheet Billing Temp. All the other worksheet data is pasted below that data.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    10-13-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Code to copy and paste not working

    oh my, thanks,

    How can I prevent that from happening?

    Carole

  4. #4
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, UK
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Code to copy and paste not working

    Hi Carole,

    Alternative code (see attached with code saved in Module 5):
    Sub Billing_CopyIt()
    
        Dim ws As Worksheet
        Dim wsOut As Worksheet: Set wsOut = ThisWorkbook.Sheets("Billing Temp")
        Dim rng As Range
        
        Application.ScreenUpdating = False
        
        For Each ws In ActiveWorkbook.Worksheets
            Select Case ws.Name
                Case Is = "Audit Temp", "Audit Summary", "Billing Temp", "Code_Table", "Master Provider List", "Accuracy Report Group", "Instructions"
                    'do nothing
                Case Else
                    On Error Resume Next
                    Set rng = ws.Range("B23:H37").SpecialCells(2)
                    On Error GoTo 0
                    If Not rng Is Nothing Then
                        Set rng = rng.Resize(rng.Rows.Count, rng.Columns.Count + 1).Offset(, -1)
                        rng.Copy: wsOut.Range("A" & Rows.Count).End(xlUp)(2, 1).PasteSpecial xlPasteValues
                        Set rng = Nothing
                    End If
            End Select
        Next ws
        
        Application.ScreenUpdating = True
        
    End Sub
    Cheers, berlan
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-13-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Code to copy and paste not working

    this works great -- how can I add the contents of C4 above the first range and separate each group with a blank line between?

    thanks so much for your effort.

    Carole

  6. #6
    Registered User
    Join Date
    10-13-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Code to copy and paste not working

    this works great -- how can I add the contents of C4 above the first range and separate each group with a blank line between?

    thanks so much for your effort.

    Carole

  7. #7
    Registered User
    Join Date
    10-13-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2010
    Posts
    55

    Re: Code to copy and paste not working

    Hi,

    In an effort to try to resolve my issue with not having the provider's name associated with each of the sections, I added another column and entered a formula to place the provider's name on each row that has been filled in. To accommodate the extra column, I changed the range to ("a23:j37"), but now I am getting an error message in the code:
    Runtime error 1004 - Application-defined or object-defined error. The line highlighted by the error is:
    Set rng = rng.Resize(rng.Rows.Count, rng.Columns.Count + 1).Offset(, -1)

    the code now reads: I have also attached a revised version of the example worksheet.

    Example Worksheet.xlsm
    Sub Billing_CopyIt()
    
        Dim ws As Worksheet
        Dim wsOut As Worksheet: Set wsOut = ThisWorkbook.Sheets("Billing Temp")
        Dim rng As Range
        
        Application.ScreenUpdating = False
        
        For Each ws In ActiveWorkbook.Worksheets
            Select Case ws.Name
                Case Is = "Audit Temp", "Audit Summary", "Billing Temp", "Code_Table", "Master Provider List", "Accuracy Report Group", "Instructions"
                    'do nothing
                Case Else
                    On Error Resume Next
                    Set rng = ws.Range("a23:j37").SpecialCells(2)
                    On Error GoTo 0
                    If Not rng Is Nothing Then
                        Set rng = rng.Resize(rng.Rows.Count, rng.Columns.Count + 1).Offset(, -1)
                        rng.Copy: wsOut.Range("A" & Rows.Count).End(xlUp)(2, 1).PasteSpecial xlPasteValues
                        Set rng = Nothing
                    End If
            End Select
        Next ws
        
        Application.ScreenUpdating = True
        
    End Sub
    Thanks in advance for any assistance.

    Carole

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code for a Button (Copy, Paste) not working. Help Please
    By pelle3988 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2013, 04:45 PM
  2. [SOLVED] Copy & Paste Code not working second time around?
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2013, 05:08 PM
  3. Code: Find, Copy, Paste not working
    By ufopilot3 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2012, 03:40 PM
  4. VBA Copy Paste Code not Working...
    By geng in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2010, 10:30 PM
  5. Code not working exactly, copy and paste issue
    By jpthelpguy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2009, 01:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1