Copy Paste Special Values, on Dynamic Sheet List

[reberryjr]

I'm trying to copy all records in between columns A:CB, through the last row. This is on sheets where I won't know what their name is, but I do know what it won't be. The following code isn't producing anything:

Sub CopyPaste_ClientSheets()

Dim ws As Worksheet
Dim LastRow As Long


For Each ws In Worksheets
    If Not ws.Name = "Displays" And Not ws.Name = "Management" And Not ws.Name = "Summaries" _
    And Not ws.Name = "Bios" And Not ws.Name = "Stats" And Not ws.Name = "Appt Tracker" _
    And Not ws.Name = "Pymt Tracker" And Not ws.Name = "Variables" Then
        LastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
        If Len(ws.Range("A2").Value) = 0 Then
            Exit Sub
        Else
            ws.Range("A1" & LastRow & ":CB" & LastRow).Copy
            ws.Range("A1").PasteSpecial xlPasteValues
        End If
    End If
Application.CutCopyMode = False
Next ws


End Sub

 

[AlphaFrog]

Change this...

ws.Range("A1" & LastRow & ":CB" & LastRow).Copy

To this...

ws.Range("A1:CB" & LastRow).Copy

Or just this to Copy\Paste values. No need to copy and then PasteSpecial.

ws.UsedRange.Value = ws.UsedRange.Value

 

[reberryjr]

@AlphaFrog thanks for the info, and I'm going to try the alternatives you suggested. The issue at hand however, is that the code isn't finding the other sheets. When I step through it, it seems to be stopping at the Displays sheet.

 

[AlphaFrog]

If the first sheet it finds that is not one of the excluded sheets has A2 empty, the code will exit because of this line. Do you really want to exit if the worksheet has A2 empty?

If Len(ws.Range("A2").Value) = 0 Then

Also, double check that all the excluded sheet names are the exact names of the sheets. And make sure there are no trailing spaces in the sheet names e.g. the Displays tab name is really "Displays " with a space at the end.

 

[AlphaFrog]

Try this. Still double check the sheet names.

[color=darkblue]Sub[/color] CopyPaste_ClientSheets()
    
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] ws [color=darkblue]In[/color] Worksheets
    
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] ws.Name
            [color=darkblue]Case[/color] "Displays", "Management", "Summaries", "Bios", "Stats", "Appt Tracker", "Pymt Tracker", "Variables"
            [color=darkblue]Case[/color] [color=darkblue]Else[/color]
                [color=darkblue]If[/color] Len(ws.Range("A2").Value) > 0 [color=darkblue]Then[/color] ws.UsedRange.Value = ws.UsedRange.Value
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    
    [color=darkblue]Next[/color] ws
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[AlphaFrog]

I'm trying to copy all records in between columns A:CB, through the last row.

It's not clear where you want to paste the copied values?

 

[reberryjr]

I actually just used the code you provided, and it works great! I wasn't aware of the "used range" feature, so I was trying to say that if A2 wasn't null, copy all data from A1 through the last column, and every row with data, then paste it in A1. Your used range suggestion, does just that.

Many thanks!