CODEWITHSUNDEEP
vbaexcel
step
step

Reputation: 51

vba copy certain range and paste as picture

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

Upvotes: 2

Views: 28293

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37500

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

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21657

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:

Upvotes: 4

Related Questions