Copy data pulled by formulas and then paste it as values within the same sheet

Question

I put together code to copy data pulled by formulas and then paste it as values within the same sheet. I have multiple workbooks that vary in the amount of worksheets/tabs they contain.

Problem #1:
My first worksheet on every file is called REGION.
The code is not executing on the "REGION" worksheet as designed and is also skipping the worksheet immediately after it.

Problem #2
Depending on the workbook, after 6 worksheets, I get

Run-Time Error '1004'

Problem #3
On workbooks small enough for the code to cycle through all worksheets and not finding any more worksheets to apply the code I get

Error 400

In summary, I need to:

1. Figure out why the code is skipping the worksheet immediately after the one called REGION (1st Tab)

2. Prevent error '1004' because the program is performing the same function over and over on multiple worksheets (read that I should probably set it to save every few tabs to prevent this but not sure how to do it while looping).

3. Add to the code a line that stops the cycle without throwing an error message.

Sub COPYPASTE()

Dim ws As Worksheet
    
For Each ws In Sheets
    If ws.Name <> "REGION" Or ws.Name <> "Legend" Then
        Range("C3:I47").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A2").Select
    End If
    ActiveSheet.Next.Select
Next
     
End Sub

you need to fully qualify your ranges, sheets etc. Like ThisWorkbook.Worksheets or Workbooks(2).Worksheets etc. Same for ws.Range("C3:I47"). Copy paste needs to go like this : ws.Range("C3:I47").Copy ws.Range("C3:I47").Cells(1,1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False — cyboashu, Aug 09 '17 at 16:27
@JPR please split this out into multiple Questions, StackOverflow rules say that a Question should not present multiple issues, but a discrete single issue. — Graham, Aug 09 '17 at 17:07
@Graham my bad. Because I was in a pinch and posted as fast as I could. I'll make sure to read all rules in detail. — JPR, Aug 09 '17 at 17:54
Consider editing the question so that the accepted answer still makes sense, then move everything else out separately. Thanks! — Graham, Aug 09 '17 at 20:20

score 1 · Accepted Answer · answered Aug 09 '17 at 17:07

Avoid using Select and/or Activate (and related: ActiveSheet, etc.) and while we're at it, rather than Copy/PasteSpecial, just do a direct value-assignment using the range's Value property.

So that this:

Dim ws As Worksheet

For Each ws In Sheets
If ws.Name <> "REGION" Or ws.Name <> "Legend" Then
Range("C3:I47").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A2").Select
End If
ActiveSheet.Next.Select
Next

Becomes this (also note the Or should be an And, I think)

Dim ws As Worksheet
Dim rng as Range
For Each ws In Sheets
    'Do not operate on either of Region or Legend worksheets
    If ws.Name <> "REGION" And ws.Name <> "Legend" Then
        Set rng = ws.Range("C3:I47")
        rng.Value = rng.Value2
    End If
Next

@DavidZemens, you are correct aboutt the `And` .... the `or` would be `true` for all worksheet names .... ("REGION" <> "Legend)" — jsotola, Aug 09 '17 at 17:54

Copy data pulled by formulas and then paste it as values within the same sheet

1 Answers1