VBA help to fix broken code - delete rows, paste transposed data

[spill-the-beans]

Hello,

I've tried to record a macro and expand that in order to help solve my problem. I've fixed some things I need to do by looking at solved threads, but not everything. Below is my code. Ideally, I'd like to do the data sorting operation for a bunch of text files in a named folder, but that's the least of my issues right now.

• I looked at lots of different ways to delete blank rows, but I'm not sure how to add the advised code in at this location in my code, or even which way is best in this case.
• The code stops running when it gets to the bit about selecting the next blank row down and pasting, but it doesn't tell me what the problem is.
• After saving the results file, is there a way to go back to the text file that was opened in order to close it? I don't need them after getting the data, but can't name each text file if I am later going to run the data sorting operation for loads of files.

Sub test()

''''need to open all text files within a folder here''''


'with the open text files, do this to each one'


'Take out irrelevant columns'
    Columns("A:K").Select
    Selection.Delete Shift:=xlToLeft


    Columns("B:CF").Select
    Selection.Delete Shift:=xlToLeft
    
'Take out unwanted 0 value cells'
    Columns("A:A").Select
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
''''need to delete blank rows here''''


'Change A1 to the name of the sheet for later reference'
    ActiveSheet.Range("A1") = ActiveSheet.Name
            
'Copy the relevant data'
    Range("A1:A20").Select
    Selection.Copy
    


'Activate the open results file where the data needs to be pasted'
    Windows("ResultsFile.xlsm").Activate
    
    ''''need to select next empty row here, and paste the copied values in transposed format''''
    Range("A1").End(xlDown).Offset(1). _
    PasteSpecial Paste:=xlPasteValues, Transpose:=True
       
'Save the results file'
    ActiveWorkbook.Save
    
    
    '''' need to close the text file that was opened''''


End Sub

Can anyone please help?