PDA

View Full Version : Can't get PasteSpecial to work



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!

grichey
06-06-2008, 11:52 AM
edit: braindead on Friday

zapp7
06-06-2008, 11:56 AM
I'm copying from cell J120 of the other spreadsheets to a column in the current spreadsheet (it doesn't really matter which column).

mdmackillop
06-07-2008, 02:26 AM
Sub getcells()
Dim Tgt As Range
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
Set Tgt = ThisWorkbook.Sheets(1).Cells(65536, 1).End(xlUp).Offset(0, 1)
Sheet.Range("J120").Copy
Tgt.PasteSpecial xlPasteValues
Next Sheet
ActiveWorkbook.Close
Next x
End Sub

mdmackillop
06-07-2008, 02:29 AM
You can also retrieve data without opening the books as per this KB Item (http://www.vbaexpress.com/kb/getarticle.php?kb_id=454)