Set value for range being copied over with VBA

[ItalianPlatinum]

May someone help me I think I am inherently doing something incorrectly. I just want to copy "NonF" down for each file that is transferred over as well as the file name that it came from. But what I have isn't doing that. It is just putting it on the last row.

Sub Summary()
Dim fName As String, fPath As String, fPartial As String
Dim WsNonF As Worksheet, WsSummary As Worksheet
Dim NrNonF As Long, NrFOF As Long
Dim lastrow As Long, lr As Long, lrr As Long
Dim WbkName As String

Application.ScreenUpdating = False

'set sheet variables
Set WsNonF = Sheets("NONFDATA")
Set WsSummary = Sheets("Summary")

WsNonF.Cells.Delete
WsSummary.Cells.ClearContents

'Non F
fPath = "MY PATH" 'substitute actual path is not in same directory as host workbook.
fPartial = "PARTIAL FINAL NAME" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now) - 1) = 1, "0" & Day(Now) - 1, Day(Now) - 1) & "*.txt"

If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & fPartial)
    Do While fName <> "" 'open each file loop until no more files to open
        Workbooks.OpenText fPath & fName 'opening the txt file
        NrNonF = WsNonF.Range("A" & Rows.Count).End(xlUp).Row + 1 'find open row
        lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 1 'find the maximum row
        WbkName = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) 'get activeworkbooks name
        Range("A2:A" & lastrow).Copy 'Copy data
        WsNonF.Range("A" & NrNonF).PasteSpecial Paste:=xlPasteValues 'paste to next open row
        lrr = WsNonF.Cells(Rows.Count, "A").End(xlUp).Row 'find the range that came over from the file
        WsNonF.Range("L" & lrr).Value = "NonF" 'set the type of file it came from
        WsNonF.Range("M" & lrr).Value = WbkName 'set the file name it came from
        Application.CutCopyMode = False
        Workbooks(fName).Close SaveChanges:=False 'close file dont save
        fName = Dir
    Loop

 

[ItalianPlatinum]

Awesome - this worked. Not sure I get entirely the purpose of what resize does/means but let me read up on that more. i appreciate your time and assistance on this

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.
Let me have a quick go at the resize question.

If I want to copy from C5:C9 to the next available row in column G and lets say that is G10.
Then I need to finish up with the equivalent of
Range"G10:G14").Value = Range("C5:C9").Value

I don't know the 14 but I do know the 10 in column G (next available row)
Resize has 2 parameters .Resize(NoOfRows, NoOfColumns)

So the equivalent of the above is (where we know that C5:C9 is 5 rows)
Range"G10").Resize(5).Value = Range("C5:C9").Value

So its just what is easier
• Calculate the 14 in G14
• Calculate the number of rows
ie
9 - 5 + 1 = 5
or Range("C5:C9").rows.count
or if you have a loop you probably have a counter that already contains the 5
or if you have used an array Ubound(arr)

 

[ItalianPlatinum]

So in my case I set LrF to be the last row of the source data. The NrSum is the open row on the destination. So Resize(lrf) your saying is the number of rows to place on that last open row on A & NrSum? That would be from the right side of the equation.

NrSUM = .Range("A" & Rows.Count).End(xlUp).Row + 1 'find open row
    .Range("A" & NrSUM).Resize(lrf).Value = WsF.Range("K1:K" & lrf).Value 'transfer over ID

 

[Alex Blakenburg]

So like I mentioned it is only that simple in your case because the data on the right side starts at row 1.
The number of rows is
• Last row - First Row + 1 (+1 for inclusive)
Since your range is starting in row 1 that becomes
• Last Row - 1 + 1 which = Last Row which = lrf

 

[ItalianPlatinum]

Yea I think that is the part that trips me up when I use variables sometimes hard to trace because it isn't an easy cookie cutter situation and I think ultimately that did me. In turn eventually had me coming here for help.