Private Sub CommandButton1_Click()
Dim wbTrans As Workbook, wbMeas As Workbook, wsTrans As Worksheet, wsMeas As Worksheet, wsData As Worksheet
Dim wbPath As String, flPath As String, flName As String, wpTrans As String
Dim dat_v As Variant, dat_rng As Range, iss_rng As Range, iss_num As Variant
Dim char_clean As Variant, char_rng As Range, r As Variant, rows As Range
With Application
.ScreenUpdating = False
.DisplayAlerts = False
iWindowState = .WindowState
.WindowState = xlMinimized
End With
wpTrans = "N:\E_Neale\CSV_Cleaned_Char"
wnTrans = "Trans_to_Meas.xlsm"
Set wbTrans = ActiveWorkbook
Set wsTrans = wbTrans.Worksheets("Files")
Set wsData = wbTrans.Worksheets("Measurements")
flPath = wsTrans.Range("B1").Value
flName = wsTrans.Range("B2").Value
cntr_r = 0
cntr_k = 0
pn_name = Split(flName, "_")(0)
Set iss_rng = wsTrans.Range(Cells(3, 2), Cells(14, 2))
For Each iss_num In iss_rng
If iss_num = "" Then
GoTo Leave
End If
lastr = 0
lastc = 0
i = 0
col_s = 0
cntr_c = 0
r_end = lastr
r_s = 2
r = 0
wbName = flName & "-" & iss_num & ".xlsx"
wbPath = flPath & "\" & wbName
Set wbMeas = Workbooks.Open(wbPath)
Set wsMeas = wbMeas.Worksheets("Update_CSV")
wsMeas.Range("F2").Activate
While Not ActiveCell = ""
lastr = lastr + 1
ActiveCell.Offset(1, 0).Activate
Wend
wsMeas.Range("F2").Activate
While Not ActiveCell = ""
lastc = lastc + 1
ActiveCell.Offset(0, 1).Activate
Wend
'Need to determine best way to transfer data into wbTrans(Measurements)
wsData.Activate
While cntr_c < (lastc * lastr)
cntr_r = ((col_s - i) * lastr) + cntr_k
Windows(wbName).Activate
chk_char = Left(wsMeas.Cells(1, 6 + col_s).Value, 1)
If IsNumeric(chk_char) = False Then
i = i + 1
GoTo skip_letter
End If
meas_data = wsMeas.Range(wsMeas.Cells(2, 6 + col_s), wsMeas.Cells(lastr + 1, 6 + col_s)).Value
sn_data = wsMeas.Range(wsMeas.Cells(2, 2), wsMeas.Cells(lastr + 1, 2)).Value
char_data = wsMeas.Cells(1, 6 + col_s).Value
wsData.Activate
wsData.Range(wsData.Cells(2 + cntr_r, 6), wsData.Cells(1 + cntr_r + (lastr), 6)).Value = meas_data
wsData.Range(wsData.Cells(2 + cntr_r, 8), wsData.Cells(1 + cntr_r + (lastr), 8)).Value = sn_data
wsData.Range(wsData.Cells(2 + cntr_r, 2), wsData.Cells(1 + cntr_r + (lastr), 2)).Value = char_data
wsData.Range(wsData.Cells(2 + cntr_r, 5), wsData.Cells(1 + cntr_r + (lastr), 5)).Value = 1
wsData.Range(wsData.Cells(2 + cntr_r, 1), wsData.Cells(1 + cntr_r + (lastr), 1)).Value = pn_name
For r = 1 To lastr
wsData.Range("D" & 1 + cntr_r + r).Value = r
Next r
skip_letter:
col_s = col_s + 1
cntr_c = (col_s + i) * lastr
Wend
cntr_k = cntr_r
cntr_c = 0
wbMeas.Activate
ActiveWorkbook.Close
Next iss_num
Leave:
Set char_rng = wsData.Range(wsData.Cells(2, 2), wsData.Cells(cntr_k + 1, 2))
For Each char_clean In char_rng
If InStr(char_clean.Value, "_") > 0 Then
char_clean.Value = Replace(char_clean, "_", ".")
End If
Next char_clean
With Application
.ScreenUpdating = True
.DisplayAlerts = True
iWindowState = .WindowState
.WindowState = xlMaximized
End With
End Sub
