Hi All

I am having trouble with this and wanted some assistance. I am trying
to build a consolidation of P&L sheets held in different files. I am
able to dynamically open all the P&L files fine.

I just want to match the sheet names in the opened files with that of
the consolidation sheet names. So in the opened file, for example a
sheet called “MP+L” exists so copy it (values only). Match this sheet
name in the consolidation file, paste this data right over the top of
the old data (no links).

This is possible it is just getting late here in Oz and I am tired.

Thanks in advance for your help.

Chad

Code works however without the dynamism of matching sheet names
between consolidation and all children worksheets.

Option Explicit
Sub OpenandPaste()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim ws As Worksheet
Dim varSh As String
Dim twbk As Workbook
Dim lw As Integer

Set twbk = ActiveWorkbook

Application.ScreenUpdating = False
sPath = "C:\users\smallman\excel" 'location of files
ChDir sPath
sFil = Dir("Open*.xls") 'change or add formats
Do While sFil < "" 'will start LOOP until all files in folder sPath
have been looped through
Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file
varSh = "P+L" 'NEED a variable here to cope with the changing file
names.
' do something
For Each ws In ActiveWorkbook.Worksheets
If Right(LCase(ws.Name), 1) = "1" Then '1 just used as a test to see
that sheet1 would copy in OK
lw = ws.Range("B" & Rows.Count).End(xlUp).Row
ws.Range("B6:S" & lw).Copy twbk.Sheets(varSh).Range("B6")
End If
Next ws
oWbk.Close False 'close the workbook, NOT saving changes
sFil = Dir
Loop ' End of LOOP
End Sub