EXCEL VBA does not work properly

[kraaniks]

Hi,
I have a macro which filters the results based on variables and then copies the filtered results and pastes into another destination file.

The issue I am having is that when I autoplay this macro (F5) it does not paste all copied/filtered values into the destination sheet, however if I walk through the Macro with manual processing (F8) then it captures all the values and paste them correctly in the destination file. Usually there are like 5 lines missing if I use autoplay.

Does somebody know why am I having this isssue and how can it be overcome?

Thanks
Ed

 

[James006]

Hello,

Why don't you post your macro ...:wink:

Saying that ' VBA does not work properly ...' is very surprising ...

Most probably your code needs to be fully adapted to your worksheet ...

 

[kraaniks]

Hi, this is the code I am using:

Sub Update_report()
        
For x = 1 To 12
 
Select Case x
    Case 1
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3625 Security and LAN\CCR_3625.xlsx"
    Destination_CC = "CCR_3625"
    GoTo Report
   
    Case 2
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3641 IP Access Planning\CCR_3641.xlsx"
    Destination_CC = "CCR_3641"
    GoTo Report
   
    Case 3
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3642 IP Access Config and Ops\CCR_3642.xlsx"
    Destination_CC = "CCR_3642"
    GoTo Report
   
    Case 4
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3643 Fiber\CCR_3643.xlsx"
    Destination_CC = "CCR_3643"
    GoTo Report
   
    Case 5
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3663 Data Centre\CCR_3663.xlsx"
    Destination_CC = "CCR_3663"
    GoTo Report
   
    Case 6
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3671 IP Core\CCR_3671.xlsx"
    Destination_CC = "CCR_3671"
    GoTo Report
  
    Case 7
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3682 IP Access Design\CCR_3682.xlsx"
    Destination_CC = "CCR_3682"
    GoTo Report
   
    Case 8
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3683 Optical\CCR_3683.xlsx"
    Destination_CC = "CCR_3683"
    GoTo Report
  
    Case 9
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3685 Mobile Access Sweden\CCR_3685.xlsx"
    Destination_CC = "CCR_3685"
    GoTo Report
   
    Case 10
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3866, 3867 IT Infra SE NL\CCR_3866_3867.xlsx"
    Destination_CC = "CCR_3866_3867"
    GoTo Report
   
    Case 11
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\3896 B2B Cloud\CCR_3896.xlsx"
    Destination_CC = "CCR_3896"
    GoTo Report
  
    Case 12
    Destination_DIR = "Y:\Infrastructure\LT\Finance\Monthly follow up reports 2018\00_Database Controller Use Only\CCR_mngt.xlsx"
    Destination_CC = "CCR_mngt"
    GoTo Report
  
Report:
  
Sheets(1).Select
       
            'count number of rows source
            K = ActiveSheet.Range("A1", ActiveSheet.Range("A1").End(xlDown)).Rows.Count
       
            If Not ActiveSheet.AutoFilterMode Then
            ActiveSheet.Range("A1:AK" & K).AutoFilter
            End If
       
            Workbooks.Open Filename:=Destination_DIR
            Workbooks(Destination_CC & ".xlsx").Sheets("Data").Select
       
            'count number of rows destination
            I = ActiveSheet.Range("A1", ActiveSheet.Range("A1").End(xlDown)).Rows.Count
       
            Worksheets("Data").Range("A2:AK" & I).Clear
       
            Workbooks("Database Transport Infra 2018.xlsm").Activate
            Sheets("Data").Range("A1:AK" & K).AutoFilter Field:=34, Criteria1:=Destination_CC, Operator:=xlFilterValues
            Range("A1:AK" & K).Select
            Range("AH1").Activate
            Selection.Copy
       
            Workbooks(Destination_CC & ".xlsx").Sheets("Data").Activate
            Range("A1").Select
            ActiveSheet.Paste
       
            Sheets("Pivot Actuals").Select
            ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
            Sheets("Personnel costs").Select
            ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
            Sheets("YTD vs BU vs F2").Select
            ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
            Sheets("F2 P8 account check").Select
            ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
            Sheets("Capex").Select
            ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
       
            Sheets(1).Select
            ActiveWorkbook.Save
            ActiveWorkbook.Close
  
End Select
 
Next x
 
MsgBox "Report done"
 
End Sub