Copy Paste Special Values, on Dynamic Sheet List

reberryjr

Well-known Member
Joined
Mar 16, 2017
Messages
714
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Change this...
Code:
ws.Range("A1" & LastRow & ":CB" & LastRow).Copy

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


Or just this to Copy\Paste values. No need to copy and then PasteSpecial.
Code:
ws.UsedRange.Value = ws.UsedRange.Value
 
Last edited:
Upvote 0
@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.
 
Upvote 0
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.
 
Upvote 0
Try this. Still double check the sheet names.

Code:
[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]
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top