RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- Office Version
- 2019
- Platform
- Windows
Hello guys
I have this code in which, when there is data in the MasterData Sheet, it shows the perfect result. But the hidden sheets are unhidden, when there is no data entered, after the msgbox that displays Data is not entered. I am unaware why it is opening the hidden sheet. Please corect me, if the placement of hidesheets and unhidesheets is right and if ok then how do I keep the sheet hidden, even when there is no data entered.
I have this code in which, when there is data in the MasterData Sheet, it shows the perfect result. But the hidden sheets are unhidden, when there is no data entered, after the msgbox that displays Data is not entered. I am unaware why it is opening the hidden sheet. Please corect me, if the placement of hidesheets and unhidesheets is right and if ok then how do I keep the sheet hidden, even when there is no data entered.
Rich (BB code):
Option Explicit
Sub GenerateMasterXML()
Application.ScreenUpdating = False ' Turn ScreenUpdating off
Dim ImportMastersFormulaStartRow As Long
Dim cell As Range
Dim ImportMastersFormulaStartAddress As String, MasterDataLedgersStartAddress As String
Dim ImportMastersFormulaStartColumn As String
Dim MastersDataParticularsColumnLetter As String
Dim XML_FileName As String
Dim wsImportMasters As Worksheet, wsListOfLedgers As Worksheet
Dim wsMasterData As Worksheet, wsOriginal As Worksheet
'
UnHideSheets
Set wsImportMasters = Sheets("ImportMasters")
Set wsMasterData = Sheets("MasterData")
'
ImportMastersFormulaStartAddress = "A2" ' <--- Adjust this if needed
ImportMastersFormulaStartColumn = "A" ' <--- Adjust this if needed
ImportMastersFormulaStartRow = 2 ' <--- Adjust this if needed
MasterDataLedgersStartAddress = "B2" ' <--- Adjust this if needed
MastersDataParticularsColumnLetter = "B" ' <--- Adjust this if needed
XML_FileName = "C:\Users\" & Environ("username") & "\Desktop\Master.xml" ' <--- Adjust this if needed
If wsMasterData.Range(MasterDataLedgersStartAddress) = "" Then ' If all ledgers match then ...
MsgBox "Party Ledger Name Not Entered.." ' Display message to user
Exit Sub ' Exit sub
End If
Dim LastColumnNumberInRow As Long
Dim LastRowInSheetImportMasters As Long
Dim LedgerCount As Long
Dim xmlFile As Object
Dim LastColumnLetterSheetImportMasters As String
Dim strData As String
Dim strTempFile As String
'
With wsImportMasters
LastColumnLetterSheetImportMasters = Split(Cells(1, (.Cells.Find("*", , xlFormulas, _
, xlByColumns, xlPrevious).Column)).Address, "$")(1) ' Get last column letter used in ImportMasters sheet
LastRowInSheetImportMasters = .Cells.Find("*", , xlFormulas, , xlByRows, _
xlPrevious).Row ' Get last row used in ImportMasters sheet
'
.Range(ImportMastersFormulaStartColumn & ImportMastersFormulaStartRow + 1 & ":" & _
LastColumnLetterSheetImportMasters & LastRowInSheetImportMasters + 1).ClearContents ' Erase all but one row of formulas on ImportMasters sheet
'
LedgerCount = wsMasterData.Range(MasterDataLedgersStartAddress & ":" & _
MastersDataParticularsColumnLetter & wsMasterData.Range(MastersDataParticularsColumnLetter & _
Rows.Count).End(xlUp).Row).Rows.Count ' Get count of ledgers
'
If LedgerCount > 1 Then .Range(ImportMastersFormulaStartAddress & ":" & _
LastColumnLetterSheetImportMasters & LedgerCount + 1).FillDown ' If LedgerCount > 1 Then Create range of formulas
'
LastColumnNumberInRow = .Cells(ImportMastersFormulaStartRow, _
.Columns.Count).End(xlToLeft).Column ' Get last column # used in row ImportMastersFormulaStartRow
'
.Range(ImportMastersFormulaStartAddress).Resize(LedgerCount, LastColumnNumberInRow).Copy ' Copy ImportMasters Formula results
End With
'
'---------------------------------------------------------------------------------------
'
strData = CreateObject("htmlfile").ParentWindow.ClipboardData.GetData("Text") ' Save contents into strData
strTempFile = XML_FileName ' Set File name to copy to
CreateObject("Scripting.FileSystemObject").CreateTextFile(strTempFile, True).Write strData ' Write the data to file
'
HideSheets
MsgBox ("File saved on Desktop as Master.XML.") ' Display message to user
'
Application.ScreenUpdating = True ' Turn ScreenUpdating back on
End Sub