Are the filenames of the 11 workbooks known and constant, or do you want to loop through all files in a folder, or look for file names with a base name and number e.g. File01.xlsx, File02.xlsx...File11.xlsx, or prompt the user to select the files ?
Same for the worksheets in each file. Do you want to loop through all sheets, or a list of specific ones, or ones that have a base name?
Sub MoveDataOnOtherSheets()
'
' MoveData Macro
' this must repeat for 11 different worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim vFile As Variant
Dim strPath As String
Dim counter As Long
strPath = ThisWorkbook.Path & "\"
Application.ScreenUpdating = False
For Each vFile In Array("Testwb", "Testwb2")
Set wb = Workbooks.Open(strPath & vFile)
For Each ws In wb.Worksheets ' loop through all sheets
'Insert, copy, paste
ws.Range("L5:W5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ws.Range("L5:W5").Value = ws.Range("L4:W4").Value
Next ws
wb.Close SaveChanges:=True
counter = counter + 1
Next vFile
Application.ScreenUpdating = True
MsgBox counter & " files processed.", vbInformation, "Move Data Complete"
End Sub
Bookmarks