Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

using PasteSpecial xlFormats

0

hello 

I  have  code  extract  data from two  sheets , but  the  problem  deletes  the  borders.

I  try  using PasteSpecial xlFormats  but  doesn't  work  . seem  wrong way  what  I  use  it .

the  range  from  column A: H  when  extract  data in sheet MASTER  .

this  the part  of  code  with  bold  what  I  try 

With Sheets("MASTER")
    .Range("A2:H" & lrP).Clear
    .Range("A2").Resize(k, 8).Value = arr
    .Range("E2").Resize(k, 4).Interior.Color = vbYellow
    .Range("E2").Resize(k, 4).SpecialCells(xlBlanks).Interior.ColorIndex = 0
    .Range("A2:H" & lrP).PasteSpecial xlFormats
End With

if  you  need  the  whole  code  I  will  post  it 

thanks  in  advance 

excel
MS Office Version: 2016
posted by: leap (rep: 46) on Thu Mar 31, 2022 at 4:25 am
Answer
Discuss

Answers

0

I  figure  out  how  fix  my  problem .

see   the  bold part 

With Sheets("MASTER")
    .Range("A2:H" & lrP).ClearContents
    .Range("A2").Resize(k, 8).Value = arr
    .Range("E2").Resize(k, 4).Interior.Color = vbYellow
    .Range("E2").Resize(k, 4).SpecialCells(xlBlanks).Interior.ColorIndex = 0
End With
posted by: leap (rep: 46) on Thu Mar 31, 2022 at 6:37 am
Discuss

Discussion

Leap

Good that you fixed the problem yourself.

As you asked (under my now-deleted Answer) "... why  PasteSpecial xlFormats, or xlPasteAll  don't work ", they normally follow a Copy statement like:
.Range(A2:H" & lrP).Copy 
 .Range("D1:D5").PasteSpecial _ 
  Operation:=xl
(as I asked).

If your copy and paste ranges are not the same size, you might get only top and left outside borders only (if the Paste range is smaller).
John_Ru (rep: 6142) Mar 31, '22 at 8:06 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login