I know I'm probably asking 10 different questions within one post, but I'm such a newbie when it comes to macros that I don't know how to break it down more so that I can Google the answers..

CCSV.xlsm

Attached is a small part of what I'm working on.
I have this giant master sheet with somewhat condensed headers, but I need to copy over all the data to a new worksheet with different headers..

The third column of Sheet1 ("Analyte") has four different variables, but when I copy to a new sheet, I need each of the four variables to be in their own column (example would be in Sheet3). The corresponding "Week" number (column 5 of Sheet1), "Sample_Point" (column 4 of Sheet1), and "ANALYSIS_FINALRESULT" (column 1 of Sheet1) needs to match with the appropriate "Analyte" point. For instance, from Sheet1, Sample_Point RA should have four Analyte values for Week 0.33 (one each for Ca, K, Mg, and Na). I don't want the same week number for the same Sample_Point to show up four times in the new worksheet.

I don't know where the issue is in here, but if someone could help me out, or guide me to another thread that answers some of my questions, I would be so grateful!

    '   Set up Variables
    Dim ROW_NEXT As Long
    STR_FIELD_NAMES = "Sample_Point#Sample_Date#Site#Data_Source#Sample_Reference#Sample_Type#Unit#Parameter_Type#Ag#Parameter_Type#Al#Parameter_Type#As#Parameter_Type#B#Parameter_Type#Ba#Parameter_Type#Be#Parameter_Type#Bi#Parameter_Type#Cd#Parameter_Type#Co#Parameter_Type#Cr#Parameter_Type#Cu#Parameter_Type#Fe#Parameter_Type#Hg#Parameter_Type#Li#Parameter_Type#Mn#Parameter_Type#Mo#Parameter_Type#Ni#Parameter_Type#P#Parameter_Type#Pb#Parameter_Type#S#Parameter_Type#Sb#Parameter_Type#Se#Parameter_Type#Si#Parameter_Type#SiO2#Parameter_Type#Sn#Parameter_Type#Sr#Parameter_Type#Ti#Parameter_Type#Tl#Parameter_Type#U#Parameter_Type#V#Parameter_Type#Y#Parameter_Type#Zn"
    ARR_FIELD_NAMES = Split(STR_FIELD_NAMES, "#")

  '   Set up headers and find column number
    COL_O_FINAL = 0
    For Each V In ARR_FIELD_NAMES
        COL_O_FINAL = COL_O_FINAL + 1
        Sheets(NAME_FINALSHEET).Cells(1, COL_O_FINAL).Value = V
    Next V

    For x = 1 To Sheets.Count
        If Sheets(x).Name Like "CSV*" Then
            Sheets(x).Activate

            '   Find final input COL and ROW
            COL_I_FINAL = Cells(1, Columns.Count).End(xlToLeft).Column
            ROW_I_FINAL = Cells(Rows.Count, 1).End(xlUp).Row

            For R = 1 To COL_I_FINAL
                Select Case Cells(1, R).Value
                    Case Is = "Sample_Point"
                        COL_Sample_Point = R
                    Case Is = "Sample_Date"
                        COL_Sample_Date = R
                    Case Is = "Site"
                        COL_Site = R
                    Case Is = "Data_Source"
                        COL_Data_Source = R
                    Case Is = "Sample_Reference"
                        COL_Sample_Reference = R
                    Case Is = "Sample_Type"
                        COL_Sample_Type = R
                    Case Is = "Unit"
                        COL_Unit = R
                    Case Is = "Parameter_Type"
                        COL_Parameter_Type = R
                    Case Is = "Ca"
                        COL_Ca = R
                    Case Is = "Parameter_Type"
                        COL_Parameter_Type = R
                    Case Is = "K"
                        COL_K = R
                    Case Is = "Parameter_Type"
                        COL_Parameter_Type = R
                    Case Is = "Mg"
                        COL_Mg = R
                    Case Is = "Parameter_Type"
                        COL_Parameter_Type = R
                    Case Is = "Na"
                        COL_Na = R
                    Case Is = "Week"
                        COL_Week = R
                End Select
            Next R