Why does my Copying only work on the First Loop?

241 views Asked by At

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.

1

There are 1 answers

0
Coding Novice On BEST ANSWER

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