zapp7
06-06-2008, 11:09 AM
I have this macro that I'm using to grab a certain common cell from multiple spreadsheets in various excel files:
Sub getcells()
MyPath = "H:\ExcelFiles\"
For x = 1 To 5
MyFileName = "p" & x & ".xls"
Workbooks.Open MyPath & MyFileName
For Each Sheet In Sheets
ThisWorkbook.Sheets(1).Cells(65536, 1).End(xlUp).Offset(1, 0) = MyFileName
Sheet.Range("J120").Copy Destination:=ThisWorkbook.Sheets(1).Cells(65536, 1).End(xlUp).Offset(0, 1)
Next Sheet
ActiveWorkbook.Close
Next x
End Sub
The problem with this is that I'm copying the formula and the cells all end up with #REF! in them. I know Paste Special can copy the values, but I don't know how to use it in macro code. I attempted it, but it copied everything into a single cell, whereas this one will copy the results into vertically adjacent cells.
Example: (I want values in column like this with the absolute values)
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
Sub getcells()
MyPath = "H:\ExcelFiles\"
For x = 1 To 5
MyFileName = "p" & x & ".xls"
Workbooks.Open MyPath & MyFileName
For Each Sheet In Sheets
ThisWorkbook.Sheets(1).Cells(65536, 1).End(xlUp).Offset(1, 0) = MyFileName
Sheet.Range("J120").Copy Destination:=ThisWorkbook.Sheets(1).Cells(65536, 1).End(xlUp).Offset(0, 1)
Next Sheet
ActiveWorkbook.Close
Next x
End Sub
The problem with this is that I'm copying the formula and the cells all end up with #REF! in them. I know Paste Special can copy the values, but I don't know how to use it in macro code. I attempted it, but it copied everything into a single cell, whereas this one will copy the results into vertically adjacent cells.
Example: (I want values in column like this with the absolute values)
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!
p1.xls #REF!