Here's how to copy and paste data with specific formatting using macros in Excel:

Method 1: Using Paste Special with VBA

This method utilizes the `PasteSpecial` command within your macro to control how the data is pasted.

```vba
Sub CopyPasteWithFormat()

' Define the range to copy
Dim copyRange As Range
Set copyRange = Range("A1:B10") ' Replace with your actual range

' Copy the data
copyRange.Copy

' Define the destination range
Dim pasteRange As Range
Set pasteRange = Range("C1") ' Replace with your destination

' Paste with formatting using PasteSpecial
pasteRange.PasteSpecial xlPasteValuesAndFormats

End Sub
```

Explanation:

1. We define two ranges: `copyRange` for the data to be copied and `pasteRange` for the destination.
2. The `Copy` method copies the data from `copyRange`.
3. The `PasteSpecial` method with the argument `xlPasteValuesAndFormats` pastes the copied data into `pasteRange` while preserving the original formatting.

Method 2: Looping and Formatting

This method loops through each cell in the copied range and applies the desired formatting individually.

```vba
Sub CopyPasteWithFormatLoop()

' Define the range to copy
Dim copyRange As Range
Set copyRange = Range("A1:B10") ' Replace with your actual range

' Define the destination range
Dim pasteRange As Range
Set pasteRange = Range("C1") ' Replace with your destination

' Copy the data
copyRange.Copy

' Loop through each cell and apply formatting
Dim cell As Range
For Each cell In pasteRange
cell.Value = copyRange.Cells(cell.Row - pasteRange.Row + 1).Value ' Copy value
cell.Font.Bold = True ' Apply formatting (adjust formatting as needed)
cell.Font.Color = vbRed ' Apply formatting (adjust formatting as needed)
Next cell

End Sub
```

Explanation:

1. Similar to method 1, we define ranges for copying and pasting.
2. The data is copied using the `Copy` method.
3. We loop through each cell in the `pasteRange`.
4. The value from the corresponding cell in the `copyRange` is assigned using relative cell referencing.
5. Inside the loop, you can apply desired formatting to each cell (e.g., bold font, red color).

Remember to replace the example ranges (`A1:B10` and `C1`) with your actual data and desired destination.

These are two common approaches for copying and pasting data with specific formatting using macros in Excel. Choose the method that best suits your needs and adjust the formatting commands within the loop for specific customization.