Solved

Copy range and paste only formats

Posted on 2014-03-15
12
237 Views
Last Modified: 2014-06-23
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?
0
Comment
Question by:DemonForce
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Not similar but the

copy
Pastespecial

combination can do this.
0
 

Author Comment

by:DemonForce
Comment Utility
Yeah used that, but is there a faster way?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
What formatting are you trying to copy over?
0
 

Author Comment

by:DemonForce
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:DemonForce
Comment Utility
Yeah, I tried those before but even with switch off screnupdating and events I get flicker
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
your comment is to my post ?
gowflow
0
 

Author Comment

by:DemonForce
Comment Utility
gowflow, yes I have tried that and it works, but not fast enough
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Actually borders are a slow process and I do not see any way out of it.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
can you post  sample of what you want to copy, maybe you have something that intervene with this.
gowflow
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Why not copy the design sheet?
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
Looking at this question again and seeing the different input and specially your comment:


Yeah, I tried those before but even with switch off screnupdating and events I get flicker

lead me to believe that this instruction:
activesheet.Range("D180:I219").Value = activesheet.Range("master").Value

could be lying in a worksheet change event that is being called recursively reason why your screen is flickering.

Can you confirm this ?

and to make it speedier I would suggest something like

Sub ABC

With Application
   .EnableEvents = false
   .DisplayAlerts = False
   .ScreenUpdating  = False
End With

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

or any Copy Paste
.....

With Application
   .EnableEvents = True
   .DisplayAlerts = True
   .ScreenUpdating  = True
End With

End Sub

Open in new window


Also wyse to note that even if you have this kind of structure and the copy paste is affecting cells that have formulas and you have code in the Worksheet_Calculate event all this can contribute to slow/flickering and all this.

To sum it up, with the very little info you provided these are the wide range of issues that we can possibly think of. For sure it would be much better and more beneficial to you and time saving if you can provide more specifics on where the code lies and what are you encountering exactly and what type of data your are trying to copy/paste.

gowflow
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now