Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

Please help i dont know why is my vba code not working Sub Delivery_Schedule_XML

ID: 3799271 • Letter: P

Question

Please help i dont know why is my vba code not working

Sub Delivery_Schedule_XML()

'turn off screen updating to avoid flicker effect of copy/paste
On Error GoTo errHandler
Application.ScreenUpdating = False

Dim current As Range
Dim date_stamp As String
Dim i, j As Integer
Dim save_path As String
Dim savename As String

date_stamp = Format(Date, "mm-dd-yyyy")
'save_path = "C:UsersmmoschetDesktopTariff AutomationLTL"
savename = date_stamp & "_" & "Delivery_Schedule" & ".xml"
Set current = Range("F" & 2)

'********************************************************************************************
'DEFAULT DESKTOP SAVE LOCATIONS
Dim user_path As String
user_path = CreateObject("WScript.Shell").specialfolders("Desktop") & "Tariff Automation"
reference_path = user_path & "Reference Files"
save_path = user_path
'********************************************************************************************

'********************************************************************************************
'TO CHANGE THE DIRECTORY FOR SAVING XMLS AND REFERENCE FOLDER
'save_path = "INSERT COPIED PATH HERE" & ""
'reference_path = "INSERT COPIED REFERENCE PATH HERE" & ""
'********************************************************************************************

i = 2
j = 2

'Delivery schedule xml file creation
Set NewBook = Workbooks.Add
    With NewBook
    .Title = "xml 1"
    .SaveAs FileName:=save_path & savename, FileFormat:=xlXMLSpreadsheet
    End With
   
'Activate delivery schedule xml file and format to text (@)
Workbooks(savename).Activate
ActiveWorkbook.ActiveSheet.Cells.NumberFormat = "@"
ActiveSheet.Name = "Data"
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "Info"
ActiveWorkbook.ActiveSheet.Cells.NumberFormat = "@"
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "Mapping"
ActiveWorkbook.ActiveSheet.Cells.NumberFormat = "@"
Worksheets("Data").Activate

'Reactivate master file
Workbooks("LTL Requests Log.xlsm").Activate

'Copy and paste the static information for delivery schedule xml
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("A2:N2").Copy Workbooks(savename).Worksheets("Data").Range("A1")
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("A7:B11").Copy Workbooks(savename).Worksheets("Info").Range("A1")
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("A14:C31").Copy Workbooks(savename).Worksheets("Mapping").Range("A1")

'Start copying in relevant data
While current.Value <> ""

    'Column A
    Workbooks(savename).Worksheets("Data").Range("A" & i).Value = "H"
    Workbooks(savename).Worksheets("Data").Range("A" & i + 1).Value = "D"
   
    'Columns E, F, & J
    Workbooks(savename).Worksheets("Data").Range("E" & i).Value = current.Value
    Workbooks(savename).Worksheets("Data").Range("F" & i).Value = current.Value
    Workbooks(savename).Worksheets("Data").Range("J" & i).Value = current.Value
    Workbooks(savename).Worksheets("Data").Range("J" & i + 1).Value = current.Value
   
    'Columns C & I
    Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" & j).Value = "=CONCATENATE(""LTL"",B" & j & ")"
    Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" & j).Copy
    Workbooks(savename).Worksheets("Data").Range("C" & i).PasteSpecial xlPasteValues
    Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" & j).Copy
    Workbooks(savename).Worksheets("Data").Range("I" & i).PasteSpecial xlPasteValues
    Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" & j).Copy
    Workbooks(savename).Worksheets("Data").Range("I" & i + 1).PasteSpecial xlPasteValues
    Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" & j).Value = ""
   
    'Column G
    If current.Offset(0, 2).Value = "NONE" Then
        Workbooks(savename).Worksheets("Data").Range("G" & i).Value = ""
    Else
        Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" & j).Value = "=CONCATENATE(""LTL-"",H" & j & ",""DAY"")"
        Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" & j).Copy
        Workbooks(savename).Worksheets("Data").Range("G" & i).PasteSpecial xlPasteValues
        Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" & j).Value = ""
    End If
   
    'Columns K, M, & L
    current.Offset(0, -3).Copy
    Workbooks(savename).Worksheets("Data").Range("K" & i).PasteSpecial xlPasteValues
    current.Offset(0, -3).Copy
    Workbooks(savename).Worksheets("Data").Range("M" & i).PasteSpecial xlPasteValues
   
    current.Offset(0, -2).Copy
    Workbooks(savename).Worksheets("Data").Range("K" & i + 1).PasteSpecial xlPasteValues
    current.Offset(0, -2).Copy
    Workbooks(savename).Worksheets("Data").Range("M" & i + 1).PasteSpecial xlPasteValues
   
    Workbooks(savename).Worksheets("Data").Range("L" & i).Value = "1"
    Workbooks(savename).Worksheets("Data").Range("L" & i + 1).Value = "2"
   
    'Columns D, H, & N
    Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("D4").Copy Workbooks(savename).Worksheets("Data").Range("D" & i)
    Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("H4").Copy Workbooks(savename).Worksheets("Data").Range("H" & i)
    Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("N4").Copy Workbooks(savename).Worksheets("Data").Range("N" & i)
    Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("N4").Copy Workbooks(savename).Worksheets("Data").Range("N" & i + 1)
   
    i = i + 2
    j = j + 1
    Set current = current.Offset(1, 0)
   
