condensing lines of code

[stephicohu]

This is Stephanie, again, I was informed that I didn't need to use select option in coding as much. I am wondering if I could condense this code:

Sheets("5 month").Activate
Range(Cells(3, 33), Cells(7, 33)).Copy
Sheets("Sheet2").Activate
Range(Cells(3, 2), Cells(7, 2)).Select
ActiveSheet.Paste Link:=True

Can I have combine the sheets("5 month").Range(Cells(3,33), Cells(7,33)).Copy?

Thanks

 

[Skyybot]

If you don't want to use Activate often then you must be very specific with your references.

Sub test()
Dim wb As Workbook, sht2 As Worksheet, mon As Worksheet
Dim cpyRng As Range, pstRng As Range

Set wb = ThisWorkbook: Set sht2 = wb.Worksheets("Sheet2"): Set mon = wb.Worksheets("5 month")
Set cpyRng = Range(mon.Cells(33, 3), mon.Cells(33, 7))
Set pstRng = Range(sht2.Cells(3, 2), sht2.Cells(7, 2))

cpyRng.Copy
pstRng.PasteSpecial xlPasteAll

End Sub

 

[Alex Blakenburg]

Did you intentionaly Paste Link:=True ?
You can't do that without activating the sheet and selecting the destination.

PS: not using Activate & Select is not so much about condensing the code but more about speeding up the code since they make is slower and making the code easier to understand since it is often hard to keep track of what is the Active or selected worksheet or range.

 

[Alex Blakenburg]

If you did meant to link and wanted to condense the code, this should work:

Sub NoCopyLinkVersion()

    With Sheets("Sheet2")
        .Range(.Cells(3, 2), .Cells(7, 2)).Formula = "=" & Sheets("5 month").Cells(3, 33).Address(0, 0, xlA1, 1)
    End With

End Sub

The address parameters used:
expression.Address (RowAbsolute, ColumnAbsolute, ReferenceStyle, External)