after run a code a range value changes and get a error REF

Question

I have a workbook with various sheets named Alert* (each Alert sheet name have a different date on it) and client sheet. The time that I run code to Copy and Paste the information in 2 sheets named client I am facing an issue.

The Copying and Paste are working fine. However, it deletes the information in Range ("K16", "C1" & "C2") in all my sheets named Alert*. It is not a big problem, because I still can copy this information once again from the client sheet.

I am trying many ways to copy and paste code, and I can't make it work.

Dim sht As Worksheet
Dim sw As Worksheet: Set sw = Sheets("Client*")

 For Each sht In Worksheets
    If sht.Name Like "Alert*" Then
        sht.Range("K16").Value = sw.range("J3")
        sht.Range("C1").Value = sw.range("C1")
        sht.Range("C2").Value = sw.range("C2")
    End If
Next ws

I try Dim sht As Worksheet set sht = worksheets("Alert*)

ActiveSheet.Range("J3").Copy sht.Range("K16") ActiveSheet.Range("C1:C2").Copy sht.Range("C1:C2")

But it is not working.

Maybe it would be possible to use a loop to check all the sheets named Alert* and paste the information from Client sheet to the correct range.

Have you tried ws.Range("K16").Formula = "='Client Review'!J3"? — Samuel Everson
Have you stepped through the error checker for the formula on the worksheet which generally helps work out where the error is caused from? — Samuel Everson
Hmmm, I don't see anywhere in your code where the sheet "Client Review" is deleted or manipulated - have I missed something? Also does this MS document about the REF error help? — Samuel Everson

Xabier Xabier · Accepted Answer · 2020-05-07T12:58:50

If we are to assume that you create a new worksheet named "Client Review" manually or by some other method not shown, I believe the below will help you achieve your desired outcome.

It will essentially look for a worksheet called "Client Review*" (let's called this Worksheet A), then copy the ranges from that worksheet to the "Client Review" (Worksheet B), and then it will delete A and rename B to have a date stamp on it, so when you re-run this another day when you have recreated the "Client Review" worksheet (i.e Worksheet A).

If all I said above makes sense, then you would have to ensure you update the formula for K16 once you have recreated the Client Review Worksheet.

I've added another loop similar to yours to ensure every worksheet named Client* does have the formula ("='" & ws.Name & "'!J3")

Sub CopyOldToNew()
Application.ScreenUpdating = False: Application.DisplayAlerts = False: Application.AskToUpdateLinks = False 'For less lag
Dim wsClientReview As Worksheet: Set wsClientReview = ThisWorkbook.Worksheets("Client Review")
Dim wsPreviousClientReview As Worksheet
Dim ws As Worksheet

On Error GoTo ErrorTrue

today = Format(Date, "MM.DD.YYYY")
For Each wsPreviousClientReview In ThisWorkbook.Worksheets
    If wsPreviousClientReview.Name Like "Client Review*" And wsPreviousClientReview.Name <> "Client Review" Then
        'wsPreviousClientReview.Activate
        Exit For
    End If
Next ws

wsPreviousClientReview.Range("A22:N250").Copy

wsClientReview.Range("A22:N250").Paste
wsClientReview.Range("J3").Value = wsPreviousClientReview.Range("J3").Value
wsClientReview.Range("G8:H12").Value = wsPreviousClientReview.Range("G8:H12").Value
wsClientReview.Name = "Client Review " & Format(Date, "mm.dd.yyyy")
ws.Delete

wsClientReview.Move before:=Thisworbkook.Sheets(1)

For Each ws In Worksheets
    If ws.Name Like "Client*" Then
        ws.Range("K16").Value = wsClientReview.Range("J3").Value
    End If
Next ws

Application.ScreenUpdating = True: Application.DisplayAlerts = True: Application.AskToUpdateLinks = True
Exit Sub

ErrorTrue:
MsgBox "No manually added sheets identified."
Alert.Activate
Application.ScreenUpdating = True: Application.DisplayAlerts = True: Application.AskToUpdateLinks = True
End Sub

after run a code a range value changes and get a error REF

1 Answers