Home New sheet -> Choose it
Reply: 0

New sheet -> Choose it

user2844
1#
user2844 Published in April 22, 2018, 3:04 am

Sorry for posting a lot of questions, i found out now, what my sub should do.

I want a sub which CountIf a lot of different variables, and save these. Then it should check if a sheet named "Statistics" already exists. If it does, it should ask "Do you want to overwrite" with yes/no. If it does not already exist, it should create it, and put in the information in the "Statistics" sheet.

It's supposed to be in 1 sub, but i made it in 2 for a start to not get confused - But i cant seem to get my Sheet-checker to work.

Is it possible for one of you to put my 2 subs into 1 sub, and maybe explain what i should do?

Thank you so much

Sub1

    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets("Statistics")
 If Not ws Is Nothing Then
MsgBox "A Sheet named 'Statistics' already exists"
Else
MsgBox "'Statistics'-sheet does not already exist"
End If
      If Err.Number = 9 Then
        ans = MsgBox("The file, " & save_name & " exists, do you want to overwite it?", vbYesNo + vbQuestion)
            Select Case ans
        Case vbYes
            'overwrite the file
            wb.SaveAs FileName:=WAD_path & "\" & "WADs " & "Rev " & Rev & "\" & save_name, FileFormat:=51
        Case vbNo
            'exit
            MsgBox "Ensure the data is correct." & vbCrLf & "The process will end.", vbOKOnly + vbExclamation
            Exit Sub
        End Select

        Set ws = Worksheets.Add(After:=Sheets(Worksheets.Count))
        ws.Name = "Statistics"

    End If
    With ws
        'do stuff
    End With
End Sub

Sub2

Sub Opgave3Dim()

'Dim'er de forskellige fakulteter 1-5 som Long
Dim information1 As Long
Dim information2 As Long
Dim information3 As Long
Dim information4 As Long
Dim information5 As Long

'Sætter informationerne til et eller 2 af tallene hvor 2*x = Kandidat, 1*x = Bachelor
information1 = WorksheetFunction.CountIfs(Columns("I"), "1", Columns("K"), "Bachelor")
information11 = WorksheetFunction.CountIfs(Columns("I"), "1", Columns("K"), "Master")
information2 = WorksheetFunction.CountIfs(Columns("I"), "2", Columns("K"), "Bachelor")
information22 = WorksheetFunction.CountIfs(Columns("I"), "2", Columns("K"), "Master")
information3 = WorksheetFunction.CountIfs(Columns("I"), "3", Columns("K"), "Bachelor")
information33 = WorksheetFunction.CountIfs(Columns("I"), "3", Columns("K"), "Master")
information4 = WorksheetFunction.CountIfs(Columns("I"), "4", Columns("K"), "Bachelor")
information44 = WorksheetFunction.CountIfs(Columns("I"), "4", Columns("K"), "Master")
information5 = WorksheetFunction.CountIfs(Columns("I"), "5", Columns("K"), "Bachelor")
information55 = WorksheetFunction.CountIfs(Columns("I"), "5", Columns("K"), "Master")

End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30442 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO