Link to home
Start Free TrialLog in
Avatar of James0903
James0903

asked on

Assistance with a VB Macro code

Sir/Madam,

I have an Excel workbook with many individual worksheets.  The worksheet called "CSV" is where my data is stored.  What I am trying to do is create a macro, run from the "Start" worksheet, which copies the data from "CSV" and inputs it into the proper place on the "Results" tab.  I am new to macro coding -- the code that is presently in the macro comes from a previous Experts question.  I am trying to modify the code to make it work for what I need now.

Please see attached Word document for a list of the issues I am encountering.  This Word document has the macro code.  Also please see the Excel spreadsheet attached.

Thank you as always.  Please advise with any questions!
ExpertsExchange-Question-15.docx
ExpertsExchange-Question15.xlsm
Avatar of Curt Lindstrom
Curt Lindstrom
Flag of Australia image

Try this one
This macro is stored in the attached file. Run macro from "Start" sheet
Sub FormB()
    Dim Carrier As String, CarrierN As String
    Dim RowN As Long
    Dim RowCSV As Long
    Application.DisplayAlerts = False
    If Sheets(1).Name = "Result" Then Sheets(1).Delete
    Application.DisplayAlerts = True
    Sheets("Template").Select
    Sheets("Template").Copy Before:=Sheets(1)
    Sheets(1).Select
    Sheets(1).Name = "Result"
    RowN = 12
    RowCSV = 1
    With Sheets(1)
        Carrier = "" & Sheets("CSV").Range("G" & RowCSV).Value & "-(" & Sheets("CSV").Range("F" & RowCSV).Value & ")"
        CarrierN = "" & Sheets("CSV").Range("G" & RowCSV).Value & "-(" & Sheets("CSV").Range("F" & RowCSV).Value & ")"
        Do While CarrierN <> "-()"
            .Range("A" & RowN - 9).Value = "Carrier Name: " & Carrier
            .Range("A" & RowN - 8).Value = "Calendar Period: 12 Months From " & Sheets("CSV").Range("E" & RowCSV).Value
            .Range("B" & RowN + 23).Value = Sheets("CSV").Range("A" & RowCSV).Value
            .Range("F" & RowN + 23).Value = Sheets("CSV").Range("B" & RowCSV).Value
            .Range("J" & RowN + 23).Value = Sheets("CSV").Range("C" & RowCSV).Value
            .Range("N" & RowN + 23).Value = Sheets("CSV").Range("D" & RowCSV).Value

            Do While Carrier = CarrierN And CarrierN <> ""
            If Sheets("CSV").Range("H" & RowCSV).Value = "carrier" Then RowCSV = RowCSV + 3
            If Sheets("CSV").Range("H" & RowCSV).Value = "S_l2a" Then RowN = RowN + 1
            If Sheets("CSV").Range("H" & RowCSV).Value = "II_2_a" Then RowN = RowN + 5
            If Sheets("CSV").Range("H" & RowCSV).Value = "NS_II2a" Then RowN = RowN + 1
                .Range("B" & RowN).Value = Sheets("CSV").Range("J" & RowCSV).Value
                .Range("C" & RowN).Value = Sheets("CSV").Range("K" & RowCSV).Value
                .Range("D" & RowN).Value = Sheets("CSV").Range("L" & RowCSV).Value
                .Range("E" & RowN).Value = Sheets("CSV").Range("M" & RowCSV).Value
                .Range("F" & RowN).Value = Sheets("CSV").Range("N" & RowCSV).Value
                .Range("G" & RowN).Value = Sheets("CSV").Range("O" & RowCSV).Value

                RowN = RowN + 1
                RowCSV = RowCSV + 1
                CarrierN = "" & Sheets("CSV").Range("G" & RowCSV).Value & "-(" & Sheets("CSV").Range("F" & RowCSV).Value & ")"
            Loop
            RowN = RowN + 3
            If CarrierN <> "-()" Then
                'Worksheets("Template").Rows("35:35").Copy Destination:=.Range("A" & RowN)
                Range("A" & RowN + 1).Select
                .HPageBreaks.Add Before:=ActiveCell
                RowN = RowN + 2
                Worksheets("Template").Rows("1:35").Copy Destination:=.Range("A" & RowN)
                Carrier = CarrierN
                RowN = RowN + 11
            End If
        Loop
    End With
End Sub

Open in new window


Cheers,
Curt
Carriers-Version-1.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Curt Lindstrom
Curt Lindstrom
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James0903
James0903

ASKER

Curt, thank you.  I will take a look.  I tried opening the attachment earlier but the site was down for maintenance.  Hopefully it works this time.
Curt, EXCELLENT!  THANK YOU!  Once again the Experts come through!  I have run this against the master list of 350+ airlines and works perfectly.  This is great and saves me hours of manually copying the data into the chart.  Thank you very much!  Your code is easy to follow as well so I can use this as a learning experience as well.  I did as much as I could and knew, you put the icing on the cake.  Thanks!
I'm pleased that it worked well for you!

Cheers,
Curt