0

So, I have two sub-routines.

The 1st is for importing worksheets and the 2nd is for deleting blank rows if a certain condition holds true.

I would like to pass in the name of the sheet that has been imported, to the DeleteBlankCells() sub-routine. I think this is the Set wsSht = .Sheets(sWSName) variable here that we are setting.

In the 2nd sub-routine, you can see the hardcoded sheet value that I would like to replace with the value being passed in from the import.

This is the 1st sub-routine:

Sub ImportSheet()
Dim sImportFile As String, sFile As String
Dim sThisBk As Workbook
Dim vfilename As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set sThisBk = ActiveWorkbook
sImportFile = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
If sImportFile = "False" Then
    MsgBox "No File Selected!"
    Exit Sub

Else
    vfilename = Split(sImportFile, "\")
    sFile = vfilename(UBound(vfilename))
    Application.Workbooks.Open Filename:=sImportFile
    Set wbBk = Workbooks(sFile)
    With wbBk
        If SheetExists(sWSName) Then
            Set wsSht = .Sheets(sWSName)
            wsSht.Copy after:=sThisBk.Sheets("Sheet3")
        Else
            MsgBox "There is no sheet with name :Raw_Data in:" & vbCr & .Name
        End If
        wbBk.Close SaveChanges:=False
    End With
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Sheets("Sheet1").Activate
End Sub

Private Function SheetExists(sWSName) As Boolean
Dim ws As Worksheet
On Error Resume Next
sWSName = InputBox("Enter sheet name")
Set ws = Worksheets(sWSName)
If Not ws Is Nothing Then SheetExists = True
End Function

and this is the 2nd sub-routine:

Sub DeleteBlankCells()
Dim Rng As Range

Sheets("HARDCODED SHEET NAME").Activate

Set Rng = Rows("1:1").Find(What:="HIVE_FIELD_TYPE", after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
   Sheets("HARDCODED SHEET NAME").Columns(Rng.EntireColumn.Address).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub
TylerDurden
  • 1,632
  • 1
  • 20
  • 30
ToofuWarrior
  • 3
  • 1

2 Answers2

1

You can sen over the Worksheet itself, no need to use the Sheet's name.

In your 2nd Sub, change to:

Sub DeleteBlankCells(ws As Worksheet)

Dim Rng As Range

With ws
    Set Rng = .Rows("1:1").Find(What:="HIVE_FIELD_TYPE", after:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    .Columns(Rng.EntireColumn.Address).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

End Sub

And in your 1st Sub call it after:

Set wsSht = .Sheets(sWSName)

With:

DeleteBlankCells wsSht
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • Do we have to call it in the 1st Sub? It's just I have two separate controls buttons for performing these sub-routines? and I'd like to keep them isolated? – ToofuWarrior Apr 26 '17 at 14:16
  • @ToofuWarrior then you can declare `wsSht` as a `Global` variable, is it something you want to do ? Do you know how ? – Shai Rado Apr 26 '17 at 14:17
  • I don't know how to do that, but I also just thought... alternatively, I could just import the sheet and then rename during that import it to the a hard coded value? – ToofuWarrior Apr 26 '17 at 14:20
  • @ToofuWarrior there are many was to implement it, I answered your posr, which titles "How do you pass in the set variable name of a field from one sub-routine to another in VBA?" – Shai Rado Apr 26 '17 at 14:26
  • Aye, you did! Thanks friend – ToofuWarrior Apr 26 '17 at 14:42
0

Alternatively, perhaps a global variable? (it is hacky way to accomplish it)

How to declare Global Variables in Excel VBA to be visible across the Workbook

Community
  • 1
  • 1
royalflight
  • 13
  • 2