0

I have created a couple of macros and I have a problem (sometimes) when I have several Excel files open. For some reason the macros stop working (VBA Runtime error 1004). I suspect that I have to declare workbook/worksheet properly, but I have yet to understand how. With my basic understand I thought the following would work (not in my code, I tried it and it didn't work): `

Dim wb as WorkBook
Dim ws as WorkSheet
Set wb = ActiveWorkbook
Set ws = ActiveWorkSheet
wb.ws.range("A1") = 2

`

However, since the above is not working for me I guess I'm missing some useful/valuable knowledge.

If useful: the macros are used for creating monthly report. Hence they are initially empty and then create a sheet for January, February and so forth. 

Sub ConsultantInvoicing()

    Dim CurrentMonth As String
    Dim NextMonth As String

    'Dim wb As Workbook
    'Set wb = ActiveWorkbook
    'Dim wsActiveSheet As Worksheet
    'Set wsActiveSheet = wb.ActiveSheet

    Sheets(Sheets.Count).Select

    If Range("A1") = "" Then
        Call AddFirstSheet
    Else
        Call AdditonalSheet

    End If


End Sub



Private Sub AddFirstSheet()

    CurrentMonth = Application.InputBox("Please enter what month", 
"Invoice (monthly) - What month?")
    ActiveSheet.Name = CurrentMonth
    Call NewSheetFormat


End Sub


Private Sub AdditonalSheet()

    CurrentMonth = Range("A1")

    Select Case CurrentMonth

        Case "January", "january"
            NextMonth = "February"

        Case "February", "february"
            NextMonth = "March"

        Case "March", "march"
            NextMonth = "April"

        Case "April", "april"
            NextMonth = "May"

        Case "May", "may"
            NextMonth = "June"

        Case "June", "june"
            NextMonth = "July"

        Case "July", "july"
            NextMonth = "August"

        Case "August", "august"
            NextMonth = "September"

        Case "September", "september"
            NextMonth = "October"

        Case "October", "october"
            NextMonth = "November"

        Case "November", "november"
            v = "December"

        Case "December", "december"
            NextMonth = "January"

    End Select

    Set ws = ThisWorkbook.Sheets.Add _
    (After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ActiveSheet.Name = NextMonth
    ActiveSheet.Range("A1") = NextMonth

    Call NewSheetFormat


End Sub


Private Sub NewSheetFormat()
 'formats the sheet


    Range("A:A").ColumnWidth = 29.17
    Range("ColumnWidth").ColumnWidth = 12.5
    ..
    ..




End Sub

Thanks in advance! Regards, Alexander

Alexander
  • 41
  • 5
  • Start here: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and maybe also https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 – Tim Williams May 20 '19 at 16:08
  • 1
    If you're using `ws` - this is already qualified by the `ActiveWorkSheet`. There is no need to `wb.ws.range..` - just use `ws.range("A1") = 2` – CLR May 20 '19 at 16:10
  • 1
    Also, your whole `Select Case....NextMonth = ` could be replaced with `NextMonth = Format(DateSerial(Year(Date), Month(DateValue("1/" & Range("A1") & "/" & Year(Date))) + 1, 1), "mmmm")` – Tom May 20 '19 at 16:24
  • 1
    For example you have a worksheet name "Main", I always declare workbook and worksheet this way, 1.) Dim mySh as Worksheet 2.) Set mySh = ThisworkBook.Sheets("Main"). Then everytime you use .Range or .Cell use it this way --> mySh.range("A2").value = "Something" or mySh.cell(1, 2).value = "". Using thisworkbook code secure that your program will not interfere with other open excel application. – Love Coding May 20 '19 at 16:43
  • This is great, thank you all so much! Really neat with the alternative to my case also, I'll have to look that up a little more to learn it:) – Alexander May 20 '19 at 17:30

0 Answers0