Store cell values from One sheet to another VBA

  • Hi Carim and royUK,

    I want to update value from one sheet to another sheet. Basically Data from the "dashboard sheet" copies to the sheet name reference in the dashboard sheet "C4" with the respective headers. ( Referencing which sheet the data should be updated/edited)

    And the highlighted part ( B13:C13) should be filtered to update price and currency.

    Please refer to Sheet - "Required Output' for better understanding.

    Kindly refer to this macro - sub ADD() from the attached sample sheet which is not working as intended.

    Your thoughts are highly appreciated,


    • Sample 1.xlsm

      (43.81 kB, downloaded 20 times, last: )
  • If you have a With Statement then you do not refer to the Sheet in the code between the statement.

    1. With Sheet5
    2. .Range("A1:Z1" & last).AutoFilter Field:=1, Criteria1:=Sheet5.Range("C5").Value, Criteria2:=Sheet5.Range("C6").Value

    Should be

    1. With Sheet5
    2. .Range("A1:Z1" & last).AutoFilter Field:=1, Criteria1:=.Range("C5").Value, Criteria2:=.Range("C6").Value

    Is the criteria on Sheet5 which is Cart_3?

    I'// sort this in the morning, I'm finishing for today.

  • 1) Please refer to the image attached. The highlighted arrow mark is where sheet name to be selected in which the dashboard data will be updated.

    2) Origin and destination will be given as full name. But only ID should be updated instead of full name.(Kindly refer to the image 2)

    For eg Input data - Alabama. Output - AL (lists sheet)

    3)VBA should identify coloumn header "33" and update the value at intersection point as 1256 and move to the next coloumn header "33c" and update the currency.(Kindly refer to the image 3)

    Thank you so much for your efforts.!!!! Please take care.

  • You say you want to transfer the entries from the DashBoard sheet to the relevant Cart sheet.

    If this is correct then you need the data to be in the same order as the destination sheet header row to be able to do this efficiently.

    Also, Dashboard has the state name not the code.

    Why are you using AutoFilter in your code?

  • I think this is what you want

  • Thank you so much for your reply, RoyUK!!

    The given macro works well to update the data on the required sheet.[ But only cell range (A:F) has updated. (K:L) has not updated. ]

    Why are you using AutoFilter in your code?

    Autofilter code helps to identify the required header and row to edit the data.

    Scenario - Coloumn name - 33 and 33C has to be reupdated/edited its price to 3750 and CAD respectively ,corresponding to the origin and destination.

    Autofilter function filters the required origin and destination to be edited and update 1256 -> 3750 (Coloumn header - 33) and USD to CAD (Coloumn header -33C)

    Attached sample macro which has to be replicated.. (the header name gets updated instead of matrix update) -Kindly refer workbook - "M"

    Basically I'm looking for matrix lookup function in vba which has 3 criterias ( Origin, destination and coloumn header"33")

    and the data should be updated.

    Hope you understand what I'm looking for..

    Thanks again!!!!