Pasting only Background/Number Formatting and values with Merged Cells

carlob

New Member
Joined
Jan 1, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone
I am attempting to copy a specific region's contents and copy the Current Region back to a summary page.

I have unfortunately run into a bit of an issue with pasting the data back to the summary sheet.

Desired Outcome:
  1. Maintain Merged Cells
  2. Maintain Cell Highlight Colors
  3. Cell Values
Copy Source
The source area has some formulas within it to gather some sheet data. See Picture Below:
Excel_Formatting_1.JPG

Now whenever I go to paste the selected region, via means of my vba code

VBA Code:
TitleBlockRange.CurrentRegion.Copy
nextEmptyCell.PasteSpecial (xlPasteFormats)       
nextEmptyCell.PasteSpecial (xlPasteValues)

I get the following error:
Excel_Formatting_2.JPG


I have tried every single variation of PasteSpecial() that I can think of!

Is there a method that I am missing here? What method should I be using?

Thank you all in advance!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Is nextEmptyCell one cell?
Did you try xlPasteAll ?

Excel Formula:
nextEmptyCell.PasteSpecial  xlPasteAll
 

carlob

New Member
Joined
Jan 1, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Is nextEmptyCell one cell?
Did you try xlPasteAll ?

Excel Formula:
nextEmptyCell.PasteSpecial  xlPasteAll
Yes, nextEmptyCell is a single Cell. My Code Looks for "Requirement Max/min Values" and then copies that cells' region. (I will paste a code snippet in my original post)

I cannot use xlPasteAll bc it brings along the formulas from the other sheets. This then ruins the values within the pasted range as the formulas are now pointing at nothing.
Other it than bringing along the formulas though it works great!!!!
 

carlob

New Member
Joined
Jan 1, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Sorry, I was not able to figure out how to edit my initial post.

See below for the code snippet that I am working with:


VBA Code:
'Copy Found address and all adjacent formated cells
        TitleBlockRange.CurrentRegion.Copy

        'Activate the Summary Sheet
        Sheets("Summary").Activate
                
            'Set Inital_Copy_Count to ensure that the summary macro always starts in the same place
            'Start If {3}
            If Inital_Copy_Count < 1 Then
                Inital_Copy_Count = Inital_Copy_Count + 1
                'Sets copy range to the next available empty row
                Set nextEmptyCell = Range("A" & Rows.Count).End(xlUp).Offset(10)
                                
    
                'nextEmptyCell.PasteSpecial (xlPasteAll)
                
                                                
                nextEmptyCell.PasteSpecial (xlPasteFormats)
                
                nextEmptyCell.PasteSpecial (xlPasteValues)
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400

ADVERTISEMENT

Try using xlPasteAll followed by xlPasteValues to overwrite the formulas

VBA Code:
nextEmptyCell.PasteSpecial  xlPasteAll
nextEmptyCell.PasteSpecial  xlPasteValues
 

carlob

New Member
Joined
Jan 1, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
So it seems that if you use :

VBA Code:
nextEmptyCell.PasteSpecial (xlPasteAll  
nextEmptyCell.PasteSpecial (xlPasteValues)

It will throw error '1004' as shown in the original post, however if you change it to the following:

VBA Code:
nextEmptyCell.PasteSpecial (xlPasteAll)
nextEmptyCell.PasteSpecial (xlPasteValuesAndNumberFormats)

We are able to overwrite the formulas from the
VBA Code:
xlPasteAll

The only other issue now is the annoyance of the following "pop-up" whenever we get to the
VBA Code:
nextEmptyCell.PasteSpecial (xlPasteValuesAndNumberFormats)
line of code.

In an effort to keep posts focused and organized, I will ask how to remove this box in another thread.

Thanks AlphaFrog!

Excel_Formatting_3.JPG
 
Solution

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
You're welcome.

Try this...

Excel Formula:
Application.DisplayAlerts = False
nextEmptyCell.PasteSpecial (xlPasteAll)
nextEmptyCell.PasteSpecial (xlPasteValuesAndNumberFormats)
Application.DisplayAlerts = True
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top