CODEWITHSUNDEEP
excelvba
JPR
JPR

Reputation: 13

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

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

Upvotes: 1

Views: 218

Answers (1)

David Zemens
David Zemens

Reputation: 53663

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

Upvotes: 1

Related Questions