vba copy certain range and paste as picture

1
votes

Now I can copy the whole borders from Sheet1 to Sheet2 but my problem is I can't copy the specific range like last 10 columns value with the header. For example, when input new value at O column it will just copy and paste the from F to O with the headers.

Worksheets("sheet1").Select
Set startcell = Range("B2")
startcell.CurrentRegion.CopyPicture xlScreen, xlBitmap
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste

example of image

3
vbaexcel

3 Answers

1
votes

Try this:

Sub CopyLastTenCols()
    'declaration of variables
    Dim sheet1 As Worksheet, sheet2 As Worksheet, lastCol As Long
    Set sheet1 = Worksheets("Sheet1")
    Set sheet2 = Worksheets("Sheet2")
    'here we determine last column to copy
    lastCol = sheet1.Cells(2, 2).End(xlToRight).Column
    'here, we union headers with last ten columns, then paste it to range
    'of appropriate size, starting in A1 cell
    Union(sheet1.Range("B2:B5"), sheet1.Range(sheet1.Cells(2, lastCol - 9), sheet1.Cells(5, lastCol))).Copy sheet2.Range("A1:K4")
End Sub
2
votes

Assuming this task is something you are able to do manually (without VBA), that you now want to automate:

Use the Macro Recorder to record the steps that you take to accomplish this manually, then Edit the macro to view the VBA code, remove any extraneous sections, and you will be left with the code you're looking for.

This process can be used for any task that you know how to do manually (with Excel's) built-in features, but you want to automate.

This is how I learned the first half of what I know about VBA today. It's a good "cheater" way to get started in VBA.

More Information:

0
votes

you may be after this

Sub main()
    With Worksheets("Sheet1").Range("B2").CurrentRegion
        If .Columns.Count > 11 Then .Columns(2).Resize(.Columns.Count - 11).EntireColumn.Hidden = True
        .CopyPicture xlScreen, xlBitmap
        Sheets("Sheet2").Range("B2").PasteSpecial
        .EntireColumn.Hidden = False
    End With
End Sub