Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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?
0
DemonForce
Asked:
DemonForce
  • 4
  • 4
  • 3
  • +1
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Not similar but the

copy
Pastespecial

combination can do this.
0
 
DemonForceAuthor Commented:
Yeah used that, but is there a faster way?
0
 
Saqib Husain, SyedEngineerCommented:
What formatting are you trying to copy over?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DemonForceAuthor Commented:
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
 
gowflowCommented:
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
 
DemonForceAuthor Commented:
Yeah, I tried those before but even with switch off screnupdating and events I get flicker
0
 
gowflowCommented:
your comment is to my post ?
gowflow
0
 
DemonForceAuthor Commented:
gowflow, yes I have tried that and it works, but not fast enough
0
 
Saqib Husain, SyedEngineerCommented:
Actually borders are a slow process and I do not see any way out of it.
0
 
gowflowCommented:
can you post  sample of what you want to copy, maybe you have something that intervene with this.
gowflow
0
 
NorieCommented:
Why not copy the design sheet?
0
 
gowflowCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now