Start Free TrialLog in
Avatar of DemonForce
DemonForce

asked on 

Copy range and paste only formats

Hi,

I use this...

        activesheet.Range("D180:I219").Value = activesheet.Range("master").Value

to cut and paste values without using clipboard, is there a way to use very similiar but just copy and paste the format of the cells and none of its data?
Microsoft Excel
Avatar of undefined
Last Comment
Jacques Geday
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
Not similar but the

copy
Pastespecial

combination can do this.
Avatar of DemonForce
DemonForce

ASKER

Yeah used that, but is there a faster way?
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
What formatting are you trying to copy over?
Avatar of DemonForce
DemonForce

ASKER

Borders...

Basically the sheet has a nice layout, but copy and paste or ctrl-c etc in the wrong place messes the sheet up, so I use a design sheet to recopy the layout back to the original so any mesed up stuff gets put straight back.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
ActiveSheet.Range("master").Copy
ActiveSheet.Range("D180").PasteSpecial Paste:=xlPasteFormats
will paste the formats only.

ActiveSheet.Range("D180").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Will paste formulas and Format Numbers

ActiveSheet.Range("D180").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Will paste Values and Number Formats.

gowflow
Avatar of DemonForce
DemonForce

ASKER

Yeah, I tried those before but even with switch off screnupdating and events I get flicker
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
your comment is to my post ?
gowflow
Avatar of DemonForce
DemonForce

ASKER

gowflow, yes I have tried that and it works, but not fast enough
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
Actually borders are a slow process and I do not see any way out of it.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
can you post  sample of what you want to copy, maybe you have something that intervene with this.
gowflow
Avatar of Norie
Norie
Why not copy the design sheet?
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent