Changing the thousands separator in VBA

B.W.B. · May 18th 2016

Hi,
I'm having trouble with this code:

Code

Sub Macro8_SELECT_S14()
'
' Macro8_SELECT_S14 Macro
'
'
    Sheets("Hights").Select
    ActiveWindow.SmallScroll Down:=9
    Range("G27:G30").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-33
    Range("A1").Select
    Sheets("STICKandTEMP").Select
End Sub
Sub Macro9_SELECT_S13()
'
' Macro9_SELECT_S13 Macro
'
' 
    Sheets("Hights").Select
    ActiveWindow.SmallScroll Down:=9
    Range("F27:F30").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-30
    Range("A1").Select
    Sheets("STICKandTEMP").Select
End Sub

Display More

There are 9 modules like this one.
When i run code to copy data(G27:G30), and go to the other workbook to paste data, paste code in other book:

Code

Private Sub pASTE_SPECIAL()
 On Error GoTo ifERROR
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
 SkipBlanks:=False, Transpose:=True
ifERROR:
 Exit Sub
 End Sub

then i get error in excel turning coma decimal separator(EUROPA) into dot( . ) !!!

[ATTACH=CONFIG]69162[/ATTACH][ATTACH=CONFIG]69163[/ATTACH]

also posted here: https://answers.microsoft.com/…-b478-51a23fd44bf2?page=2

B.W.B. · May 18th 2016

Re: Coma decimal separator turns in dot after Copy code?

More pictures, sorry ozgrid

