Combining data from multiple worksheet to a seperate worksheet

[shri_h]

Hi All,

I have a workbook called "Form_2.1_Passage_Plan". This WB has multiple worksheet, the data which I need is in worksheets named "Berth to Pilot WP Input", "Pilot to Pilot WP Input" and "Pilot to Berth WP Input". The Worksheet where the data needs to be compiled is named "WaypointSummary". There are other worksheets in the Workbook called "Index", "UKC Proforma" and "Strait Transit" which should be skipped.

1. From WS "Berth to Pilot WP Input" - All filled Rows starting from "E6:J6" to be copied to WS "WaypointSummary" Rows starting from "B12:G12"
2. From WS "Berth to Pilot WP Input" - All filled Rows starting from "N6" to be copied to WS "WaypointSummary" Rows starting from "H12"
3. From WS "Pilot to Pilot WP Input" - All filled Rows starting from "E6:J6" to be copied to WS "WaypointSummary" in column "B:G" starting from the next row after item 2.
4. From WS "Pilot to Pilot WP Input" - All filled Rows starting from "N6" to be copied to WS "WaypointSummary" in column "H" starting from the next row after item 2.

The same data to be copied from the 3rd WS.
I need only the data to be copied without the format.

As this workbook will be used multiple times, the data in WS "WaypointSummary" in array "A12:J1000" to be cleared every time when the VBA is activated with a dialog box that data in WaypointSummary sheet will be cleared.

Going ahead, I want only the "WaypointSummary" WS to be saved as a separate Workbook, can this be written in the same VBA as well?

I am new to this forum, any assistance to get me started most welcome. I am not sure if I have been able to explain myself enough. If any further information is required, please do let me know.

[spitfireblue]

Here you go...

Please Login or Register  to view this content.

Let me know how you go,
Stephen

[shri_h]

Thanks a lot Spitfireblue,

the data in range "E6:J6" copies perfectly from both the sheets. However only the first 2 rows from "N6" is copied into the "WaypointSummary" sheet.

It creates a new workbook, but It is giving runtime "1004" error. "Microsoft Excel cannot access the file "C\Users\stream\Desktop\C8175100, I tried changing the filename to a known folder, but it still gives similar error.

If I need to add additional sheets to copy from, do I need to make any changes?

Once again thanks for your time and feedback.

[shri_h]

I just realized, the first cell in column "N6" was blank, once I entered data into the cell, it works fine.
However I will have situations where the cells may be blank, is there a workaround this?

Thanks once again.

[spitfireblue]

It creates a new workbook, but It is giving runtime "1004" error. "Microsoft Excel cannot access the file "C\Users\stream\Desktop\C8175100, I tried changing the filename to a known folder, but it still gives similar error.

You definitely need to change this to a known folder... I am not sure why it is still giving an error. Maybe remove the & Format(Date, "dd-mm-yyyy") and just put in the folder location and filename that you want.

[shri_h]

I just realized, the first cell in column "N6" was blank, once I entered data into the cell, it works fine.
However I will have situations where the cells may be blank, is there a workaround this?

Thanks once again.

For cells which are blank in the source worksheet, is there a way to enter "0" or "NA".

[spitfireblue]

If I need to add additional sheets to copy from, do I need to make any changes?

Yes. As per the code already written, you need to select the sheet:

Please Login or Register  to view this content.

Then you need to paste the values

Please Login or Register  to view this content.

Here is a breakdown:
The sheet where you are pasting the data: Sheets("WaypointSummary")
The cells where you are pasting the data: .Range("B12").End(xlDown).Offset(1, 0).Range("A1")
(Note: I have started at B12, gone to the bottom, and then down another cell to paste below the first lot of data)
The actual pasting of the the data:.PasteSpecial xlPasteValues

[spitfireblue]

I just realized, the first cell in column "N6" was blank

On which sheet? Are any cells in column E in either sheet that you are copying from blank?

[spitfireblue]

Maybe PM me a copy of the workbook or post a cut down version (i.e. remove any sensitive data)

[spitfireblue]

Final Code (in case someone is looking for something similar):

Please Login or Register  to view this content.