Wend

'Activate xml file and format to text (@)
Workbooks(savename).Activate
ActiveWorkbook.ActiveSheet.Cells.NumberFormat = "@"

'Error handling for screen updating
Application.ScreenUpdating = True
errHandler:
    Application.ScreenUpdating = True

End Sub

Explanation / Answer

I hope the below code will help u...

Sub Delivery_Schedule_XML()
'turn off screen change to avoid flicker result of copy/paste
On Error GoTo errHandler
Application.ScreenUpdating = False
Dim current As vary
Dim date As String
Dim i, j As number
Dim save_path As String
Dim savename As String
date_stamp = Format(Date, "mm-dd-yyyy")
'save_path = "C:UsersmmoschetDesktopTariff AutomationLTL"
savename = date &amp; "_" &amp; "Delivery_Schedule" &amp; ".xml"
Set current = Range("F" &amp; 2)
'********************************************************************************************
'DEFAULT DESKTOP SAVE LOCATIONS
Dim user_path As String
user_path = CreateObject("WScript.Shell").specialfolders("Desktop") &amp; "Tariff Automation"
reference_path = user_path &amp; "Reference Files"
save_path = user_path
'********************************************************************************************
'********************************************************************************************
'TO CHANGE THE DIRECTORY FOR SAVING XMLS AND REFERENCE FOLDER
'save_path = "INSERT COPIED PATH HERE" &amp; ""
'reference_path = "INSERT COPIED REFERENCE PATH HERE" &amp; ""
'********************************************************************************************
i = 2
j = 2
'Delivery schedule xml file creation
Set NewBook = Workbooks.Add
With NewBook
.Title = "xml 1"
.SaveAs FileName:=save_path &amp; savename, FileFormat:=xlXMLSpreadsheet
End With

'Activate delivery schedule xml file and format to text (@)
Workbooks(savename).Activate
ActiveWorkbook.ActiveSheet.Cells.NumberFormat = "@"
ActiveSheet.Name = "Data"
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "Info"
ActiveWorkbook.ActiveSheet.Cells.NumberFormat = "@"
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "Mapping"
ActiveWorkbook.ActiveSheet.Cells.NumberFormat = "@"
Worksheets("Data").Activate
'Reactivate master file
Workbooks("LTL Requests Log.xlsm").Activate
'Copy and paste the static information for delivery schedule xml
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("A2:N2").Copy Workbooks(savename).Worksheets("Data").Range("A1")
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("A7:B11").Copy Workbooks(savename).Worksheets("Info").Range("A1")
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("A14:C31").Copy Workbooks(savename).Worksheets("Mapping").Range("A1")
'Start copying in relevant data
While current.Value &lt;&gt; ""
'Column A
Workbooks(savename).Worksheets("Data").Range("A" &amp; i).Value = "H"
Workbooks(savename).Worksheets("Data").Range("A" &amp; i + 1).Value = "D"

