Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copy range and paste only formats

Posted on 2014-03-15
12
Medium Priority
?
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39931382
Not similar but the

copy
Pastespecial

combination can do this.
0
 

Author Comment

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

Expert Comment

by:Saqib Husain, Syed
ID: 39931394
What formatting are you trying to copy over?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:DemonForce
ID: 39931400
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 31

Expert Comment

by:gowflow
ID: 39931406
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
ID: 39931411
Yeah, I tried those before but even with switch off screnupdating and events I get flicker
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39931449
your comment is to my post ?
gowflow
0
 

Author Comment

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

Expert Comment

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

Expert Comment

by:gowflow
ID: 39931521
can you post  sample of what you want to copy, maybe you have something that intervene with this.
gowflow
0
 
LVL 34

Expert Comment

by:Norie
ID: 39931588
Why not copy the design sheet?
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39936464
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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

705 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