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
Bookmarks