Permanently copy from cell to next sheet

[Excelnoub]

Good morning/afternoon,
I am trying to find a VBA or formula that will let me do the following:
I have information to add in Cell A5 of sheet 1. Every time I insert info in this cell I want this information to be transferred in Sheet 2 Cell A5. It is not a copy and paste as I want this information to be permanently inserted in Sheet 2 Cell A5. If I delete the information in Sheet 1 Cell A5 then I still want to have Sheet 2 Cell A5. Then, when I insert another text in Sheet 1 Cell A5, I need it to copy the information on the next line in Sheet 2 Cell A6.


Sheet 1 A5=123 therefore Sheet 2 A5=123 if I delete Sheet 1 A5=123, Sheet 2 is still A5=123

If I add more info to Sheet1 A5=456 then Sheet 2 add a line after A5=123 therefore A6=456

Let me know if this is complicated.

I also need this information to be transfered to a Sheet 3 but that I will figure it out.

 

[wigi]

For instance, a Worksheet_Change event on that first sheet. Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$5" Then Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1) = Target.Value
End Sub

As long as sheet 2 is the second sheet in the file, and that sheet has a header in cell A4, it will work.

 

[HalfAce]

Right click the sheet tab, choose View Code and paste this in the sheet module. Press Alt+Q to close the vb editor and check it out.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$5" Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Sheets("Sheet2").Range("A5") = "" Then
  Sheets("Sheet2").Range("A5") = Target.Value
  Sheets("Sheet3").Range("A5") = Target.Value
Else
  Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2).Value = Target.Value
  Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2).Value = Target.Value
End If
End Sub

Hope it helps.

 

[Excelnoub]

Right click the sheet tab, choose View Code and paste this in the sheet module. Press Alt+Q to close the vb editor and check it out.

Hope it helps.

Thank you for the Help... Error on the Code Sheet, I already have this code that helps with the Wrap text cause I have merged cells that need to expand. Where would I insert the new code?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With

 

[HalfAce]

I would look at something like this.
Note, if the target was A5 and you don't want to perform the rest of the code in those cases, you can insert an 'Exit Sub' where I have it noted.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range


If Target.Address = "$A$5" Then
  If Sheets("Sheet2").Range("A5") = "" Then
    Sheets("Sheet2").Range("A5") = Target.Value
    Sheets("Sheet3").Range("A5") = Target.Value
  Else
    Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2).Value = Target.Value
    Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp)(2).Value = Target.Value
  End If
[COLOR=DarkGreen]'''  (You can put an Exit Sub right here if you like)[/COLOR]
End If


With Target
  If .MergeCells And .WrapText Then
    Set c = Target.Cells(1, 1)
    cWdth = c.ColumnWidth
    Set ma = c.MergeArea
    For Each cc In ma.Cells
      MrgeWdth = MrgeWdth + cc.ColumnWidth
    Next
    Application.ScreenUpdating = False
    ma.MergeCells = False
    c.ColumnWidth = MrgeWdth
    c.EntireRow.AutoFit
    NewRwHt = c.RowHeight
    c.ColumnWidth = cWdth
    ma.MergeCells = True
    ma.RowHeight = NewRwHt
    cWdth = 0: MrgeWdth = 0
    Application.ScreenUpdating = True
  End If
End With

Does that help?

 

[Excelnoub]

Does that help?

I love you guys, You guys need a raise lol

Thank you for everything.

 

[Excelnoub]

I love you guys, You guys need a raise lol

Thank you for everything.

So The code works… but I have another problem…

I have 5 sheets, one that identifies the first step in the process therefore some information will always be the same as file numbers and also I need dates to be copied permanently to the last page as a final report. This one I know and tested to formula with the last code...
So lets say that Sheet1 Cell A5 is my starting point of my document where I will start inserting file numbers… I need this info in all the pages but permanently, code was complete.

I also need Sheet1 Cell A6 to do the same IF Cell A5 is populated, but I need them to be copied to the next available line IF it is not populated starting from A5 in all other sheets, so 2,3,4,5.

So Sheet1
Cell A5 = File number. IF Cell A5 is populated then Perm copy to Cell A5 in Sheets,2-3-4-5
IF Cell A6 = File number. IF Cell A6 is populated then Perm copy to Cell A6 in Sheets,2-3-4-5

IF Sheet1 A5 is deleted and I add another File number to Sheet1 A5, Then Sheet1 A5 Perm copy to Cell A7 in Sheets,2-3-4-5 as A6 is already populated.

Damm I need to learn more this VBA stuff lol my head need a tylenol...

 

[Excelnoub]

So The code works… but I have another problem…

I have 5 sheets, one that identifies the first step in the process therefore some information will always be the same as file numbers and also I need dates to be copied permanently to the last page as a final report. This one I know and tested to formula with the last code...
So lets say that Sheet1 Cell A5 is my starting point of my document where I will start inserting file numbers… I need this info in all the pages but permanently, code was complete.

I also need Sheet1 Cell A6 to do the same IF Cell A5 is populated, but I need them to be copied to the next available line IF it is not populated starting from A5 in all other sheets, so 2,3,4,5.

So Sheet1
Cell A5 = File number. IF Cell A5 is populated then Perm copy to Cell A5 in Sheets,2-3-4-5
IF Cell A6 = File number. IF Cell A6 is populated then Perm copy to Cell A6 in Sheets,2-3-4-5

IF Sheet1 A5 is deleted and I add another File number to Sheet1 A5, Then Sheet1 A5 Perm copy to Cell A7 in Sheets,2-3-4-5 as A6 is already populated.

Damm I need to learn more this VBA stuff lol my head need a tylenol...

Up

 

[Excelnoub]

Better Scenario:
Sheet1

Cell A5 needs to be transferred "Copied" to all Sheets Cell A5 (2,3,4,5) permanently.

Cell B5 needs to be transferred "Copied" to Sheet5 Cell B5 permanently.

Cell D5 needs to be transferred "Copied" to Sheet5 Cell D5 permanently.
(This is a drop down selection, the selection should be transferred and not the validation (Formula))

Cell E5 needs to be transferred "Copied" to Sheet5 Cell G5 permanently.

Sheet2
Cell B5 needs to be transferred "Copied" to Sheet5 C5 permanently.
Cell F5 needs to be transferred "Copied" to Sheet5 E5 permanently.

Sheet3
Cell B5 needs to be transferred "Copied" to Sheet5 F5 permanently. B5 has a formula [=IF(Sheet2!F5="","",Sheet2!F5+90)] therefore I only need the answer from B5 to be transferred to Sheet5 F5

Sheet4 will not be copied/transferred info only