如何不使用Select/Selection简化VBA复制单元格格式的代码？

Nice call moving away from Select and Selection—those methods are slow, prone to errors if the active sheet changes mid-execution, and generally make code harder to debug and maintain. Since you already know how to assign ActiveSheet to a Worksheet object, let's trim down your code to its most efficient, clean form.

Streamlined, Select-Free Code #

' Assign your target worksheet to a variable (replace with your sheet name for specificity if needed)
Dim ws As Worksheet
Set ws = ActiveSheet

' Copy formats from column C to columns A & B in the specified row
ws.Range("C" & SelRowNumber).Copy
ws.Range("A" & SelRowNumber & ":B" & SelRowNumber).PasteSpecial xlPasteFormats

' Optional: Clear the clipboard to remove the "marching ants" selection
Application.CutCopyMode = False

Breakdown of the Key Improvements #

• No more unnecessary selection: We directly interact with ranges via the ws worksheet object, so the code doesn't waste time updating the screen or relying on the active selection state (a common source of bugs).
• Simplified PasteSpecial: Since you're using the default values for Operation, SkipBlanks, and Transpose, you can omit those named arguments entirely—xlPasteFormats is the first parameter, so it works perfectly on its own.
• Clipboard cleanup: Adding Application.CutCopyMode = False is a small but useful touch that frees up system resources and removes the distracting selection outline after the code runs.

Quick Note on One-Liners #

You might be tempted to condense this into a single line with Copy Destination:=, but that copies all cell content (values, formulas, formats) instead of just formats. Stick with the two-line Copy + PasteSpecial approach to match your original code's intent.

If you only needed to copy number formats specifically, you could use ws.Range("A" & SelRowNumber & ":B" & SelRowNumber).NumberFormat = ws.Range("C" & SelRowNumber).NumberFormat, but PasteSpecial xlPasteFormats covers all formatting (font, fill, borders, alignment, etc.), which is exactly what your original code does.

内容的提问来源于stack exchange，提问作者Student of the Digital World