VBA How to IF NOT and go to next page

Question

I have a question, I would like to write a loop that, if one Sheet would not go to fill another, and if it does fill it, I tried so, but it pops up that it must be done by the For statement. I don't know how. I mean If this tab C_0700_0002 is missing, go to the other one for example C_0700_0007.

If wb.Sheets("C_0700_0002") Is Nothing Then
Next
Else
'C_0700_002'
wbMe.Sheets("Tabela_COREP").Range("F4").Copy
wb.Sheets("C_0700_0002").Range("G17").PasteSpecial Paste:=xlPasteValues
wbMe.Sheets("Tabela_COREP").Range("J5").Copy
wb.Sheets("C_0700_0007").Range("G17").PasteSpecial Paste:=xlPasteValues
wbMe.Sheets("Tabela_COREP").Range("J5").Copy
wb.Sheets("C_0700_0007").Range("G17").PasteSpecial Paste:=xlPasteValues

Try ```If wb.Sheets("C_0700_0002") Is Nothing Then GoTo Next_Sheet Else 'C_0700_002' wbMe.Sheets("Tabela_COREP").Range("F4").Copy wb.Sheets("C_0700_0002").Range("G17").PasteSpecial Paste:=xlPasteValues wbMe.Sheets("Tabela_COREP").Range("J5").Copy wb.Sheets("C_0700_0007").Range("G17").PasteSpecial Paste:=xlPasteValues wbMe.Sheets("Tabela_COREP").Range("J5").Copy wb.Sheets("C_0700_0007").Range("G17").PasteSpecial Paste:=xlPasteValues Next_Sheet : ``` That way if the if statement is True, you will skip the part until the ```Next_Sheet: ``` statement happen — AlexHhz, Feb 14 '22 at 15:45
Did you add the last Next_Sheet: statement at the end ? "Label not definied" is the compile error you get when an instruction is referring to a label that doesn't exist in that scope — AlexHhz, Feb 14 '22 at 15:55
`If wb.Sheets("C_0700_0002") Is Nothing` will fail (runtime error 9) when the sheet does not exist, see https://stackoverflow.com/questions/54823466/check-if-sheet-exists-if-not-create-vba. @AlexHhz I don't see a point in GoTo. Simply revert the logic (If Not) — FunThomas, Feb 14 '22 at 16:03

VBasic2008 · Accepted Answer · 2022-02-14T17:00:03.377

2

Copy Conditionally

Dim ws As Worksheet

On Error Resume Next
    Set ws = wb.Worksheets("C_0700_0002")
On Error GoTo 0

If ws Is Nothing Then
    Set ws = wb.Worksheets("C_0700_0007")
End If

ws.Range("G17").Value = wbMe.Worksheets("Tabela_COREP").Range("J5").Value

EDIT

Const sAddressesList As String = "A1,B3,C2,D5,B6,C7,G2,F11,A17,B13"
Const dAddressesList As String = "B3,F8,G6,G15,B17,F7,A3,A7,F15,C4"
Const dNumsList As String = "1,2,3,4,5,6,7,8,9,10"

Dim wb As Workbook
Dim wbMe As Workbook

' Other code...

Dim sws As Worksheet: Set sws = wbMe.Worksheets("Tabela_COREP")

Dim dws As Worksheet
Dim n As Long

Dim sAddresses() As String: sAddresses = Split(sAddressesList, ",")
Dim dAddresses() As String: dAddresses = Split(dAddressesList, ",")
Dim dNums() As String: dNums = Split(dNumsList, ",")

For n = 0 To UBound(dNums)
    On Error Resume Next
        Set dws = wb.Worksheets("C_0700_" & Format(dNums(n), "000#"))
    On Error GoTo 0
    If Not dws Is Nothing Then
        dws.Range(dAddresses(n)).Value = sws.Range(sAddresses(n)).Value
        Set dws = Nothing
    End If
Next n

edited Feb 14 '22 at 17:00

answered Feb 14 '22 at 16:18

VBasic2008

44,888
5
17
28

Thnkas! Thanks, however, if I have dozens of tabs like C_0700_0002, then 0003, 0004 and a lot of copy and paste then I have to `wbMe.Sheets ("Table_COREP"). Range ("H7"). Copy wb.Sheets ("C_0700_0002"). Range ("T26"). PasteSpecial Paste: = xlPasteValues` change this procedure to the one you provided in range =? – Przemek Dabek Feb 14 '22 at 16:26
Then it's a little bit different. Can we assume that the source worksheet will always be `wbMe.Worksheets("Table_COREP")` and that only the cell will change? You would want to use 3 lists, possibly arrays, that would control which 1.)source worksheet is associated with which 2.)source- and 3.)destination cell. – VBasic2008 Feb 14 '22 at 16:35
More to me is that I have tabs Where to copy from and paste somewhere. Let's say they go C_0700_0002 then there is C_0700_0003 etc. And I would like, for example, when there is no C_0700_0002, it went to the next tab C_0700_0003, if there is no C_0700_0003, then to the next one. – Przemek Dabek Feb 14 '22 at 16:47
maybe easier if I say yes. if there is no sheet, let the macro continue – Przemek Dabek Feb 14 '22 at 16:57

VBA How to IF NOT and go to next page

1 Answers1

Copy Conditionally

EDIT