Pasting values from multiple worksheets into master worksheet

[ppp112]

I am trying to paste values from each worksheet in my workbook into a master worksheet. The problem I'm having is highlighted in yellow below. I cannot seem to change this line to paste values.

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" Then
Sht.Select
Range("A:A").Insert
Range("A2").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"
Range("A2").Copy
Range("A2").PasteSpecial Paste:=xlPasteValues
Range("A2:K2").Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A:A").Delete
Else
End If
Next Sht


End Sub


Thanks in advance!

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

[ppp112]

AlphaFrog,

This works great if the data is hard-coded, but it doesn't work if the source worksheets are formulas. I need to paste the values from the source worksheets.

[AlphaFrog]

Please Login or Register  to view this content.

[ppp112]

Awesome. Thanks!