Excel Macro Possible Bug

Question

I have made a Macro that performs some repetitive tasks (updates certain data connections and transfers this information to another Excel). The code works perfectly, however, sometimes, it changes the formatting of certain cells, in worksheets, that are not even called in the Macro.

This sounds as a bug. I have previously had the same problem, that was solved by removing the On Error Resume Next line.

Could you please read my code, and see if there is something that could be causing this bug? I really cannot allow to lose my formatting since I am using these sheets for important company reports.

Before running the Macro:

After running the Macro:

Here goes my code:

    Sub TRANSFER_INPUT()
'
' TRANSFER_INPUT Macro
'

Dim MWWorkBook As Workbook
Set MWWorkBook = ActiveWorkbook

Sheets("PAR").Select

Dim Pateka As String
Worksheets("PAR").Activate
Pateka = Range("E5").Value

Dim Datum1 As String
Worksheets("PAR").Activate
Datum1 = Range("E6").Value


Dim InputExcel As Workbook
Workbooks.Open Filename:=Pateka & "INPUT" & Datum1 & ".xlsx", UpdateLinks:=3
Set InputExcel = ActiveWorkbook

'###
'MAIN WORKBOOK / PREFRLANJE FAJLOVI
'###

'INPUTBILANS
MWWorkBook.Activate
Sheets("INPUTBILANS").Select

Range("F11:M1000").Select
Selection.Copy

Range("Y11").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False


'REFRESH BILANS VO INPUT
MWWorkBook.Activate
Sheets("INPUTBILANS").Select

Range("F10").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

'###
'###
'PREFRLI VO INPUT
'###
'###

'Kopiraj Bilans_1

Range("F11:V1000").Select
Selection.Copy

'Pastiraj Bilans_1 VO INPUT / Bilans_1 vo Bilans_2
InputExcel.Activate
Sheets("BILANS_1").Select
Range("B8").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

Range("B8:R1000").Select
Selection.Copy

Sheets("BILANS_2").Select
Range("B8").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False



''' PREFRLI I KOPIRAJ COSTS

MWWorkBook.Activate
Sheets("COSTS").Select

Range("A4").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Range("A5:AV312").Select
Selection.Copy

InputExcel.Activate
Sheets("COSTS").Activate
Range("A5").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

'Kopiraj OPER.COST
MWWorkBook.Activate
Sheets("OPER.COST | NONOP | PRIHODI").Select

Range("D7").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Range("G16:P63").Select
Selection.Copy

InputExcel.Activate
Sheets("OPER.COST").Select
Range("Z4").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

'Kopiraj PRIHODI

MWWorkBook.Activate
Sheets("OPER.COST | NONOP | PRIHODI").Select
Range("D65:F204").Select
Selection.Copy

InputExcel.Activate
Sheets("PRIHODI").Select
Range("B4").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

'Kopiraj NONOP

MWWorkBook.Activate
Sheets("OPER.COST | NONOP | PRIHODI").Select
Range("F8:F14").Select
Selection.Copy

InputExcel.Activate
Sheets("NONOP").Select
Range("D5").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Application.CutCopyMode = False

End Sub

Thank you so much!

You are using a lot of `.Select` and `Selection.`... You might want to take a look at this answer: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Vincent G, Mar 08 '18 at 08:18
As VincentG said I also suggest to get rid of all those `.Activate`, `.Select` and `Selection.` to make your code more safe. This can cause many unpredictable issues. **After that if that didn't solve your issue:** please explain in detail what "*changes the formatting of certain cells*" actually means. What changes? Number format? Background color? A screenshot of when it failed might help too, so we can compare it to the correct result. — Pᴇʜ, Mar 08 '18 at 08:57
Thank you so much for your replies and help. I have attached two "screenshots" - you can check my question, right before the code itself. It kinda moves the numbers to the left, and if the column width is too small, then it shows "###" in certain cells. And I really have million numbers :/ — Maksim Maksimović, Mar 08 '18 at 10:53
Please answer: (1) What format / number format has the source cell. (2) What format / number format has the target cell after you pasted the values. (3) What do all these little green corners say (after paste). (4) did you tidy up your code as we suggested? — Pᴇʜ, Mar 08 '18 at 11:11
To make sure you never ever activate the wrong sheets, I would suggest to use always complete notation. It's more precise to type something like `Thisworkbook.Worksheets("SheetName").Range("A1")` rather than just `Range("A1")`. Modify all your code and if the same problem persists, then check the code of events that may be triggering and changing your cells. Also, debugging with key F8 will allow you to check what your code does step by step and you will be able to locate what lines change the format of those cells you don't want to change. — Foxfire And Burns And Burns, Mar 08 '18 at 11:43
@FoxfireAndBurnsAndBurns, I modified my code with your suggestions. I will test it for some time, and then let you know if it has fixed my problem. Thanks everyone for your input! — Maksim Maksimović, Mar 08 '18 at 13:41

Excel Macro Possible Bug

0 Answers0