Macro to copy formulas from multiple workbooks into new workbook (paste text of formula)

Hello,

I've got a large number of workbooks, each with the exact same sheet names and structure; I want to copy selected cells from selected sheets out of each one and paste into a master workbook, each of the 80 workbooks having one row in the master workbook with a column for each of the cells copied and pasted. The cells being copied have formulas in them and I want to paste the actual formula (not the result) into the master workbook.

I've found many solutions to situations almost like this, but not something that works for just right. Here is something I found that almost does the trick at: http://www.mrexcel.com/forum/excel-q...-workbook.html (the macro code posted by hiker95).

The relevant portion of the code is:

With .Range("A" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!B1"
.Value = .Value

However, this pastes the actual values, not the formulas (cell B1 of the source workbook is a formula). But what I need is the actual formula text pasted into the master workbook.

I know it must be something simple I can add or change to what is provided in this existing thread, I just cannot figure out what that addition or edit is.

Any direction is appreciated. I'm using Windows XP and Excel 2010.

Thanks,

Doug

Have you tried .Formula instead of .Value?

Yes, I tried that; every combination of .Value and .Formula too.

What I mean is, assuming you've formatted the formula correctly (hard to say since I don't know what fn and sn are), just use the .Formula line and remove the .Value line. If you step through your code, you should see the formula populate in range "A" & NR on the .Formula line, and the changing to just the value on the next line.

Yes, I did try that also. I'll paste the entire macro from the above referenced link so the code snippet will make more sense; I wasn't sure if that was appropriate, thus did not do that initially.

TAKEN FROM http://www.mrexcel.com/forum/excel-q...-workbook.html (the macro code posted by hiker95)


Option Explicit
Sub GetMyData()
Dim myDir As String, fn As String, sn As String, sn2 As String, n As Long, NR As Long

'***** Change Folder Path *****
'myDir = "C:\TestData" 'for testing
myDir = "C:\TestData"

'***** Change Sheetname(s) *****
sn = "Detaljplan"
sn2 = "Dim PEX og HL"

fn = Dir(myDir & "\*.xls")
Do While fn <> ""
If fn <> ThisWorkbook.Name Then
With ThisWorkbook.Sheets("Summary")
NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1

'D39, L34 and C16 from worksheet "Detaljplan"
With .Range("A" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!D39"
.Value = .Value
End With
With .Range("B" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!L34"
.Value = .Value
End With
With .Range("C" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn & "'!C16"
.Value = .Value
End With

'F14, G14, I14, J14, M14 and N14 from worksheet "Dim PEX og HL"
With .Range("D" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!F14"
.Value = .Value
End With
With .Range("E" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!G14"
.Value = .Value
End With
With .Range("F" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!I14"
.Value = .Value
End With
With .Range("G" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!J14"
.Value = .Value
End With
With .Range("H" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!M14"
.Value = .Value
End With
With .Range("I" & NR)
.Formula = "='" & myDir & "\[" & fn & "]" & sn2 & "'!N14"
.Value = .Value
.Offset(, 1).Value = fn
End With
End With
End If
fn = Dir
Loop
End Sub