Pasting only Background/Number Formatting and values with Merged Cells

[carlob]

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:

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

TitleBlockRange.CurrentRegion.Copy
nextEmptyCell.PasteSpecial (xlPasteFormats)       
nextEmptyCell.PasteSpecial (xlPasteValues)

I get the following error:

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!

 

[AlphaFrog]

Is nextEmptyCell one cell?
Did you try xlPasteAll ?

nextEmptyCell.PasteSpecial  xlPasteAll

 

[carlob]

Is nextEmptyCell one cell?
Did you try xlPasteAll ?

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]

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:

'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]

Try using xlPasteAll followed by xlPasteValues to overwrite the formulas

nextEmptyCell.PasteSpecial  xlPasteAll
nextEmptyCell.PasteSpecial  xlPasteValues

 

[carlob]

So it seems that if you use :

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:

nextEmptyCell.PasteSpecial (xlPasteAll)
nextEmptyCell.PasteSpecial (xlPasteValuesAndNumberFormats)

We are able to overwrite the formulas from the

xlPasteAll

The only other issue now is the annoyance of the following "pop-up" whenever we get to the

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!

 

[AlphaFrog]

You're welcome.

Try this...

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