Copy pasting macro fails to copy the needed data if column/row is added

[Guims]

Hi, I'm trying to create a macro that copy-pastes data from one worksheet to another. The macro has to copy four cells from one sheet and paste them in 4 cells in another. the issue is that, post macro recording, I am changing both sheets (adding or deleting rows and columns) so the macro copy-pastes the cells I initially inserted (say B1:B4) despite the wanted data having moved due to the new columns or rows that have been added/deleted (i.e now in C1:C4 if I added a column). Is it possible to make macros that "follow" the cells you copy the same way a formula does (formulas adapt the referred cells when you add/remove a column or row) so that it still copies the 4 cells I want to copy (now C1:C4 in the example) even if they are not located at the same place they were while I was creating the macro (B1:B4 in the example? The same applies to the place where the macro pasts in the pasting worksheet if I add/remove columns or rows.

Thanks in advance.

 

[DanteAmor]

Hi and welcome to MrExcel!

It could be done in 2 ways.
1. You can name the 4 cells in named range. That way in the macro you would refer to the named range, eg .:

Sub copynamed()
  Range("MyCells").Copy
End Sub

2. The other option is more complex, you would have to put a title above the 4 cells, the macro would look for that title on the sheet and take the 4 cells below. eg.:

Sub copycells()
  Dim f As Range
  Set f = Cells.Find("Mytitle", , xlValues, xlWhole)
  If Not f Is Nothing Then
    f.Offset(1).Resize(4).Copy
  End If
End Sub

 

[Guims]

Hi Dante,

Thank you so much this works very well! I'll definitely be using this site a lot as of now

Guillaume

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.