VB/Macro to replicate data ?!

[Dhimit]

G'Day folks,

Can somebody please give me a 'STEP BY STEP' instruction (use of Macro/VB code?!) to replicate data from one or more worksheets to a TARGET worksheet ?

For example, if i create a record in 'XYZ' worksheet, it should create the same record in TARGET worksheet.
Likewise, if i create a record in 'ABC' worksheet in the same workbook, it should create the same record in the SUBSEQUENT ROW of the TARGET worksheet.

For this to happen, it must be done programmitically either using a MACRO or VB Script.

Are there any 'Excel experts' out there who can help me achieve my goal ?

Dhimit

 

[stanleydgromjr]

Dhimit,

Welcome to the board.

I assume that your data is in columns A thru E of all the sheets.


Please TEST this macro in a TEST copy of your workbook.

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Option Explicit
Sub CopyToTarget()
'
' CopyToTarget Macro
' Macro created 07/24/2007 by Stanley D. Grom, Jr.
'
' Keyboard Shortcut: Ctrl+t
'
    Dim lngTargetLastRow As Long
    Dim lngActiveCellRow As Long

    Application.ScreenUpdating = False

    lngTargetLastRow = Sheets("Target").Range("A" & Rows.Count).End(xlUp).Row + 1
    lngActiveCellRow = ActiveCell.Row
    Range(Cells(lngActiveCellRow, "A"), Cells(lngActiveCellRow, "E")).Copy Destination:=Sheets("Target").Cells(lngTargetLastRow, "A")

    Application.ScreenUpdating = True

End Sub

Please TEST this macro in a TEST copy of your workbook.

To assign the two key combination 'CTRL + t' to the macro:
Click on:
Tool, Macros, Macros...

Change the 'Macros In:' box to 'This Workbook'

Click on the 'CopyToTarget' macro, and click on the 'Options...' button.

In the 'Shortcut key:' box type in a 't' without the apostrophes.

Click on OK, and click on the 'X' in the upper right hand corner of the 'Macro' box.


After you enter a new row of data, press and hold down the 'CTRL' key, and press the 't' key to run the macro in any of the worksheets in the workbook except the 'Target' worksheet.

Have a great day,
Stan

 

[Dhimit]

RE;

Hi Stan,

Thanks for your promt reply.

I think you've fully understood my requirement, in a nutshell:

I want a summary of ALL row entries of ALL worksheets in a TARGET worksheet.

For example:

'ABC' Worksheet#1
ROW1 COL:A
ROW2 Peter
ROW3 JON

'XYZ' Worksheet#2
ROW1 COL:A
ROW2 Mary
ROW3 Jane

'Target' Worksheet#3
ROW1 COL:A
ROW2 Peter
ROW3 JON
ROW4 Mary
ROW5 Jane

Thanks again,
Dhimit

 

[Dhimit]

RE:

Hi again Stan,

The Macro works fine, however, the Macro gets disabled when I reopen the worksheet.

The error :

" Macro in this workbook are disabled because the security levle is high..
To run the macros, you can either have them sigend or change your security level."

Sorry to be a nuisance, can you tell me how to change the security level ?

Dhimit

 

[Dhimit]

Re:

Hi Stan,

I've managed to resolve the 'Security' issue - but can you advise on whether the rows in 'Target' worksheet can be refreshed AUTOMATICALLY
when rows are DELETED in SOURCE worksheets ?!

Dhimit

 

[Dhimit]

Attn: Stan

Hi stan,

Sorry to be a nuisance, do you know how i can delete the record from TARGET worksheet automatically when i delete a record from the SOURCE worksheet ?

Dhimit

 

[stanleydgromjr]

Dhimit,

"how i can delete the record from TARGET worksheet automatically when i delete a record from the SOURCE worksheet"

You have multiple 'SOURCE' sheets. 'ABC', 'XYZ'.

I am not aware of a Worksheet Event that can do this, but we can try with another macro.

Do you want to delete both rows in the source and target sheets, or just clear both rows? Or, do you actually want to delete both rows in both sheets?

