Help to finish my vba code (export sheet and etc)

[SimbadS]

Hi again!

Im working on a code and i have come far but i want to perfect it so the code does the work from start to end.
So far what i do is unhide a sheet, copy it to a new woorkbook, then select all cells in the sheet (in new workbook) and eliminate formulas.
So far so good. It does the job.
here is the code

Sub Copy_to_CSV()
'
' Copy_to_CSV Makro
'

'
    Sheets("Mainpage").Select
    Sheets("mysheet").Visible = True
    Sheets("mysheet").Select
    Sheets("mysheet").Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter
    Selection.AutoFilter
    Rows("1:1").Select
    Selection.AutoFilter
    Range("B9").Select
    Windows("Mywoorkbook.xlsm").Activate
    Sheets("mysheet").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Mainpage").Select
End Sub

This is the whole job that i want done:
unhide sheet, copy it to a new workbook and ASK "where to save?"(this can come later or before, does not matter when)
Then, kill all formulas.
Next step is collum "AG:AG" to change from "," to "." .
Last step is in collum "A:A" to delete empty rows.
Last step here is because i have empty slots in example row A5, A10, A30, etc, IF "empty" delete entire row, if "X" stay.

Thank you!

 

[JLGWhiz]

See if this works for you

Sub Copy_to_CSV()
'
' Copy_to_CSV Makro    
    Sheets("mysheet").Visible = True    
    Sheets("mysheet").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Celete
    End With    
    Columns("AG").Replace(",", ".")    
    Windows("Mywoorkbook.xlsm").Activate
    Sheets("mysheet").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Mainpage").Select
End Sub

This one has the destination path input.

Sub Copy_to_CSV()
'
' Copy_to_CSV Makro 
Dim savDest As string   
    Sheets("mysheet").Visible = True    
    Sheets("mysheet").Copy
    With ActiveSheet.UsedRange
        .Value = .Value
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Celete
    End With    
    Columns("AG").Replace(",", ".")  
    savDest = InputBox("Enter the Directory Path to save to.", "PATH TO SAVE"  
    'ActiveWorkbook.SaveAs(savDest & "<enter file name and extension here>" )
    Windows("Mywoorkbook.xlsm").Activate
    Sheets("mysheet").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Mainpage").Select
End Sub

 

[SimbadS]

Hi!
It seems that i get a problem here:

 

[JLGWhiz]

Yep, need to remove the parentheses since no equal symbol is used. Also, noted I misspelled Delete above that.

With ActiveSheet.UsedRange
        .Value = .Value
        .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    Columns("AG").Replace ",", "."
    Windows("Mywoorkbook.xlsm").Activate

 

[SimbadS]

Like a charm again
Thank you

 

[SimbadS]

There is one issue that vba does not solve. Im not sure why and it is that change from , to .
Here is a screenshot from exportsheet

When i do it manually afterwards it works. (mark all and search and replace).

Any suggestions?

 

[JLGWhiz]

Can you be morfe specific in definining your issue?

 

[SimbadS]

This sheet is for import to accounting system. And the system only understands . and not ,
Therefore i need to change all , to . before import.
Collum with numbers is AG.

 

[JLGWhiz]

It looks like you might have commas from different font sets if the 'Replace' function is working for some but not others. I cannot test that theory from the screen shot you posted, If all the commas were ASCII CHAR(44) then they should all be replaced with the period symbol. I am not knowledgeable enough about the different character codes to offer a way to remedy this condition. Maybe someone else will pick up on the tread and offer a solution. This condition can occur if data is downloaded from the internet where different character codes are used.

 

[Jimmypop]

Not sure if this will work for you but it does for me...(tested on different fonts)

My macro will automatically select a range where I want to replace a comma.

After the selection I call the following macro:

Sub ReplaceComma()
Application.Goto Reference:="ReplaceComma"
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Let know if it works