Macro needs help

[Kyrre]

I have a sheet to sum expences and hours on projects, and use a number of macros in it.
It is one macro that is unfinished, is there any superheroes who could fix a better one? It all in the sheet (I hope)
Thanks

The file was to large, so dropbox: https://www.dropbox.com/s/6edg39wawzv9srh/MACRO_needs_help.xlsm

 

[NARAYANK991]

Hi ,

As far as the simpler requirement is concerned , the code is as shown below :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
        Application.StatusBar = "This automation tool is prepared by 'Surendra (Sunny Singh)' on 28/Sep/2013. Please contact at 'xxxxxxxxxxx@xxxxxxx.xxx' for any further assistance."
        ThisWorkbook.Save
        MsgBox "This Excel-VBA automation project is made for 'Kyrre Sandvik'." & vbNewLine & vbNewLine & _
                "by Surendra (Sunny Singh)" & vbNewLine & _
                "Microsoft Certified Excel" & Chr(174) & " 2010 (MCP)", vbInformation, "Thank you!"
    On Error GoTo 0
End Sub

The offending statements are the following :

Application.StatusBar = "This automation tool is prepared by 'Surendra (Sunny Singh)' on 28/Sep/2013. Please contact at 'xxxxxxxxxxx@xxxxxxx.xxx' for any further assistance."

MsgBox "This Excel-VBA automation project is made for 'Kyrre Sandvik'." & vbNewLine & vbNewLine & _
"by Surendra (Sunny Singh)" & vbNewLine & _
"Microsoft Certified Excel" & Chr(174) & " 2010 (MCP)", vbInformation, "Thank you!"

Just remove these two statements , and you should no longer get the popup when closing the file.

Narayan

 

[dan_l]

from now on all of my code will show that message box......lol

 

[Kyrre]

Hi ,

As far as the simpler requirement is concerned , the code is as shown below :

Narayan

Thank you. I tried to remove, but I think I messed it up. I have to look into it later. Do U have a trick for that "gjør om" button as well?

 

[Kyrre]

I found some hidden sheets, but the size is still way over the top..,,.

 

[Luke M]

Hi Kyrre,
It was the one picture at the top of the TIMELISTE worksheet that was causing the extra 900kb in file size. I've taken your file and "put it on a diet" by removing all the old named ranges that contained errors, implemented a XL table, and got rid of unused/unwanted code. The new macro "DoItAll" will copy data from Ask1 to CSV, do the Text-to-Columns, and then transfer to TIMELISTE worksheet. You also still have the individual buttons, if that is needed.

Apologies if I butchered something up with the language.

 

[Kyrre]

The picture How stupid of me.,., Yes it is often the small things that crashes things up.
The macro was exactly what i was looking for- almost And I can see that it is my mistake.

The macro must run from no.4, not from no 1 as I have by mistake asked for.

I get csv from another sheet- so I have to copy and paste the csv into csv-tab for further prosessing( change name in dropdown)

Is it poosible to cange that? I am very glad for this help

 

[Luke M]

Easy enough. If you look at the macro, there's 3 sections. The first section is the transfer from Ark1 to CSV, but it sounds like we don't need that.
Current code:

Sub DoItAll()
Dim lastRow As Long

Application.ScreenUpdating = False

'TRANSFER INFO FROM ARK1 TO CSV
'Clear out previous data
Call NullStill_New
With Worksheets("Ark1")
    'Find the last row of our data on Ark1 sheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "Please paste data into row 2 below.", vbOKOnly, "No Data"
        GoTo escapeClause
    End If
    'Copy the data to CSV sheet
    .Range("A2:A" & lastRow).Copy Worksheets("CSV").Range("B4")
End With

'SPLIT THE DATA INTO CORRECT CELLS
'Use our existing macro to split the data
Call Csv_til_excel_New


'TRANSFER TO MAIN TABLE
With Worksheets("CSV")
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    .Range("A4:F" & lastRow).Copy
End With
With WshTimeListFromCSV
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
End With
   

escapeClause:
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

And based on what you just said, remove the first bit

Sub DoItAll()
Dim lastRow As Long

Application.ScreenUpdating = False

'This section is what the "Gjør om" button calls.
'SPLIT THE DATA INTO CORRECT CELLS
'Use our existing macro to split the data
Call Csv_til_excel_New

'TRANSFER TO MAIN TABLE
With Worksheets("CSV")
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    .Range("A4:F" & lastRow).Copy
End With
With WshTimeListFromCSV
    .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
End With
   

escapeClause:
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Kyrre]

I will look into this, and try to figure it out (how the code works, not just use it)
I am very greatful, you are awesome!

 

[Kyrre]

Finally I got it done, combined a bit and its working! Great!

 

[Kyrre]

However, I cant find the macro with this text"Application.StatusBar = "This automation tool is prepared by 'Surendra (Sunny Singh)' on 28/Sep/2013. Please contact at 'xxxxx",....,
I open Macro, step in it and look around. cant find it. Do I need glasses?

 

[Luke M]

I believe I removed it from the workbook I posted it. If it's still there, it would be in the ThisWorkbook module (look in the VBE explorer window). You can also use Ctrl+f (find dialogue) and search the entire project for "Surendra". But again, I'm 99% sure I already removed it.