Merge Excel columns from different worksheets to new sheet

[InfoMan206]

Using Excel 2003, I have a workbook with 7 worksheets each containing 160 rows plus a header, each worksheet has a key field (employee number) and several unique columns. Looking for an easy to understand solution to merge the columns from all the worksheets into a new sheet - once data is merged, the key field is only required in column A.


When the data is first exported from our in-house custom app it has an initial 13 rows of information which is normally deleted manually (along with a number of columns which do not contain useful data) - if there is an easy way to add part of the script to remove the first 13 lines of each worksheet prior to the merge it would be a bonus (the header line is initially on row 14 prior to me deleting the other info).


I've found scripts which will merge rows but not columns - any assistance would be great.


Thank you.


Jason

 

[Mindpsyche]

Still not clear enough for me. So:

1. Your workbook has 7 sheets, each sheet with 160 rows of data.
2. The first 13 rows of this data needs to be deleted in all of the 7 sheets.
3. You need a new sheet inserted which contains only the unique columns from each of the other 7 sheets?

How can these columns be identified? Do they have unique headers or are they always fixed on the same column:

Eg: In sheet 1: You always keep Columns A, B and C.
In sheet 2: You always keep Columns D,E and F

Or is it random: The columns to keep on each sheet change. In this case how do you identify which column to keep for each sheet?

Anymore clarity from your side will help.

 

[InfoMan206]

Sorry for the delay Mindpsyche, the 14th row contains all the unique headers - and I always keep specific columns. Not sure if there is a way to post a sample excel file here - if needed I can host it on another server to provide it to you if needed.

The following is the manual script that I have people use to format the existing file to get to the point I need:

Insert new sheet at start of workbook

Medical Staff Tab:
· Delete Rows 1-13
· Delete Columns D,E,G,I,J,K
· Then move Columns A-Q to new sheet

Dental Staff Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-E to new sheet

Orderly Room Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then delete columns (confirm letters) C (Security Effective),
o K (TOS - Valid Terms Of Servi-Effective Date), L (TOS - Valid Terms Of Servi-Expiry Date),
o AI (PASS - Passport-Effective Date), AJ (PASS - Passport-Expiry Date)
· Then move Columns A-AJ to new sheet

Transport Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-I to new sheet

PSP Staff Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then delete column C (FIT - Fitness Test-Effective Date)
· Then move Columns A-D to new sheet

Security Section Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-I to new sheet

Post Deployment Period Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-E to new sheet

Training Officer Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-Q to new sheet

Once merged to main remove duplicate SN fields – after ensuring everything is lined up.
Select entire table, then Ctrl-H, then enter “-“ in the Find what entry, then enter “/” in the Replace with entry, then select Replace All (don’t worry if you see entries which look like ####### as this is just a width issue)

Select all columns containing dates, right click and select format, then date, then OK to select default date type

Ensure fields are in the correct order for the merge by confirming in the back-end database file

Ensure all users are out of the database

Backup the Master-be.mdb database file

Clear the contents of the DAG table

Import contents from Excel & verify results

 

[aditi_lic]

Open the file with this macro, it will open all sheets from the file. Then run the macro it will merge all sheets with first sheet heading and all row & column.
Macro: Excel Sheets merger Macro