0

First of all, my apologies to the community for bringing the same macro multiple times. I'm just incapable of figuring this out.. I solved one thing and then another aspect (which I don't even think I have touched) stopped working. On the code below, everything works flawless on my Macbook but not so much on my colleagues windows computer. Specifically, it is the:

 "ws.Range("A1") = NextMonth"

found in the "Else" section that seems not to be working. Ironically, it did work earlier but then the sheet name (month) was in the wrong language (another post). For some unreason the latter issue was resolved and then the first-mentioned issue appeared.. such a mess!

The only thing I have done to the sheet that is not visible is changing the format of the A1 cell to "[$-en-GB] MMMM ¯¯¯¯", which is to make sure the month in "A1" appears in english.

Any suggestions?

Regards, Alexander

Sub MonthlyReport()


Dim CurrentMonth As String
Dim CurrentYear As String
Dim NextMonth As String
Dim ws As Worksheet
Dim ExtractMonth As String
Dim ExtractYear As String


Sheets(Sheets.Count).Select
Set ws = ActiveSheet

 If ws.Range("A1") = "" Then

    CurrentMonth = Application.InputBox("Please enter the month", "Report (monthly)")
    CurrentYear = Application.InputBox("Please enter the year", "Report (monthly)")

    Sheets("Template").Visible = True
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Sheets("Template").Visible = False

    Sheets(Sheets.Count).Select
    Set ws = ActiveSheet
    ws.Range("A1") = CurrentMonth & " " & CurrentYear

    ws.Name = CurrentMonth

    ws.Range("C96") = "Average " & CurrentMonth & " " & CurrentYear


Else

    CurrentMonth = ws.Range("A1")
    NextMonth = DateAdd("m", 1, CurrentMonth)


    Sheets("Template").Visible = True
    Sheets("Template").Copy After:=Sheets(Sheets.Count)
    Sheets("Template").Visible = False

    Sheets(Sheets.Count).Select
    Set ws = ActiveSheet

    ws.Range("A1") = NextMonth

    ExtractMonth = Format(NextMonth, "mmmm")
    ExtractYear = Format(NextMonth, "yyyy")
    ws.Name = ExtractMonth

   ws.Range("C96") = "Average " & ExtractMonth & " " & ExtractYear



End If




End Sub
Alexander
  • 41
  • 5
  • 1
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ May 24 '19 at 12:38
  • 2
    Also, when comparing strings, it can be a good idea to use `Trim()` in case of any leading or trailing white spaces... – Mistella May 24 '19 at 12:48
  • 1
    With this: `ws.Range("A1") = "NextMonth"` , try to include `.Text` to ensure you are comparing apples with apples. – Dean May 24 '19 at 13:00
  • Please forgive my incompetence (I basically learned to code through this website), but I'm not successful using .text with that line - "Object required". The trim and link are good advice! – Alexander May 24 '19 at 13:15
  • Is `Option Explicit` specified at the top of the module? Does the code still compile with it? – Mathieu Guindon May 24 '19 at 14:21
  • Since you're using `DateAdd` to compute the next month, `CurrentMonth` should be declared `As Date`; does `Debug.Print TypeName(ws.Range("A1").Value)` output `Date`? – Mathieu Guindon May 24 '19 at 14:23
  • @MathieuGuindon, "option explicit" is not included but the code runs with it (I just added it) – Alexander May 26 '19 at 09:34
  • @MathieuGuindon. That is a great observation! I'll see about this once I get home tonight. Thank you – Alexander May 26 '19 at 09:34
  • @MathieuGuindon. The debug.print... does not work. It gives me an error "object required". – Alexander May 27 '19 at 13:32
  • That doesn't add up. `ws` is declared as a `Worksheet`, and assigned to `ActiveSeet` - it *is* an object. Does the VBE highlight a particular statement when it says "object required"? Are you missing any references? – Mathieu Guindon May 27 '19 at 14:04

0 Answers0