Copy specific area with filled cells (range) to another worksheet within the same workbook

Dear Excel specialists,

I have a short question, but first allow me to provide some additional context.
- I have a workbook, with several worksheets.
- People are required to fill in a fixed table with data. There is only one exception regarding the fixed table, people are allowed to add more rows in the table.

What I like to achieve is (pseudo code)

- Look for data in all worksheets (fixed position: A8 - i8 horizontally, vertical is flexible), except the data in the Summary worksheet
- Preferably --> Copy all cells within that specific range (A8-i8) and stop when the first cell found that has no value (empty). My script looks for the text : "END"
- Copy all data within that specific area to the Summary sheet
- Put all collected data "under each other"


The problem with the current script is: '
- It copies total rows instead of an area, so it "wipes" all rows clean in the Summary worksheet. So it also cleans my "conditional formatting" planning table (that is/was positioned in J8- Z8 / and further down).

To summarize it all:
- I would like to copy an area instead of rows (because of cleaning the rest of my summary sheet).

Is this easy to achieve in a simple (easy to understand script/small script)?


Original Script:
For Each ws In Worksheets
If ws.Name <> Planning_Summary_Name Then
lr = ws.Cells.Find("*End*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
ws.Range("A8:I" & lr).Copy Sheets(Planning_Summary_Name).Range("D" & Rows.Count).End(xlUp).Offset(2, -3)
End If
Next ws

Thank you (in advance) very much for you support!

Please check the attachment I provided.

HI Sourabhg98 and Marc L,

Both work like a charm. Thank you very much for you quick support/help.
Highly appreciated!

Best regards,

Hans

PS.
Reputation added and closing this thread. :-)

Originally Posted by sourabhg98

It is not copying complete rows, it is copying the specific area only. What is wiping your data is the clear statement -

Please Login or Register  to view this content.

Try this -

Please Login or Register  to view this content.

You can remove "END" from the all the data sheets now. Also, this will not copy headers, just the data without headers. So, you can manually put in the headers in the summary sheet once in row 1.

--------------------------------------

Hi Sourabhg98,

I have just one additional question of a problem that I just ran into.
Is it possible to assign the first row of the past action?
In the summary sheet I like to get the "Paste" action starting at Row number five. Changing the offset did'nt work, because this will effect the paste result because of the loop.

Attachment 768895

Thank you (in advance for your answer)

Hi Sourabhg98,

I have just one additional question of a problem that I just ran into.
Is it possible to assign the first row of the past action?
In the summary sheet I like to get the "Paste" action starting at Row number five (please see attachment).
Changing the offset did'nt work, because this will effect the paste result because of the loop.

Attachment 768895

Thank you (in advance for your answer)

Attachment 768897Attachment 768897

That's the problem for me.
This image (with items) is just a part of a large sheet with information.
This is the reason why a row number / position for the past action is important for me

YES!!!! This did the trick! Thank you very much for your support!!!
Now I can continue with my work. Again thanks!

Added again in your reputation.