Merging worksheets into a master sheet (with formulas and formats intact)

[Arrow081974]

Hi all

I'm really hoping somebody maybe able to advise as I've been racking my brains out on this. I'm working on a project where I need to merge multiple excel worksheets (to be supplied by different owners- but in a consistent format) into a single worksheet. I've made a fair amount of progress but now have the following issues.

1. I need the project to also copy across the formulas and formats present into the source file

I was going to use the following code

ActiveSheet.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False = Worksheets("Plan").Range(Cells(erow, 1), Cells(erow, 15))
ActiveSheet.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False = Worksheets("Plan").Range(Cells(erow, 1), Cells(erow, 15))

in addition/replacing

ActiveSheet.Paste Destination:=Worksheets("Plan").Range(Cells(erow, 1), Cells(erow, 15))

2. I also need the ability to apply the formulas present within the source worksheets to the master sheet if new rows are keyed directly into the master worksheet. As the number of rows may flex between source sheets.

Here's my full code so far.

Sub copydateafrommultipleworkbooksintomaster()


Dim Folderpath As String, filepath As String, filename As String


Folderpath = "C:\Users\Adam\Documents\Test environment\business files"
filepath = Folderpath & "*.xlsx*"
filename = Dir(filepath)
Dim lastrow As Long, lastcolumn As Long


Do While filename <> ""
Workbooks.Open (Folderpath & filename)
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Plan").Range(Cells(erow, 1), Cells(erow, 15))


filename = Dir
Loop
Application.DisplayAlerts = True
MsgBox "Congratulations. All rows have now been imported "


End Sub

Really appreciate any help anybody can offer.

Adam