Error trying to select sheet with public variable

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows
I am trying to make a code that makes the program write data in a specific excel spreadsheet according to the value of the variable "MachineName" declared as public


What happens is that the program does not allow me to do it because an error is detected on line 96


The error:


Capturar.JPG


Capturar.JPG




The code is that:

VBA Code:
Sub KillEmpty()

    Dim sh As Worksheet
    Dim k As Long

    Range("C4").Select
   
    If MachineName = "A4" Then
   
        Set sh = ThisWorkbook.Sheets("A4")
       
    ElseIf MachineName = "A8.1" Then
   
        Set sh = ThisWorkbook.Sheets("A8_1")
   

    ElseIf MachineName = "A8.2" Then
      
        Set sh = ThisWorkbook.Sheets("A8_2")
   
    ElseIf MachineName = "A8.3" Then
   
        Set sh = ThisWorkbook.Sheets("A8_3")
       
       
    ElseIf MachineName = "A12.1" Then

        Set sh = ThisWorkbook.Sheets("A12_1")
   
   
    ElseIf MachineName = "A12.2" Then

        Set sh = ThisWorkbook.Sheets("A12_2")
     
        
    ElseIf MachineName = "A12.3" Then

        Set sh = ThisWorkbook.Sheets("A12_3")
   
   
    ElseIf MachineName = "A20.1" Then

        Set sh = ThisWorkbook.Sheets("A20_1")
       

    ElseIf MachineName = "A20.2" Then

        Set sh = ThisWorkbook.Sheets("A20_2")
       
    End If
   
    k = sh.Range("C4", sh.Range("C4").End(xlDown)).Rows.Count
   
   
    If ActiveCell.Value = "" Then
        Call FillCells
   
    ElseIf ActiveCell.Value <> "" Then
        Do Until ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
        Loop
        Call FillCells
       
    End If
End Sub

Can anyone help me?
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows
Which module is the declaration in, and what is the code that calls the routine you posted?
Capturar.JPG

Is in the module 1



is this what you need when you speak in a routine?
Capturar.JPG
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
What happens if you put this new line:
VBA Code:
MsgBox sh.Name
before this line in your current code:
VBA Code:
k = sh.Range("C4", sh.Range("C4").End(xlDown)).Rows.Count
This should tell you the workshete name that the "sh" variable is assigned is before using it.
Very that it matches the sheet name EXACTLY, and the sheet is not hidden or anything like that.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,195
Messages
5,640,793
Members
417,166
Latest member
Funwayo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top