'Columns E, F, &amp; J
Workbooks(savename).Worksheets("Data").Range("E" &amp; i).Value = current.Value
Workbooks(savename).Worksheets("Data").Range("F" &amp; i).Value = current.Value
Workbooks(savename).Worksheets("Data").Range("J" &amp; i).Value = current.Value
Workbooks(savename).Worksheets("Data").Range("J" &amp; i + 1).Value = current.Value

'Columns C &amp; I
Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" &amp; j).Value = "=CONCATENATE(""LTL"",B" &amp; j &amp; ")"
Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" &amp; j).Copy
Workbooks(savename).Worksheets("Data").Range("C" &amp; i).PasteSpecial xlPasteValues
Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" &amp; j).Copy
Workbooks(savename).Worksheets("Data").Range("I" &amp; i).PasteSpecial xlPasteValues
Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" &amp; j).Copy
Workbooks(savename).Worksheets("Data").Range("I" &amp; i + 1).PasteSpecial xlPasteValues
Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" &amp; j).Value = ""

'Column G
If current.Offset(0, 2).Value = "NONE" Then
Workbooks(savename).Worksheets("Data").Range("G" &amp; i).Value = ""
Else
Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" &amp; j).Value = "=CONCATENATE(""LTL-"",H" &amp; j &amp; ",""DAY"")"
Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" &amp; j).Copy
Workbooks(savename).Worksheets("Data").Range("G" &amp; i).PasteSpecial xlPasteValues
Workbooks("LTL Requests Log.xlsm").Worksheets("09.28 REQ").Range("Y" &amp; j).Value = ""
End If

'Columns K, M, &amp; L
current.Offset(0, -3).Copy
Workbooks(savename).Worksheets("Data").Range("K" &amp; i).PasteSpecial xlPasteValues
current.Offset(0, -3).Copy
Workbooks(savename).Worksheets("Data").Range("M" &amp; i).PasteSpecial xlPasteValues

current.Offset(0, -2).Copy
Workbooks(savename).Worksheets("Data").Range("K" &amp; i + 1).PasteSpecial xlPasteValues
current.Offset(0, -2).Copy
Workbooks(savename).Worksheets("Data").Range("M" &amp; i + 1).PasteSpecial xlPasteValues

Workbooks(savename).Worksheets("Data").Range("L" &amp; i).Value = "1"
Workbooks(savename).Worksheets("Data").Range("L" &amp; i + 1).Value = "2"

'Columns D, H, &amp; N
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("D4").Copy Workbooks(savename).Worksheets("Data").Range("D" &amp; i)
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("H4").Copy Workbooks(savename).Worksheets("Data").Range("H" &amp; i)
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("N4").Copy Workbooks(savename).Worksheets("Data").Range("N" &amp; i)
Workbooks("LTL Requests Log.xlsm").Worksheets("Static Info DelvSched").Range("N4").Copy Workbooks(savename).Worksheets("Data").Range("N" &amp; i + 1)

i = i + 2
j = j + 1
Set current = current.Offset(1, 0)

Wend
'Activate xml file and format to text (@)
Workbooks(savename).Activate
ActiveWorkbook.ActiveSheet.Cells.NumberFormat = "@"
'Error handling for screen change
Application.ScreenUpdating = True
errHandler:
Application.ScreenUpdating = True
End Sub

Related Questions

Please help edit the following program so that the user can input their own arra
Question #3606273
Please help edit the following program so that the user can input their own arra
Question #3607119
Please help especially with 1 and 2 If you dissolve K_2CO_3, in water, the potas
Question #493849
Please help explain how these work... Thank you! Explain the wireless light. Can
Question #1548060
Please help explain how these work... Thank you! Explain the wireless light. Can
Question #1599182
Please help explain how to solve this. The correct answer is \"A\" Timmy brushes
Question #1509388
Please help explain the solution, thank you! An amusement park lets guests drive
Question #3042474
Please help explain these.. I am lost. Please explain step by step. The followin
Question #3607089
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote

Navigate

Previous
Please help i dont know what to do! In a certain experiment, 28.01 mL of .2500 M
Next
Please help i have been stuck on these few questions, need help ASAP!! THANK YOU

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.