VB/Macro to replicate data ?!

Dhimit

Board Regular
Joined
Jul 24, 2007
Messages
195
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
 

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)
Joined
Jul 30, 2006
Messages
3,656
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.

Code:
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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
 
Joined
Jul 30, 2006
Messages
3,656
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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
 
Joined
Jul 30, 2006
Messages
3,656
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).


Code:
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
 

Forum statistics

Threads
1,181,053
Messages
5,927,848
Members
436,573
Latest member
CMR237

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