Good afternoon,
I'm trying to get my macro to paste row j from "master" workbook into cell A12 of sheet1 in each workbook j in E:\DIRECTORY\. The files in the directory are sorted in the same order as the rows to be copied in master. Row 1 in master goes to workbook 1 (A12 in sheet1), row 2 in master goes to workbook2 (A12 in sheet 1) etc. Below is my attempt, but it keeps looping through files until every file gets every row. I only want one row per file:
Sub semiauto()
'this sets your template workbook/worksheet
Dim master As Workbook
Set master = Workbooks("NS RA (Non-Union)")
'this creates a collection of all filenames to be processed
Dim loopFolder As String
Dim fileNm As Variant
Dim myFiles As New Collection
Dim FinalRow As Long
Dim i As Long
master.Activate
Sheets("Sheet2").Select
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
'''don't forget the backslash before the final double-quote below
loopFolder = "C:\Users\marsmanm\Desktop\PAY\MMRA1\test\"
fileNm = Dir(loopFolder & "*.xlsm")
Do While fileNm <> ""
myFiles.Add fileNm
fileNm = Dir
Loop
'this loops through all filenames and copies your copyWS to the beginning
Dim wb As Workbook
For i = 2 To FinalRow
For Each fileNm In myFiles
Set wb = Workbooks.Open(filename:=(loopFolder & fileNm))
master.Activate
Sheets("Sheet2").Select
Range("A" & i, "n" & i).Copy
wb.Activate
Sheets("Appendix D Calc").Select
With ActiveSheet.Range("A12")
.PasteSpecial (xlPasteValues)
.PasteSpecial (xlPasteFormats)
.PasteSpecial (xlPasteColumnWidths)
wb.Save
wb.Close
End With
Next
Next i
End Sub
Bookmarks