Forum Discussion

roboed's avatar
roboed
Copper Contributor
Jun 08, 2022
Solved

Macro to copy/paste to a specific cell

Hi. Need some guiding here...

 

Assume the following data in normal cells (not a table). Data is generated via various drop-downs and formulas etc.

 

 AB
1Unique IDData
2555Data5
3666Data6

 

So far all good. Then, I want a button/macro to do the following:

 

  • Copy A2:B3
  • Paste it in the table below ("output_table"), BUT
    • Find the correct Unique ID corresponding to the first one (here: 555)
    • Paste the copied values starting in the matching cell (555)
  • The output_table is table formatted as a table
  • The output_table is static and will always contain all Unique IDs possible
  • Macro needs to work both up and down regardless of cells in output_table being empty or not. Sometimes values will be overwritten, other times filled for the first time. ONLY criteria is it needs to paste from the correct Unique ID.
  • The copied IDs will always be sequential and never have any gaps (Never 333 then 555). So just the first Unique ID to be located, then the rest will always match.
 DE
10Unique IDData
11333Data3
12444Data4
13555Data5
14666Data6

"output_table"

 

Any help to correct code? Thanks a lot in advance!

 

Robert, 

Norway

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 08, 2022

    roboed 

    Sub CopyData()
    Dim rng As Range
    Set rng = ActiveSheet.ListObjects("Table1").ListColumns("Unique ID").DataBodyRange.Find(What:=Range("A2").Value, LookAt:=xlWhole)
    If rng Is Nothing Then
        Beep
    Else
        Range("A2:B3").Copy Destination:=rng
        Application.CutCopyMode = False
    End If
End Sub

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 08, 2022

    roboed 

    Sub CopyData()
    Dim rng As Range
    Set rng = ActiveSheet.ListObjects("Table1").ListColumns("Unique ID").DataBodyRange.Find(What:=Range("A2").Value, LookAt:=xlWhole)
    If rng Is Nothing Then
        Beep
    Else
        Range("A2:B3").Copy Destination:=rng
        Application.CutCopyMode = False
    End If
End Sub
    • roboed's avatar
      roboed
      Copper Contributor
      Jun 14, 2022
      Thanks Hans! With some tweeking it worked - but you got me in the right direction. Much appreciated.

      Robert

Resources