0

I am creating a macro to create invoices from a template and filling the details with data table.

UPDATE: I have found the issue. The code is not the problem. I was trying the code in step into mode every time and excel was executing the whole code after the FileCopy step automatically. I added a msgbox step and a application wait step to slow down the process. That's when I realized that the whole code is getting executed. Can someone help me figure out why that is happening even in debug mode?

Below is the code I have until now. It worked fine a couple of times. But now it suddenly ends at line where the copy of the template is saved in the selected folder from the FileDialog picker. Can't figure out what is going wrong. No errors show up while executing.

Option Explicit

Sub MMRetCI()

'select destination folder
Dim Dfolder As FileDialog
Dim DFolderPath As String

Set Dfolder = Application.FileDialog(msoFileDialogFolderPicker)

With Dfolder
    .Title = "Select Destination Folder"
    .AllowMultiSelect = False
    .Show
    DFolderPath = .SelectedItems(1) & "\"
    Debug.Print DFolderPath
End With

'Create copy of template
Dim strtemplatename As String
Dim UserName As String
strtemplatename = "MM_Returns CI_Template.xlsm"

    FileCopy "Location of Template" & strtemplatename, DFolderPath & strtemplatename 'Location of template is dummy. I have an actual path there. The code suddenly ends after this line.

'Turn Filter on
With Workbooks("Returns for pre-Brexit orders_Template.xlsm")
If Not Sheets("RawData").AutoFilterMode Then
    Sheets("RawData").Range("A1").AutoFilter
  End If
End With

'Get all order IDs in array
Dim WSRaw As Worksheet
Dim OrderCount As Double
Set WSRaw = Workbooks("Returns for pre-Brexit orders_Template.xlsm").Sheets("RawData")

    With WSRaw
        OrderCount = Range("Z2", .Range("Z2").End(xlDown)).Rows.Count + 1
    End With

Dim OrderArrayAddress As Range
Dim ArrayOfOrders() As Variant
Dim i As Double
    
    ArrayOfOrders = Sheets("RawData").Range("Z2").CurrentRegion.Value
    
 'Write order number to filter cell
    With Sheets("CI Details")
    For i = 2 To OrderCount
    Range("B1").Formula = ArrayOfOrders(i, 1)
    
    'More operations here
    
    Next
    End With
End Sub
Achal Desai
  • 93
  • 1
  • 8
  • Just a shot in the dark: did you try with shorter simpler paths, with no spaces, or both source and destination? Did you double check the whole path is correct? Is the file huge? – Andrew Jan 26 '21 at 19:11
  • Also put a breakpoint at the line `With Workbooks("Returns for pre-Brexit orders_Template.xlsm")` and see if the code reaches there. And if it does then use F8 to step through the code. – Siddharth Rout Jan 26 '21 at 19:15
  • Are you running this directly or does it get called from some other code? FYI if your code is running inside of `Workbooks("Returns for pre-Brexit orders_Template.xlsm")` then you can replace that with `ThisWorkbook` – Tim Williams Jan 26 '21 at 20:01
  • Try setting these values to variables above the FileCopy line, put a breakpoint there, and check the resulting path: "Location of Template" & strtemplatename, DFolderPath & strtemplatename – Jeremy Hodge Jan 26 '21 at 20:27
  • @Andrew, Yes I double checked everything. When I hardcode the paths, the code works. But when I use the FileDialog, the code ends every time after FileCopy command. – Achal Desai Jan 27 '21 at 11:56
  • @SiddharthRout, Putting a breakpoint works. The code runs correctly every time when a breakpoint is there at workbook activate line. Then I can step through rest of the steps. But once the breakpoint is removed, the same thing happens. Code suddenly stops after FileCopy line. – Achal Desai Jan 27 '21 at 11:58
  • @TimWilliams I tried this workbook as well at your suggestion. Same result. – Achal Desai Jan 27 '21 at 12:00
  • @JeremyHodge Tried setting variables. Printed them in immediate window. Both paths are correct. The file gets copied. But the code ends after that. Inserting a breakpoint allows the code to move to the next line and step through rest of the code. But if the breakpoint is removed, the code ends after the copy of the file is created. – Achal Desai Jan 27 '21 at 12:02
  • Add this line line `DoEvents` before `With Workbooks("Returns for pre-Brexit orders_Template.xlsm")` and try again? – Siddharth Rout Jan 27 '21 at 12:15
  • Are you calling this code directly or from another method (and does the other method have an error handler)? – Tim Williams Jan 27 '21 at 16:14

0 Answers0