0

I am trying to create a workbook that allows the user to import some sort of master data, import some data you want to compare to the master data, and ultimately do analysis on these data. Both master and imported data could be a workbook (i.e. multiple sheets).

My idea is to group the worksheets into a master sheet array and an imported sheet array, so that I can refer to them in the analysis code. Currently I have managed to create these two arrays in two separate modules (2 click buttons), but then I have no way of using the arrays in a third module.

Is there a way of doing it? or is my thinking wrong to have used this approach in the first place?

the following would be my codes. 

Sub ImportMaster_Click() Dim sImportFile As String, sFile As String, sSheetName As String Dim sThisBk As Workbook Dim wbBk As Workbook Dim wsSht As Worksheet Dim vfilename As Variant Dim Mshtarray() Dim MshtName As String Dim lSheetNumber As Long Dim lshtcount As Long Dim iMshtcount As Integer Dim x As Integer Dim y As Integer 'import data that you want to compare with master data Application.ScreenUpdating = False Application.DisplayAlerts = False Set sThisBk = ActiveWorkbook sImportFile = Application.GetOpenFilename(Title:="Open File") 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 lSheetNumber = wbBk.Worksheets.Count If lSheetNumber > 1 Then x = 0 For iMshtcount = 1 To lSheetNumber x = x + 1 ReDim Preserve Mshtarray(0 To iMshtcount) Mshtarray(x) = wbBk.Sheets(x).Name Next If IsArray(Mshtarray) = True Then For y = 1 To x If Mshtarray(y) <> "Import page" Then lshtcount = sThisBk.Worksheets.Count wbBk.Sheets(Mshtarray(y)).Copy after:=sThisBk.Sheets(lshtcount) End If Next Else MsgBox "Array error" End If sThisBk.Sheets("Import page").Select ElseIf lSheetNumber = 1 Then MshtName = ActiveSheet.Name If SheetExists(MshtName) Then Set wsSht = .Sheets(MshtName) wsSht.Copy after:=sThisBk.Sheets("Import page") Else MsgBox "There is no Sheet with name : in:" & vbCr & .Name End If sThisBk.Sheets("Import page").Select Else MsgBox "Error, no worksheet opened" End If wbBk.Close SaveChanges:=False End With End If Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub Public Function SheetExists(ByVal sWSName As String, Optional wb As Workbook) As Boolean Dim sht As Worksheet If wb Is Nothing Then Set wb = ActiveWorkbook On Error Resume Next Set sht = wb.Sheets(sWSName) On Error GoTo 0 SheetExists = Not sht Is Nothing End Function

The module for importing data is similar to the one above. the problem is the third module that I want to compare the data at.

Sub Reporting_Click() Dim wbBk As Workbook Dim wsSht As Worksheet Dim firstRow As Long Dim lastRow As Long Dim lastvisRow As Long Dim readN3 As Integer Dim maxN3 As Integer Dim shtcount As Integer Dim fltrng As Range Dim a As Long Set wbBk = ActiveWorkbook Set wsSht = ThisWorkbook.Sheets 'vMshtname = ThisWorkbook.Sheets(Module1.Mshtarray(y)) <----- I have error for calling such array here 'vImshtname = ThisWorkbook.Sheets(Module3.Imshtarray(j)) Application.DisplayAlerts = False For Each wsSht In wkbk.Worksheets shtcount = ThisWorkbook.Worksheets.Count Set wsSht = ThisWorkbook.Worksheets <---- problem for setting worksheets as well since I can see wsSht is still nothing after running this line with wsSht a = 2 For a = 2 To a = shtcount <---- ****** If ThisWorkbook.Sheets(a).Name <> "Import page" Then wsSht.AutoFilterMode = False lastRow = wsSht.UsedRange.Rows.Count wsSht.Range("D6").AutoFilter Field:=4, Criteria1:=">=0", Operator:=xlAnd, Criteria2:="<>=" Set fltrng = wsSht.AutoFilter.Range.SpecialCells(xlCellTypeVisible) firstRow = fltrng.Range("E1").End(xlUp).Row lastvisRow = fltrng.Range("E1").End(xlDown).Row readN3 = Application.WorksheetFunction.Max(Range("E" & firstRow, "E" & lastvisRow)) maxN3 = 0 If maxN3 < readN3 Then maxN3 = readN3 End If Else MsgBox "No data available for analysis" End If Next End With

Even when I just want to try the codes within the for loop, VBA can only run to the line with ****** and then directly skipped to end sub. Can somebody help me out here please?

Improve this question
8
  • "Set wsSht = ThisWorkbook.Worksheets" - this line doesn't make any sense (it's a syntax error anyway). What are you trying to do? Which worksheet are you trying to assigh to wsSht? "For a = 2 To a = shtcount" - I think you simply mean "For a = 2 To shtcount", your actual line produces a syntax error. Commented Dec 17, 2018 at 3:54
  • You are opening two other files in your analysis file, right? If that is true the thisworkbook will always point to the analysis workbook and not the two other files. Commented Dec 17, 2018 at 4:43
  • @MichalRosa I was trying to assign a variable as any worksheets within the workbook so that I can run the for loop Commented Dec 17, 2018 at 5:45
  • @Andreas nope the files are copied into the workbook that I have written these macros in. This is why I would like to group the copied worksheets into an array of worksheet names that I can later refer to, but I can't figure out how this can be done. Commented Dec 17, 2018 at 5:46
  • Why are you coping the workbooks? Why not just open them and refer to them as wb1 and wb2. Then wb1.sheets("sheet 1").range("A1").value Commented Dec 17, 2018 at 6:58

1 Answer 1

Reset to default
0

Control Variable Missing (misused)

I've corrected the errors but I don't know what you are trying to do with the arrays. Try to explain.

Option Explicit Sub Reporting_Click() Application.DisplayAlerts = False Dim wbBk As Workbook Dim wsSht As Worksheet Dim firstRow As Long Dim lastRow As Long Dim lastvisRow As Long Dim readN3 As Integer Dim maxN3 As Integer Dim shtcount As Integer Dim fltRng As Range Dim a As Long 'vMshtname = ThisWorkbook.Sheets(Module1.Mshtarray(y)) <----- I have error for calling such array here 'vImshtname = ThisWorkbook.Sheets(Module3.Imshtarray(j)) Set wbBk = ActiveWorkbook ' shtcount = wbBk.Worksheets.Count ' For a = 2 To shtcount ' With wbBk.Worksheets(a) ' ' End With ' Next ' Or For Each wsSht In wbBk.Worksheets With wsSht If .Name <> "Import page" Then .AutoFilterMode = False lastRow = .UsedRange.Rows.Count .Range("D6").AutoFilter Field:=4, Criteria1:=">=0", _ Operator:=xlAnd, Criteria2:="<>=" Set fltRng = .AutoFilter.Range.SpecialCells(xlCellTypeVisible) With fltRng.Range("E1") firstRow = .End(xlUp).Row lastvisRow = .End(xlDown).Row End With readN3 = Application.WorksheetFunction _ .Max(Range("E" & firstRow, "E" & lastvisRow)) maxN3 = 0 If maxN3 < readN3 Then maxN3 = readN3 End If Else MsgBox "No data available for analysis" End If End With Next

End Sub

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@VBasic2008 I need to compare the master worksheets and the imported worksheets. Therefore I would have to refer to the worksheets with their names in the respective arrays, because I was trying to give the flexibility of not needing to know how many sheets the users are importing.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.