Trouble copying formulas

pdchislett

New Member
Joined
Oct 12, 2009
Messages
22
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Try eg:

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

Change the workbook, worksheet and range references to suit.
 
Upvote 0
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
 
Upvote 0
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:

Code:
ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).Copy Workbooks("Book1.xls").Worksheets("Sheet1").Range("A5").End(xlDown).Offset(1, 0)
 
Upvote 0
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.
 
Upvote 0
Try:

Code:
ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
Workbooks("Book1.xls").Worksheets("Sheet1").Range("A5").End(xlDown).Offset(1, 0).PasteSpecial xlPasteFormulas
 
Upvote 0
oh ok - didnt realise that. I'll have to think of a different way around this.
Thanks for your help.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top