Macro to send data from "Input" sheet to "Data" sheet

[mark2002]

Hi all,
Still quite new to macros and I am having difficulty creating one that sends data from one tab (titled "Input") to the "Data" tab.

Ideally, the macro would be able to take the data from the "Input" (first image), and send it to "Data" (second image) either replacing existing data (using Shape in column A as the reference) or adding the new shape and attributes.

Essentially, it would need to function as the example below. (the highlighted cells showing the new or updated data)

Currently, my macro is only able to take the top row from the "Input" sheet and send it to the "Data" sheet.
It is unable to send multiple rows, or replace any of the 'Shape' attributes.

Sub Input_Button()

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Long

Set sourceSheet = Sheets("Input")
Set dataSheet = Sheets("Data")

nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("B2").Value
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C2").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("D2").Value


sourceSheet.Range("B2:B200").Value = ""
sourceSheet.Range("C2:C200").Value = ""
sourceSheet.Range("D2:D200").Value = ""

End Sub


Please let me know if you can help.

Thanks you so much!
-Mark

 

[HaHoBe]

Hi Mark,

change code to

  Else
    Set rngNext = wsData.Cells(1, clngColSHAPE - 1).EntireColumn.Find(wsSrc.Cells(lngCounter, clngColSHAPE).Value)
    With rngNext
      .Offset(0, -2).Value = wsSrc.Cells(lngCounter, clngColMAIN).Value
      If Not IsEmpty(.Offset(0, -2).Value) Then .Offset(0, -2).Interior.ColorIndex = 6
      
      .Offset(0, -1).Value = wsSrc.Cells(lngCounter, clngColSUB).Value
      If Not IsEmpty(.Offset(0, -1).Value) Then .Offset(0, -1).Interior.ColorIndex = 6
      
      .Offset(0, 1).Value = wsSrc.Cells(lngCounter, clngColCOLOR).Value
      If Not IsEmpty(.Offset(0, 1).Value) Then .Offset(0, 1).Interior.ColorIndex = 6

Holger