VBA recording data entry and create a new excel with all these

bhupender

New Member
Joined
Sep 24, 2013
Messages
17
hello friends,
I need your help on this.

I do a lot of data entry in a excel file with multiple sheets with exactly same format. Sometimes after entering data in multiple sheets, I need to get a list of new entries I have done in a specific column of every sheet.
so please help me with a code. exactly I am looking for this.
as soon as I start the macro, it starts recording the value I am entering into cell A for all the sheets in one workbook.
after I complete data entry I want the macro to create a new excel sheet with the data I have punched in starting from the time when the macro started.

Hope I am not ambiguous in want I want.


thanks
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
720
Every sheet must have this code on sheet module.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then
        Exit Sub
    Else
        ReDim Preserve x(i)
            x(i) = Target.Value
            i = i + 1
    End If
End Sub

In General module.
Code:
Option Explicit
Public x(), i As Long
Sub g()
Dim ws As Worksheet, j As Long
Sheets.Add
Set ws = ActiveSheet
With ws
      For j = 0 To UBound(x)
        .Cells(j + 1, 1).Value = x(j)
      Next
      .Range(.Range("A1"), .Cells(i, 1)) = WorksheetFunction.Transpose(x)
End With
End Sub
 

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
I don't think you have to insert the event code in every sheet module. You should be able to use the Workbook_SheetChange() event instead.

To turn the tracking on and off, you probably want another global variable that determines whether each change should be tracked or not. There would be two procs, one to turn on the tracking and one to turn it off. The latter would also call the code - something like g() - to generate the output.
 

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
720
Thank you iliace. I totally forgot. I am able to use sheetchange event on Thiswokbook module...

ThisWorkbook Module
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     If Intersect(Target, Range("A:A")) Is Nothing Then
        Exit Sub
    Else
        ReDim Preserve x(i)
            x(i) = Target.Value
            i = i + 1
    End If
 End Sub

General Module
Code:
Sub test()
Dim ws As Worksheet, j As Long
Sheets.Add
Set ws = ActiveSheet
With ws
      .Range(.Range("A1"), .Cells(i, 1)) = WorksheetFunction.Transpose(x)
      Erase x
End With
End Sub
 

bhupender

New Member
Joined
Sep 24, 2013
Messages
17
Thanks takae & iliace,

I tried this code but it showing error as "Variable not defined".
also, will this code work on every open workbook??

thanks
 

Takae

Well-known Member
Joined
Jul 1, 2015
Messages
720
Sorry I droped declarations and added a line.
Please replace the old code with below code on general module.
Code:
Option Explicit
Public x(), i As Long
Sub test()
Dim ws As Worksheet, j As Long
Sheets.Add
Set ws = ActiveSheet
With ws
      .Range(.Range("A1"), .Cells(i, 1)) = WorksheetFunction.Transpose(x)
      Erase x
      i = 0
End With
End Sub

This code works on this workbook only.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,089
Members
414,501
Latest member
mdhaumyu

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