VBA复制指定区域为图片问题：如何复制含表头的最后10列数据

Got it, let's tackle this problem! Your current code uses CurrentRegion which grabs the entire contiguous block starting at B2, but we need to dynamically target the range from column F to the last used column (including the header) whenever new data is added (like your O column example). Here's how to adjust it:

Modified VBA Code #

Sub CopyTargetRangeAsPicture()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastUsedColumn As Long
    Dim rangeToCopy As Range
    
    ' Set direct references to your sheets (avoids flaky Select/Activate calls)
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
    Set targetSheet = ThisWorkbook.Worksheets("Sheet2")
    
    ' Find the last column with data in your header row (assuming header is in row 1)
    lastUsedColumn = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column
    
    ' Define the range: from F1 (header) down to the last row in column F, across to lastUsedColumn
    Set rangeToCopy = sourceSheet.Range( _
        "F1", _
        sourceSheet.Cells(sourceSheet.Cells(sourceSheet.Rows.Count, "F").End(xlUp).Row, lastUsedColumn) _
    )
    
    ' Copy the range as a screen bitmap
    rangeToCopy.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    
    ' Paste to Sheet2 starting at cell B2
    targetSheet.Range("B2").PasteSpecial
End Sub

Key Improvements & Explanations #

• No more Select/Activate: Directly referencing worksheet objects makes your code more reliable and faster (Select is prone to errors if users click around while the macro runs).
• Dynamic last column detection: sourceSheet.Cells(1, Columns.Count).End(xlToLeft).Column automatically finds the rightmost column with data in your header row. So if you add data to column O (or any other column), it will be included without manual updates.
• Header-inclusive range: We start the range at F1 (your header) instead of a data row, so the header is always included. The range extends down to the last row with data in column F, ensuring all relevant rows are captured.

Quick Adjustment Tip #

If your header isn't in row 1 (e.g., it's in row 2), just update two parts:

1. Change sourceSheet.Cells(1, ...) to sourceSheet.Cells(2, ...) when finding lastUsedColumn
2. Start the range at F2 instead of F1

内容的提问来源于stack exchange，提问作者step