If, in sheet 'ABC', you had the cursor in a cell in the row for the record you wanted to delete, what is the 'key' column to be to be used for the search of the 'Target' row? Column A, B, etc.?

Have a great day,
Stan

 

[Dhimit]

RE:

Hello again Stan,

Pls. have a look at the code below:

Sub CopyToTarget()
'
' CopyToTarget Macro
' Macro created 07/24/2007 by Stanley D. Grom, Jr.
'
' Keyboard Shortcut: Ctrl+t
'

Dim lngTargetLastRow As Long
Dim lngActiveCellRow As Long

Application.ScreenUpdating = False

lngTargetLastRow = Sheets("MacroKES").Range("A" & Rows.Count).End(xlUp).Row + 1
lngActiveCellRow = ActiveCell.Row
Range(Cells(lngActiveCellRow, "A"), Cells(lngActiveCellRow, "F")).Copy Destination:=Sheets("MacroKES").Cells(lngTargetLastRow, "F")

Application.ScreenUpdating = True

End Sub

Pls tell me why it's doing the following:

1. Copying from col. F onwards on 'target' worksheet
2. Copying only one record i.e NOT replicating records DOWNWARDS
3. OVERWRITING previous record when trying the copy next record from SOURCE worksheet

Thanks - DC

 

[Dhimit]

RE:

Hello again Stan,

Pls. have a look at the code below:

Sub CopyToTarget()
'
' CopyToTarget Macro
' Macro created 07/24/2007 by Stanley D. Grom, Jr.
'
' Keyboard Shortcut: Ctrl+t
' Dim lngTargetLastRow As Long
Dim lngActiveCellRow As Long

Application.ScreenUpdating = False

lngTargetLastRow = Sheets("MacroKES").Range("A" & Rows.Count).End(xlUp).Row + 1
lngActiveCellRow = ActiveCell.Row
Range(Cells(lngActiveCellRow, "A"), Cells(lngActiveCellRow, "F")).Copy Destination:=Sheets("MacroKES").Cells(lngTargetLastRow, "F")

Application.ScreenUpdating = True

End Sub

Pls tell me why it's doing the following:

1. Copying from col. F onwards on 'target' worksheet
2. Copying only one record i.e NOT replicating records DOWNWARDS
3. OVERWRITING previous record when trying the copy next record from SOURCE worksheet

Thanks - DC

 

[stanleydgromjr]

DC,

Here you go. The new macro, "CopyFromAllSheetsButTarget", will copy ALL data in Column A, from all worksheets (expect Target), into the next available row in Target, by pressing "CTRL" + "t".

I will have for you later today, another macro "RemoveFromSheetAndTarget", that will remove the active cell row in the active sheet, and the corresponding entry in Target.

Are you also looking for a macro that, when you add a new entry in a sheet, to have just that entry copied to the next available row in Target?

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub CopyFromAllSheetsButTarget()

    Dim wSheet As Worksheet
    Dim wsTarget As Worksheet
    Dim lngwSheetLastRow As Long
    Dim lngTargetLastRow As Long

    Application.ScreenUpdating = False

    Set wsTarget = Worksheets("Target")

    For Each wSheet In Worksheets
        If UCase(wSheet.Name) = "TARGET" Then
            'Do nothing
        Else
            lngwSheetLastRow = wSheet.Range("A" & Rows.Count).End(xlUp).Row
            lngTargetLastRow = Worksheets("Target").Range("A" & Rows.Count).End(xlUp).Row + 1
            With wSheet
                .Range("A1:A" & lngwSheetLastRow).Copy wsTarget.Cells(lngTargetLastRow, 1)
            End With
        End If
    Next wSheet

    wsTarget.Select

    Application.ScreenUpdating = True

End Sub

To assign the two key combination 'CTRL + t' to the macro:
Click on:
Tool, Macros, Macros...

Change the 'Macros In:' box to 'This Workbook'

Click on the 'CopyToTarget' macro, and click on the 'Options...' button.

In the 'Shortcut key:' box type in a 't' without the apostrophes.

Click on OK, and click on the 'X' in the upper right hand corner of the 'Macro' box.


Have a great day,
Stan