How to select last visible cell in column and then copy paste it to another sheet ?

[Tullzter]

Hello all,

this is my first post as i've recently begun to dabble in VBA..and to be fair i'm quite amazed at how useful it is.

I need your help today with a code i'm writing that would allow me to copy the last visible cell from a given column (B) onto a new sheet, here is my code so far but i'm getting an issue clearing the copy part

Worksheets("Summary").Activate
Range("B" & Cells.Rows.Count).End(xlUp).Select.Copy
Worksheets(2).Activate
Range("d1").Paste

thank you for your help, i appreciate it

 

[Peter_SSs]

Welcome to the MrExcel board!

As you 'dabble' more in vba you will find that is is seldom necessary to Activate or Select things to work with them. Further, those two actions slow the code which can be important if the code is doing a lot.

Try this (no Activate or Select)

Worksheets("Summary").Range("B" & Cells.Rows.Count).End(xlUp).Copy Worksheets(2).Range("d1")

 

[Tullzter]

Thank you sir, it worked like a charm but say i want to copy paste formats and values, how would i go about doing it ?

 

[Peter_SSs]

Thank you sir, it worked like a charm but say i want to copy paste formats and values, how would i go about doing it ?

A few more steps, but still no activate/select.

Worksheets("Summary").Range("B" & Cells.Rows.Count).End(xlUp).Copy
With Worksheets(2).Range("D1")
  .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  .PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False

 

[Tullzter]

Thank you so much ! you've been a great help

 

[Peter_SSs]

Thank you so much ! you've been a great help

You are very welcome. Thanks for the follow-up.