open different workbooks within a folder sequentially to copy and paste special values into a specific file which is then saved under a new name

[shawnbull]

Hi All,
I need VBA code please that will do the following:- Open one file at a time within a folder called "Provisional NSIs" each ending with xlsx ( around 67 files in all but numbers may change), example of file location :- C:\Users\shawn\OneDrive\Desktop\Provisional NSIs\2 Staff Locker 3 Cabinet Vertical.xlsx. Then open a second file named NSI New Template on the Desktop, (C:\Users\shawn\OneDrive\Desktop\NSI new template.xlsx), copy and paste special values from the "Provisional NSIs" Folder file specifically cells D37, F37, H37:J37 into NSI New Template at G41, I41 and K41:M41. Then save NSI New Template as the opened File Name from the "Provisional NSIs" folder with 2022 appended to the name into a folder called NSIs 2022, close both files, reopen NSI New Template, obtain the next file from the folder "Provisional NSIs" and repeat the process until all the excel files from the folder have had their data copied over.
Hope someone can assist.
Thank you

 

[shawnbull]

managed to sort it out am posting my solution should it be of benefit to anyone else:-

Sub AllFiles()
Dim folderPath As String
Dim filename As String
Dim wb As Workbook
Dim nsi As Variant



Application.ScreenUpdating = False
folderPath = "C:\Users\shawn\OneDrive\Desktop\Provisional NSIs\" 'change to suit

If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"

filename = Dir(folderPath & "*.xlsx")
Do While filename <> ""
Application.ScreenUpdating = False
Set wb = Workbooks.Open(folderPath & filename)
Set nsi = Workbooks.Open("C:\Users\shawn\OneDrive\Desktop\NSI new template.xlsx")




'copy data from selected file per folder into required static file

wb.Activate
Range("B37").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E37").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = True
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B37").Select
Selection.Copy
Application.WindowState = xlNormal
Windows("NSI new template.xlsx").Activate
Range("B41:F41").Select
ActiveSheet.Paste
wb.Activate
Range("E37").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NSI new template.xlsx").Activate
Range("G41").Select
ActiveSheet.Paste
wb.Activate
Range("G37:H37").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NSI new template.xlsx").Activate
Range("H41").Select
ActiveSheet.Paste

nsi.SaveAs filename:="C:\Users\shawn\OneDrive\Desktop\provisional 2022\" & 2022 & " " & wb.Name

nsi.Close





wb.Close SaveChanges:=False


filename = Dir
Loop


Application.ScreenUpdating = True
End Sub