Addition to code to copy and pastes special values and cell format, but not links.

**singerbatfink** · 05-08-2014, 04:32 AM

Hi I have a small piece of code which helps me copy sheet 1 from 24 separate workbooks and then merges them into a singlwe workbook.
what do I need to do the the code to allow it to only copy the cell formats and values without copying over the whole link to the original documents?
Thanks

Sub GetSheets()
 Path = "My File path"
 Filename = Dir(Path & "*.xls")
 Do While Filename <> ""
 Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
 ActiveWorkbook.Sheets(1).Copy After:=ThisWorkbook.Sheets(1)
 Workbooks(Filename).Close
 Filename = Dir()
 Loop
 End Sub

**Miroslav R.** · 05-09-2014, 02:23 AM

Hi there,
after copy, try to paste in wbk you want with pastespecial method:

for example:

Sheets(2).Range("A2").PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats

This will break you links

Hope it helps

Regards

M

**singerbatfink** · 05-13-2014, 03:05 PM

Originally Posted by Miroslav R.

Hi there,
after copy, try to paste in wbk you want with pastespecial method:

for example:

Sheets(2).Range("A2").PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats

This will break you links

Hope it helps

Regards

M

Perfect Thank you..

**Miroslav R.** · 05-13-2014, 03:10 PM

You are welcome...

Regards

M

Addition to code to copy and pastes special values and cell format, but not links.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Addition to code to copy and pastes special values and cell format, but not links.

Re: Addition to code to copy and pastes special values and cell format, but not links.

Re: Addition to code to copy and pastes special values and cell format, but not links.

Re: Addition to code to copy and pastes special values and cell format, but not links.

Thread Information

Users Browsing this Thread

Similar Threads

A macro that copy and pastes to line below but pastes in a Values and numbers format

A macro that copy and pastes to line below but pastes in a Values and numbers format

[SOLVED] Set code copy and paste special values

[SOLVED] Code for Copy and Paste Special- Values in Columns

[SOLVED] Copy and Paste Special (Formulas) pastes cell value if numeric

Bookmarks

Bookmarks

Posting Permissions