Forum Discussion

hopr37 hotmail's avatar
hopr37 hotmail
Brass Contributor
Apr 13, 2018
Solved

when cell value changes, copy the information to a new worksheet but also retain each change

Hello. How would I copy a cell ( or row actually) to a new worksheet when I make a change to a particular cell in the first worksheet? AND whenever I make a change to that particular cell it would n...
Changing cell value
Macros & VBA
  • Logaraj Sekar's avatar
    Logaraj Sekar
    Apr 17, 2018

    Try this.

     

    But after entering the value press enter, don't try downarrow / tab .

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
    ActiveCell.Offset(-1, 0).Activate
    Sheets("Sheet2").Range("A" & a).Value = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    End If
    End Sub

     

Logaraj Sekar's avatar
Logaraj Sekar
Steel Contributor
May 01, 2020
Just tell me the sheet names of your worksheet. And what things you want to do?
joe_phinazee's avatar
joe_phinazee
Copper Contributor
May 07, 2020

Logaraj Sekar 

 

So I have a "Cast Sheet" which is where I work off of constantly, and when column F status changes, I want the entire row copied and pasted to "Cast Worked" to act as a data base so I can build reports and charts from it. 

 

Currently this works:

 

Sub CopyPaste()

 

If Not Intersect(myTarget, Range("F:F")) Is Nothing Then

 

myTarget.Activate
a = Sheets("Cast Worked").Cells(Rows.Count, "B").End(xlUp).Row + 1
myTarget.EntireRow.Copy Destination:=Sheets("Cast Worked").Range("A" & a)
myTarget.Offset(1, 0).Select

 

End If

 

End Sub

 

This could be refactored because it shouldn't carry over any formatting. Also I have a macro in the quick access tool bar that pushes new data into "Cast Sheet" to be worked, and when the macro pushes the new data, that data is also copied and pasted to "Cast Worked."

 

Please advise as to how I can refactor my code.

  • joe_phinazee's avatar
    joe_phinazee
    Copper Contributor
    May 19, 2020

    Logaraj Sekar 

     

    That is the correct functions. I actually went back and removed the macro to format since it's not necessary. I updated the copy macro to just copy specific columns. I've refactored my code and it works a lot better if you wish to take a look.

  • Logaraj Sekar's avatar
    Logaraj Sekar
    Steel Contributor
    May 19, 2020

    joe_phinazee 

     

    So the process of macro is

     

    1. bringing selected data from new weekly data to "cast sheet"

    2. then when you made changes in "column F" in cast sheet it will bring the row to "cast worked"

     

    If this is ok. your macro over. If not explain.

     

    1. Why there is a macro "format cells"? 

  • joe_phinazee's avatar
    joe_phinazee
    Copper Contributor
    May 15, 2020

    Logaraj Sekar 

     

    The last one I sent you is how we get our new weekly data. How I have it on 'CS Sheet' is how the data is to be transformed.

  • Logaraj Sekar's avatar
    Logaraj Sekar
    Steel Contributor
    May 14, 2020

    joe_phinazee 

     

    You have sent "new weekly data" in two different formats. which one you want to keep.?

  • joe_phinazee's avatar
    joe_phinazee
    Copper Contributor
    May 13, 2020
    Hey,

    I just wanted to check to see if you have had a chance to review my workbook yet?