My code executes the formatting lines on the first loop only after the import, but does not execute them any time after. Everything else works. I have scrubbed it multiple times with nothing. Ideas? If anyone needs more snips of my code I can add them in. I have no problems with other sections.
Sub Main()
'this code imports one csv file at a time, analyzes it, determines individual run types and copies based on the type
Dim FName As Variant, R As Long, DirLoc As String, i As Integer
R = 1
i = 1
RowCount = 1
ColumnCount = 1
DirLoc = ThisWorkbook.Path & "\" 'location of files
FName = Dir(DirLoc & "*.csv")
Do While FName <> ""
ImportCsvFile DirLoc & FName, ActiveSheet.Cells(R, 1)
R = ActiveSheet.UsedRange.Rows.Count + 1
FName = Dir
Sheets("RAW").Range("B1:B6").Copy
Sheets("Filtered").Cells(RowCount, ColumnCount).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ColumnCount = ColumnCount + 6
Set RangeObj = Cells.Find(What:="Run:", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
RangeObj.Select
Range("A1:A" & ActiveCell.Row - 1).EntireRow.Delete
For i = 1 To 100
RunTypeChooser = 0
'Discover what format the csv file is in (depending on the run type). This may be edited to include more if needed
DiscoverRunType
'Based on the discovery this will systematically copy a run with the appropriate run type, and delete the run
CopyMain
If Range("A1") = 0 Then
Exit For
End If
Next i
i = 1
ColumnCount = 1
Loop
End Sub
The code in question that does not work is this:
Sheets("RAW").Range("B1:B6").Copy
Sheets("Filtered").Cells(RowCount, ColumnCount).PasteSpecial
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ColumnCount = ColumnCount + 6
It will only run on loop number 1, but then never again.
Briefly, it was a simple mistake. The R value was meant to count and append copied cells after the previous copied section. This causes the code to paste below, and I wasn't finding it because the code is also supposed to delete all empty cells above Run1.
The solution was to delete R and associated code to prevent appending copying