Trouble copying formulas

[pdchislett]

Hi,

I'm trying to copy a set of filtered data into another workbook but on the destination workbook i seem to lose all the formulas.

How can I copy the data and retain all the formulas?

I'm using this code:

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).Copy

Windows(xxxx).Activate
ActiveSheet.Paste


Thanks

 

[Andrew Poulsom]

Try eg:

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).Copy Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1")

Change the workbook, worksheet and range references to suit.

 

[pdchislett]

Thanks Andrew - This didnt work but I might have misunderstood.

This is a bit more of the code and a better explanation of what i'm trying to do...

In the destination file for the copied data I select A5 as this will always have data in and then work out the next blank line...

Range("A5").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

I then go back to the source file, add a filter and attempt to copy the data back to the destination file with vlookups etc still in the cells.

Windows(Source).Activate


Rows("1:1").Select
Selection.AutoFilter Field:=19, Criteria1:=CC

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).Copy

Windows(Destination).Activate
ActiveSheet.Paste

This should copy the data in the blank line selected earlier but it hard codes the data rather than retaining the vlookups etc that were copied from the Source file.

Any ideas?

Thanks

 

[Andrew Poulsom]

In what way didn't it work? What's the name of the workbook you want to paste to? See if you can adapt this:

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).Copy Workbooks("Book1.xls").Worksheets("Sheet1").Range("A5").End(xlDown).Offset(1, 0)

 

[pdchislett]

I tried again with your suggestion and while the data is being pasted into the correct place there are no formulas in my destination - just hard-coded data rather than the vlookups that were in the original file.

 

[Andrew Poulsom]

Yes, that's what happens if you paste a noncontiguous range manually.

http://support.microsoft.com/kb/210725

 

[Andrew Poulsom]

Try:

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
Workbooks("Book1.xls").Worksheets("Sheet1").Range("A5").End(xlDown).Offset(1, 0).PasteSpecial xlPasteFormulas

 

[pdchislett]

oh ok - didnt realise that. I'll have to think of a different way around this.
Thanks for your help.

 

[Andrew Poulsom]

See my recent post.