Delete columns of days in month

[Lukael]

Hello,

I have VBA macro which copies first sheet and creates new sheets for months in a year. Days in a month from 1st sheet are copied in a range of E2:AI2, that's 31 days. When new sheet for new month is created, I want this range of days to be exact as days according to the month, not always 31 days.

Problem is that after range of days there are still columns in range AJ:AN, so

EntireColumn.delete

is not a proper solution.

Can this be done with VBA, or will I must delete every redundant columns on sheets manually ?

Here is code:

Sub DoMonths()
    Dim j As Integer
    Dim k As Integer
    Dim sMo(12) As String
    
    sMo(1) = "Januar"
    sMo(2) = "Februar"
    sMo(3) = "Marec"
    sMo(4) = "April"
    sMo(5) = "Maj"
    sMo(6) = "Junij"
    sMo(7) = "Julij"
    sMo(8) = "Avgust"
    sMo(9) = "September"
    sMo(10) = "Oktober"
    sMo(11) = "November"
    sMo(12) = "December"
        
    Application.ScreenUpdating = False
    For j = 1 To 12
      Sheets(1).Copy after:=Sheets(Sheets.Count)
          With ActiveSheet
          .Name = sMo(j) & " " & .Range("A1").Value
        For k = 1 To Day(DateSerial(.Range("A1").Value, j + 1, 0))
          .Cells(2, 4 + k).Value = DateSerial(.Range("A1").Value, j, k)
        Next k
      End With
    Next j
    Application.ScreenUpdating = True
    Sheets(1).Activate
    
    Application.DisplayAlerts = False
    Sheets(1).Delete
    Application.DisplayAlerts = True

    End Sub

[Norie]

What exactly do you have on the sheet you are copying?

Do you have the 31 days in row 2?

[Lukael]

It's a sheet for monthly working hours. I have userform on it, and some formulas, but when sheet is copied everything is correct as It should be except days dont change. Days are on row 2, starting in E2, ending in AI2.

[Norie]

The code you posted doesn't add any days, the reason there are extra days is because they been copied from the original sheet.

So if you didn't list the days in row 2 on that sheet you would get the correct no of days for each month.

[Lukael]

Ok, I understand that, but how can I change the code so that It will add days correctly, without deleting columns AJ:AN ?

[Norie]

The code already adds the dates correctly, but it does not overwrite the dates that were copied from the original sheet.

If you don't want to remove the dates from the original sheet, which would be the easiest option, you could add code to clear E2:AI2 on each new sheet.

With ActiveSheet
    .Range("E2:AI2").ClearContents
' rest of code

[Lukael]

Ok, thanks, a little better than before, but columns still exist.

So, to delete column wouldn't be easy task ?

[Norie]

I'm not exactly sure what you mean by 'columns'.

The posted code doesn't add any columns, it copies a worksheet, names it after a month and then puts the days of that month in row 2.

Could you attach an example workbook with the sheet you are copying?

[Lukael]

Sorry, I didn't tell - under rows for days I have cells for entering monthly data, so It would be best to delete entire column under specific cell of day.

I'm not at home right now nad I my file is too large to sent (over 1.2 Mb), but I have allready attached sample worksheet in my previous thread " Macro for formula". You can see there and get the idea.

And, if you're really kind - could you take a little look at that too, nobody responds me anymore !

And many thanks for help !!

[Norie]

I can take a look if you can post a link to the thread.

[Lukael]

Ooops, another thing that slipped my mind

Here It is : http://www.excelforum.com/excel-prog...-to-macro.html

[Lukael]

Hey Norie, thanks for help, but I found solution

Although, couldn't done It without your (may I say) solution for stupid idiots like me :

.Range("E2:AI2").ClearContents

I just needed to create code to delete columns where cells within my range E2:AI2 are blank

here's the code, thanks to Erik Van Geit's thread of deleting rows that was easy job for me :

Sub RemoveEmptyRows()
On Error Resume Next
    With Range("E2:AI2")
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
    End With
End Sub

Thanks for help Norie, but please check my thread in link of previous post, I am still struggling with that one !