VBS is selecting the wrong sheet?

[NessPJ]

Hey guys,

I have the code enclosed i am currently working on.
It seems that the routines followed after my "Cleanup" label are not selecting the desired Sheet (called VBACACHE).
This results in my "main sheet" in the file called "Sheet1" to get almost all of its content deleted (whoops).

The code is executed when "Sheet1" is selected for the user. What am i doing wrong?

Private Sub DockindelingVullen()

Dim CountRange As Range
Dim CountF1 As Integer                    'CountA Functie voor VBA
Dim CountF2 As Integer                    'CountA Functie voor VBA

Dim ErrorCheck1 As Long                  'ErrorCheck1 = Controle op de selectie van afleverdagen. Een dag mag slechts 1 keer geselecteerd zijn.
Dim ErrorCheck2 As Long                  'ErrorCheck2 = Controle of de invoertabellen niet helemaal leeg zijn.

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual

    Sheets("Sheet1").Unprotect ("1234")
    Sheets("VBACACHE").Unprotect ("1234")
  
    Sheets("VBACACHE").Visible = True

    'Error Checks
    '============
    'ErrorCheck1
    ErrorCheck1 = Application.Sum(Range("AO9:AO15"))
        If ErrorCheck1 > 0 Then GoTo ERROR1
         
    'ErrorCheck2
    Set CountRange = Sheets("Sheet1").Range("G4:G1200")
    CountF1 = Application.WorksheetFunction.CountA(CountRange)
    Set CountRange = Sheets("Sheet1").Range("L4:L1200")
    CountF2 = Application.WorksheetFunction.CountA(CountRange)
    ErrorCheck2 = CountF1 + CountF2
        If ErrorCheck2 < 1 Then GoTo ERROR2
  
    'Start Routine
    '=============
    
    'Werksheet/Werktabel "VBACACHE" opschonen
    Sheets("VBACACHE").Range("A1:Z5000").ClearContents
  
    'Gegevens van de 2 Tabellen (Huidig en Nieuw) worden gekopieerd naar een tijdelijk werkblad.
  
    'Data Kopiëren
    'Doel                                           'Bron
    Sheets("VBACACHE").Range("A1:D1197").Value = Sheets("Sheet1").Range("G4:J1200").Value
    Sheets("VBACACHE").Range("A1198:D2394").Value = Sheets("Sheet1").Range("L4:O1200").Value
  
CLEANUP:

    'Code voor het verwijderen van cellen met 'lege' regels die volgens Excel niet volledig leeg zijn
    '=================================================================================================
  
    Dim r As Range
    Dim n As Double
    Dim rcount As Double
    Dim mybool As Boolean
  
    Set r = Sheets("VBACACHE").CurrentRegion

    n = 1
    mybool = False

    Do
        rcount = r.Rows.Count
            If Len(Cells(n, 1).Value) = 0 Then
                Rows(n).Delete
                If rcount = 1 Then Exit Sub
                mybool = True
            End If
        If mybool = False Then n = n + 1
        mybool = False
    Loop While n <= rcount
  
    'Dezelfde Code wordt nogmaals gebruikt om alleen de gewenste Dag selectie mee te nemen in de export file
    '=======================================================================================================
    
    n = 1
    mybool = False

    Do
        rcount = r.Rows.Count
            If Cells(n, 4).Value = "N" Then
                Rows(n).Delete
                If rcount = 1 Then Exit Sub
                mybool = True
            End If
        If mybool = False Then n = n + 1
        mybool = False
    Loop While n <= rcount
  
    r.Columns(4).Delete
  
  
    'Code voor het leegmaken van cellen die de waarde '0' bevatten
    '=============================================================
  
    n = 1
    mybool = False

    Do
        rcount = r.Rows.Count
         For J = 12 To r.Columns.Count
            If Cells(n, J).Value = "0" Then
                Cells(n, J).Value = ""
                If rcount = 1 Then Exit Sub
                mybool = True
                Exit For
            End If
          Next J
        If mybool = False Then n = n + 1
        mybool = False
    Loop While n <= rcount
  
  
  
  
  
  
  
  
Einde:
  
  
    Sheets("Sheet1").Protect ("1234")
    Sheets("VBACACHE").Protect ("1234")
  
    Sheets("VBACACHE").Visible = False
  
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic

    Exit Sub


ERROR1:

    MsgBox ("Er zijn 1 of meer afleverdagen geselecteerd in beide Tabellen. Een afleverdag mag slechts 1 maal geselecteerd staan."), vbCritical, "Fout in Afleverdag selectie!"
  
    Sheets("Sheet1").Protect ("1234")
    Sheets("VBACACHE").Protect ("1234")
  
    Sheets("VBACACHE").Visible = False
  
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
  
    Exit Sub
  
  
ERROR2:

    MsgBox ("Er zijn geen gegevens aanwezig in de dockindeling tabel. Er kan niets uitgevoerd worden."), vbCritical, "Geen gegevens aanwezig!"
  
    Sheets("Sheet1").Protect ("1234")
    Sheets("VBACACHE").Protect ("1234")
  
    Sheets("VBACACHE").Visible = False
  
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
  
    Exit Sub
  

End Sub

[Edit]
Oops, title should've stated VBA (typo)

 

[Fluff]

What am i doing wrong?

If you are referring to the "Cleanup" section of your code, you have not qualified cells & rows with the sheet name, so it works on the active sheet.

 

[NessPJ]

If you are referring to the "Cleanup" section of your code, you have not qualified cells & rows with the sheet name, so it works on the active sheet.

Hey, thanks for your reply!

I'm still a bit unsure:
If r As Range, i declare Set r = Sheets("VBACACHE").CurrentRegion and the syntax is r.Rows.Count
Doesn't that mean i am referencing to the VBACACHE worksheet?

 

[Fluff]

That is ok, but these lines

            If Len(Cells(n, 1).Value) = 0 Then
                Rows(n).Delete

are looking at the active sheet
That said I would expect this line

Set r = Sheets("VBACACHE").CurrentRegion

to give an error, because you not specified a range

 

[NessPJ]

That is ok, but these lines

            If Len(Cells(n, 1).Value) = 0 Then
                Rows(n).Delete

are looking at the active sheet
That said I would expect this line

Set r = Sheets("VBACACHE").CurrentRegion

to give an error, because you not specified a range

Ahhh, thanks! I will change those lines.

Apparantly CurrentRegion is an actual VBA property :

Range.CurrentRegion property (Excel)

Office VBA reference topic

docs.microsoft.com

I use this "Cleanup" routine in several Excel files of mine. I am suprised this one was giving me issues where others were not.
Maybe i did a "Sheets("Sheet1").Range("A1").Activate" with the other files i use.....

 

[Fluff]

Yes it is a VBA Property, but it says

expression.CurrentRegion

expression A variable that represents a Range object.

You are using a sheet, not a range, it should be something like

Set r = Sheets("VBACACHE").Range("A1").CurrentRegion

 

[NessPJ]

That was actually sloppy copy work on my end. Fixed!

 

[Fluff]

You're welcome & thanks for the feedback