Automatic copying the entered data on a summary sheet 'chronology' and paste as values only

navic

Active Member
Joined
Jun 14, 2015
Messages
290
Office Version
  1. 2013
Platform
  1. Windows
I need help with VBA code for Worksheet_Change event. VBA needs to automatically copied specific columns in the same row after entering data (name, string, number or text from dropdown menu).
I need this VBA use on multiple sheets. Destination sheet is named the "Chronology".

info:
- Workbook contains Force enable macro
- Sheet Chronology is hidden!
- Each sheet contains the same number of columns
- Sheet Chronology sometimes be protected by a password when I open my workbook (If I forget unprotected this sheet. It is necessary to appear a popup window for entering a password if it is protected.)
- Sheet tab name can be changed in the future.

This below is a simple example. In reality, each sheet contains 119 columns and 520 rows

Sheet A

ABCDEFGH
1Head1Head2Head3Head4Head5Head6Head7Head8
2Name1PO47
3Name2XYFromDropMenu
4Name3XY
5Name4PO
6Name5PO

<tbody>
</tbody>

Sheet B

ABCDEFGH
1Head1Head2Head3Head4Head5Head6Head7Head8
2Name12XY
3Name13XY
4Name14PO33
5Name15XY
6Name16PO

<tbody>
</tbody>

Sheet C

ABCDEFGH
1Head1Head2Head3Head4Head5Head6Head7Head8
2Name33PO
3Name34PO101
4Name35PO
5Name36XY
6Name37PO

<tbody>
</tbody>


Destination Sheet Chronology

ABCDEFGHIJ
1Date/Time -
Changed Data
Sheet tab NameHead1Head2Head3Head4Head5Head6Head7Head8
220.01.2017 15:20AName1PO47
320.01.2017 15:21AName2XYFromDropMenu
423.01.2017 09:45BName14PO33
523.01.2017 17:31CName34PO101
6

<tbody>
</tbody>





Can someone help me?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

navic

Active Member
Joined
Jun 14, 2015
Messages
290
Office Version
  1. 2013
Platform
  1. Windows
Incredibly, or nobody knows or some Excel experts do not want to waste his time? :(
 

navic

Active Member
Joined
Jun 14, 2015
Messages
290
Office Version
  1. 2013
Platform
  1. Windows
I'm trying with this VBA code, but copies the entire row, and does not begin in the D column as a destination.
Can someone modify this VBA + date/time
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Copy only the changed data + always copy columns A and B in same row and add the date-time copying
            Application.EnableEvents = False
    If Target.Column > 1 Then 'I can change col A-CK (columns from 1 to 89)- 1st attempt
    'If Target.Column <> "" Then 'I can change col A-CK (columns from 1 to 89) - 2nd attempt
        If UCase(Target.Value) <> "" Then
            Target.EntireRow.Copy Destination:=Sheets("Chronology"). _
            Range("A" & Rows.Count).End(xlUp).Offset(1)
            Target.EntireRow.Copy 'need be copied A,B columns and changed data only !!!
        End If
    End If

        Application.CutCopyMode = False
    Range("A1").Select 'I need select cell below changed cell

        ' Reset EnableEvents
            Application.EnableEvents = True
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,517
Messages
5,602,113
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top