[Blocked Image: http://i67.tinypic.com/xb9roz.jpg] http://i67.tinypic.com/xb9roz.jpg

[Blocked Image: http://i65.tinypic.com/2yzfuig.jpg] http://i65.tinypic.com/2yzfuig.jpg

B.W.B. · May 20th 2016

Re: Coma decimal separator turns in dot after Copy code?

I don't have Office in my language. Is there a code for preserving regional formats like FMT , i don't know....

B.W.B. · May 20th 2016

Re: Comma decimal separator turns into dot after Copy code?

maybe this insert into sheet :

Code

Imports System.Globalization
 
Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Application.CurrentCulture = New CultureInfo("en-GB")
        'app will use UK regional settings
    End Sub


End Class

Display More

NO no this is for forms. Sorry my bad

Maybe this: Problem with Val, but i don't have any Val in code:)
""The problem is not in the currency datatype, but in the Val function.

You used it completely unnecessary here.

Val expects a string as parameter. VB will convert the value to a string for you, and will use the regional settings for this. If the decimal seperator is a comma, the string will be "23,44"

Val however, is not regional settings aware, and will only recognize a point as decimal seperator. So it will stop after 23.

In this case there is no reason to use Val. Just remove it. It only causes an extra implicit conversion (because the parameter is not a string), an exptra function call (Val is not necessary), and a second implicit conversion (Val returns a double and not a decimal)

Your assigment will be
Curr = 23.44

Or if you want to be precise, you can use the currency declaration character, so no implicit conversion will occur anymore.

Curr = [email protected]""

B.W.B. · May 27th 2016

Re: Comma decimal separator turns into dot after Copy code?

More codes from workbooks, i'm so sorry ozgrid

Code

Sub Add_date_4()
 Dim firstDate As Date, secondDate As Date
 
 With Range("F1")
 .Value = Date
 .NumberFormat = "d.m.yyyy"
 
     If Range("G1").Value = "" Then
        Range("G1").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(OR(R[3]C="""",R[3]C[-3]=""""),TEXT(RC[-1]-1,""d.m.yyyy""),LEFT(R[3]C,LEN(R[3]C)-MIN(FIND({0;1;2;3;4;5;6;7;8;9},R[3]C&""0123456789""))-5))"
                
        Else
        
        firstDate = Range("G1")
        secondDate = DateAdd("d", 1, firstDate)
        Range("D4").Select
        Selection.Value = secondDate & " 800"
        n = Range("D4").Characters.Count - 1
        Range("D4").Characters([n], 2).Font.Superscript = True
        Range("G4").Select
        Selection.Value = secondDate & " 2400"
        n = Range("G4").Characters.Count - 1
        Range("G4").Characters([n], 2).Font.Superscript = True
        Range("N8").Select
    End If
End With
End Sub
Sub Add_date_5()
 Dim firstDate As Date, secondDate As Date
 
     If Range("P7").Value = "" Then
         Range("P7").Select
        ActiveCell.FormulaR1C1 = _
            "=IF(R[1]C="""",TEXT(R[-6]C[-10],""d.m.yyyy""),RIGHT(R[1]C,LEN(R[1]C)-MIN(FIND({0;1;2;3;4;5;6;7;8;9},R[1]C&""0123456789""))+1))"
         Else
        
         firstDate = Range("P7")
         secondDate = DateAdd("d", 1, firstDate)
         Range("P8").Select
         Selection.Value = "Last report was on " & Format(secondDate, "d.m.yyyy")
         Range("A1").Select
    
    End If
End Sub

Display More

Code

Sub Macro1_SORT_LTV()
'
' Macro1_SORT_LTV Macro
'
'
    ActiveWorkbook.Worksheets("LTV").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LTV").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "J2:J12"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("LTV").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

Display More

Code

Sub Macro1_CLEAR_DATA_FROM_REPORT()
'
' Macro1_CDFR Macro
'
'
Dim MSG As String, ANS As Variant
MSG = "  Are you shure to clear data ?"
ANS = MsgBox(MSG, vbExclamation + vbYesNo + vbDefaultButton2, "Achtung baby")
Select Case ANS
    Case vbYes
Application.ScreenUpdating = False
'this copy report on sheet as backup of 14 reports, CountA all cell with whatever value in row four
        Sheets("Report_BACKUP").Select
        Rows("4:4").Select
        If WorksheetFunction.CountA(Cells(4, 4).EntireRow) > 104 Then
            Columns("EB:EB").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Delete Shift:=xlToLeft
            Columns("A:J").Select
            Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Sheets("..........MAIN SHEET").Select
            Range("B2:J109").Select
            ActiveWindow.SmallScroll Down:=-114
            Selection.Copy
            Sheets("Report_BACKUP").Select
            Range("B2").Select
            ActiveSheet.Paste
            Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
            On Error GoTo ifERROR:
            ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
            Selection.Delete
ifERROR:
            Range("H5:J8").Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("..........MAIN SHEET").Select
        Else
            Columns("A:J").Select
            Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Sheets("..........MAIN SHEET").Select
            Range("B2:J109").Select
            ActiveWindow.SmallScroll Down:=-114
            Selection.Copy
            Sheets("Report_BACKUP").Select
            Range("B2").Select
            ActiveSheet.Paste
            Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
            ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
            Selection.Delete
            Range("H5:J8").Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("..........MAIN SHEET").Select
        End If
    'this deletes report from .......MAIN SHEET
        Range("G5:G8").Select
        Selection.Copy
        Range("D5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        ActiveWindow.SmallScroll Down:=6
        Range("B12:F23").Select
        Selection.ClearContents
        Range("H12:J23").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=12
        Range("B25:F34").Select
        Selection.ClearContents
        Range("H25:J34").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=9
        Range("B36:F47").Select
        Selection.ClearContents
        Range("H36:J47").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=12
        Range("B49:F60").Select
        Selection.ClearContents
        Range("H49:J60").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=18
        Range("B64:F73").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=12
        Range("B75:F84").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=12
        Range("B86:F97").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=12
        Range("B99:F108").Select
        Selection.ClearContents
        ActiveWindow.SmallScroll Down:=-117
        Range("A1").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = ""
        Range("A1").Select
    
Application.ScreenUpdating = True
    Case vbNo
    GoTo QUIT:
    End Select
QUIT:
End Sub

Display More

Code

Sub Macro8_SELECT_S14()
'
' Macro8_SELECT_S14 Macro
'
'
    Sheets("Hights").Select
    ActiveWindow.SmallScroll Down:=9
    Range("G27:G30").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-33
    Range("A1").Select
    Sheets("TEMPS").Select
End Sub
Sub Macro9_Select_S13()
'
' Macro9_Select_S13 Macro
'
'
    Sheets("Hights").Select
    ActiveWindow.SmallScroll Down:=9
    Range("F27:F30").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-30
    Range("A1").Select
    Sheets("TEMPS").Select
End Sub

Display More

Workbook 2nd:

Code

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveCell.Column() > 21 Or ActiveCell.Row() > 61 Or ActiveCell.Row() = 1 Then
    GoTo ifERROR
    Else
    Application.ScreenUpdating = True
ifERROR:
    End If
End Sub

Code

Private Sub move_right()
'
' move_right Macro
'
'
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
End Sub
Private Sub move_left()
'
' move_left Macro
'
'
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
End Sub

Display More

Code

Private Sub pASTE_SPECIAL()
 On Error GoTo ifERROR
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
 SkipBlanks:=False, Transpose:=True
ifERROR:
 Exit Sub
 End Sub

Code

Private Sub pRInt_report()
'
' print_report Macro
'
On Error GoTo ifERROR
    Application.ScreenUpdating = False
'this one if selecting out of print range
    If ActiveCell.Column() > 21 Or ActiveCell.Row() > 61 Or ActiveCell.Row() = 1 _
    Or ActiveCell.Value = "" Then
    MsgBox "   WRONG RANGE !" & vbNewLine & " " & vbNewLine & _
    "   OR THERE IS NO PRINT DATA!", 64, ""
    GoTo ifERROR
    Else
    Dim MSG As String, ANS As Variant
    MSG = " DO YOU WANT PRINT REPORT?" & "  " & _
    Range("A" & ActiveCell.Row) & "  " & Range("C" & ActiveCell.Row)
    ANS = MsgBox(MSG, vbQuestion + vbYesNo + vbDefaultButton2, "Print Report")
    Select Case ANS
        Case vbYes
        Range("A" & ActiveCell.Row).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("B" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C6").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("C" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("E" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("D" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("K9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("F" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F16").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("G" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("H14").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("H" & ActiveCell.Row & ":K" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("G12").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("L" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F18").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("M" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F19").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("N" & ActiveCell.Row & ":Q" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("G13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("R" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F17").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("S" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F21").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("T" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F22").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("U" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("H16:J19").Select
        ActiveSheet.Paste
        ActiveWindow.SmallScroll DOWN:=-30
        Range("A1:L30").Select
        Application.CutCopyMode = False
'        In case there is no printing device:
        On Error Resume Next
        Application.Dialogs(xlDialogPrinterSetup).Show
        Selection.PrintOut Preview:=True
        On Error GoTo 0
        Range("H16:J19").Select
        Selection.ClearContents
        Range("F22").Select
        Selection.ClearContents
        Range("F21").Select
        Selection.ClearContents
        Range("F19").Select
        Selection.ClearContents
        Range("F18").Select
        Selection.ClearContents
        Range("F16:F17").Select
        Range("F17").Activate
        Selection.ClearContents
        ActiveWindow.SmallScroll DOWN:=-18
        Range("G12:J14").Select
        Selection.ClearContents
        Range("K9").Select
        Selection.ClearContents
        Range("C9").Select
        Selection.ClearContents
        Range("C7").Select
        Selection.ClearContents
        Range("C6").Select
        Selection.ClearContents
        Range("C4").Select
        Selection.ClearContents
        Sheets("Sheet1").Select
        ActiveWindow.SmallScroll ToLeft:=12
        Range("A1").Select
        Application.ScreenUpdating = True
        Case vbNo
        GoTo QUIT:
        End Select
ifERROR:
End If
Exit Sub
QUIT:
End Sub

Display More

When ERROR occur i was using: Book1 (Macro1_SORT_LTV Macro, Macro8_SELECT_S14 Macro), book2 (Private Sub Workbook_SheetSelectionChange, move_right Macro, move_left Macro and print_report Macro).

Error isn't sudden, it appears after a while(2-3-4 hours)

B.W.B. · Jun 1st 2016

Re: Comma decimal separator turns into dot after Copy code?

[ATTACH=CONFIG]69296[/ATTACH]

i cannot believe, this is BIG BUG in excel!

**S O** · Jun 1st 2016

Re: Comma decimal separator turns into dot after Copy code?

Code

Application.DecimalSeparator = ","

B.W.B. · Jun 1st 2016

Re: Comma decimal separator turns into dot after Copy code?

Where to insert into code? What about =FMT( .... something like that, code?

B.W.B. · Jun 7th 2016

Re: Comma decimal separator turns into dot after Copy code?

Code

Sub Macro8_SELECT_S14()
'
' Macro8_SELECT_S14 Macro
'
'
    Sheets("Hights").Select
    ActiveWindow.SmallScroll Down:=9
    Range("G27:G30").Select
    Selection.Copy
    [B]Application.DecimalSeparator = ","
    Application.UseSystemSeparators = False[/B]
    ActiveWindow.SmallScroll Down:=-33
    Range("A1").Select
    Sheets("STICKandTEMP").Select
End Sub

Display More

I already unchecked File>Options>Advanced> UseSystemProperties. I hope this modified code will not reset setting to default!? So far no error. Also testing w Excel2007. Maybe, problem might be in Office Outlook 2013, i have open three apps in taskbar, Outlook 2013 mail, Excel2013 wb1, and workbook2. Maybe there is some setting in Outlook witch resets mine Excel(File>Options>Advanced>UseSystemSeparator) setting?
Also to mention, range G27:G30(and others SELECTmacros) is General setting with comma decimal separator.
Thanks in advance and for love in Excel.

I forggot to mention, when error was, numerical keyboard comma, was'nt working only inside Office, alphanumerical comma was working normally.

B.W.B. · Jun 9th 2016

Re: Comma decimal separator turns into dot after Copy code?

ERROR again. No help for me here I can't believe, i thought modifying that Copy code will solve ,,, but...

B.W.B. · Jun 16th 2016

Re: Comma decimal separator turns into dot after Copy code?

Maybe someware need to insert Application.EnableEvents=False ?? :smash:

B.W.B. · Jun 22nd 2016

Re: Comma decimal separator turns into dot after Copy code?

I figured out. Problem is in second Workbook print code. After i print report, then i get comma error.
But, question is how to fix it??

Code

Sub pRINT_FUEL_REPORT()
'
' PRINT_FUEL_REPORT Macro
'
On Error GoTo ifERROR
'This is in case if selected out of print range but clicking print in cell("V1")
    If ActiveCell.Column() > 21 Or ActiveCell.Row() > 61 Or ActiveCell.Row() = 1 _
    Or ActiveCell.Value = "" Then
    MsgBox "   W R O N G  R A N G E !" & vbNewLine & " " & vbNewLine & _
    "   OR THERE IS NO PRINTABLE DATA !", 64, ""
    GoTo ifERROR
    Else
    Dim MSG As String, ANS As Variant
    MSG = " DO YOU WANT PRINT REPORT?" & "  " & _
    Range("A" & ActiveCell.Row) & "  " & Range("C" & ActiveCell.Row)
    ANS = MsgBox(MSG, vbQuestion + vbYesNo + vbDefaultButton2, "Print report")
    Select Case ANS
        Case vbYes
        Application.ScreenUpdating = False
        
        Range("A" & ActiveCell.Row).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("B" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C6").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("C" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("E" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("D" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("K9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("F" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F16").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("G" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("H14").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("H" & ActiveCell.Row & ":K" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("G12").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("L" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F18").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("M" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F19").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("N" & ActiveCell.Row & ":Q" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("G13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("R" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F17").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("S" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F21").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("T" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("F22").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet1").Select
        Range("U" & ActiveCell.Row).Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("H16:J19").Select
        ActiveSheet.Paste
        ActiveWindow.SmallScroll DOWN:=-30
        Range("A1:L30").Select
        Application.CutCopyMode = False
        
        Application.ScreenUpdating = True
'        In case there is no printing device:
        On Error Resume Next
        Application.Dialogs(xlDialogPrinterSetup).Show
        Selection.PrintOut Preview:=True
        On Error GoTo 0
        
        Application.ScreenUpdating = False
        Range("H16:J19").Select
        Selection.ClearContents
        Range("F22").Select
        Selection.ClearContents
        Range("F21").Select
        Selection.ClearContents
        Range("F19").Select
        Selection.ClearContents
        Range("F18").Select
        Selection.ClearContents
        Range("F16:F17").Select
        Range("F17").Activate
        Selection.ClearContents
        ActiveWindow.SmallScroll DOWN:=-18
        Range("G12:J14").Select
        Selection.ClearContents
        Range("K9").Select
        Selection.ClearContents
        Range("C9").Select
        Selection.ClearContents
        Range("C7").Select
        Selection.ClearContents
        Range("C6").Select
        Selection.ClearContents
        Range("C4").Select
        Selection.ClearContents
        Sheets("Sheet1").Select
        ActiveWindow.SmallScroll ToLeft:=12
        Range("A1").Select
        
        Application.ScreenUpdating = True
        
        Case vbNo
        GoTo QUIT:
        End Select
ifERROR:
End If
Exit Sub
QUIT:
End Sub

Display More

And this code uncheck my FILE>OPTIONS>ADVANCE setting [ATTACH=CONFIG]69498[/ATTACH]

B.W.B. · Jun 28th 2016

Re: Comma decimal separator turns into dot after Copy code?

SOrry Bump:(

B.W.B. · Jul 8th 2016

Re: Comma decimal separator turns into dot after Copy code?

Please, OzGrid, may I upload workbook.xlsm ? Please.

It is so weird problem. After I print report, I just lose european keyboard layout, numerical side of keyboard, comma goes into dot. But only inside Excel, not Outlook, not inside Windows
And only after printing on paper. When I choose to cancel print in PrintPreview there is no error.

**S O** · Jul 8th 2016

Re: Comma decimal separator dot

No need to ask just upload it

[sw]*[/sw]

B.W.B. · Jul 8th 2016

Re: Comma decimal separator dot

[ATTACH=CONFIG]69619[/ATTACH]

B.W.B. · Jul 12th 2016

Re: Comma decimal separator dot

Please, someone on this beautiful site, maybe i need code to unlock and then lock again sheet? How to insert in Sub, function to unlock and lock sheet? I probably forggot to mention, List2(eng. Sheet2) is password protected sheet. In that sheet i unlocked working cells, but rest of the sheet is locked!

Code

Function UnProtectSheet()  
    'UnProtect Method with a password passed  
        Worksheets("Sheet1").Unprotect ("Vishwa123")  
End Function

NO NO, problem still persist, I unlocked all sheets and tried to print, and error again. Again comma from numerical keyboard turned into dot! AARRRRRGGGHHHHH!!
________________________________________________________________________________________________________________________

I figured out after three months. Problem is in PrintPreview, but WHYYY MICROSOFT, WHY!

Code

'  In case there is no printing device:
         On Error Resume Next
         Application.Dialogs(xlDialogPrinterSetup).Show
         Selection.PrintOut Preview:=True

When i set Selection.PrintOut Preview:=False everything is fine, no error.
But, why! I really need print preview

B.W.B. · Sep 3rd 2016

Re: Comma decimal separator dot

I figured out after three months. Problem is in PrintPreview, but WHYYY MICROSOFT, WHY!

Code

'  In case there is no printing device:
         On Error Resume Next
         Application.Dialogs(xlDialogPrinterSetup).Show
         Selection.PrintOut Preview:=True

When i set Selection.PrintOut Preview:=False everything is fine, no error.
But, why! I really need print preview ;([/QUOTE]

Bump

B.W.B. · Sep 22nd 2016

Re: Changing the thousands separator in VBA

Sorry, bump

[ATTACH=CONFIG]70256[/ATTACH][ATTACH=CONFIG]70257[/ATTACH]http://[Blocked Image: http://s15.postimg.org/493npasw7/Capture_2.jpg] [Blocked Image: http://s15.postimg.org/aa6ejhltz/Capture_3.jpg] [Blocked Image: http://s15.postimg.org/9lxk0jn47/Capture_4.jpg] [Blocked Image: http://s15.postimg.org/kmsp5kfd3/Capture_5.jpg]

B.W.B. · Sep 22nd 2016

Re: Changing the thousands separator in VBA

Also tried with my language(excel), no help for me. Error again. Every time i print on paper there is error, comma turns into point . But when i only PrintPreview and exit(no print on paper) there is no error, all good! Bug in Excel
[ATTACH=CONFIG]70259[/ATTACH]

Changing the thousands separator in VBA

Participate now!

Share