Copy range from one sheet to another

[colins5286]

Morning,

I have a workbook which contains the following sheets.

Cycoms_Source
Cyc_Summary

Source contains a list of names in column A, there is no set number of names it can range from 1 to 000's. For each name in this list there is a worksheet with a corresponding name. All these worksheets are identical. It's just the data that is different.

The data on these named worksheets is contained in row 8 through 22, columns A - H. Not all rows will have data, some worksheets may have 1 or 2 rows of data others may have 10 rows.

I would like a macro that copies the relevant data contained on these worksheets and pastes it to the Cyc_Summary sheet. It would somehow have to know which worksheets to take information from, based on the Source list then workout which rows contain data from each worksheet before adding it to the summary sheet.

I don't have any idea where to start on this one.

Thanks

[patel45]

can you attach a sample file with data and desired result and notes ?

[HaHoBe]

Hi, patel45,

maybe try again and read the opening post - everything you ask for is written down in the text so maybe you should work from that with your solution.


@colins5286:

What I canīt understand is

For each name in this list there is a worksheet with a corresponding name

In the very same workbook?

The Range starts with row 2 on the Source sheet, you would need to adpat that to your needs:

Please Login or Register  to view this content.

Ciao,
Holger

[colins5286]

Hi,

Thanks for your quick response. I have tried this code, but it doesn't seem to copy any data from the worksheets.
In column A (starting with A5) there is a list of numbers (e.g. 15879, 54786, 33178), there are then individual worksheets within the workbook called 15879, 54786, 33178. These worksheets are all identical in layout, just with different data.
I use this list as a table of contents essentially, each name in the list is a hyperlink to the actual worksheet.

The amended code I used was:

Please Login or Register  to view this content.

[colins5286]

Cleared_Spread.xlsm

Here is the attached spreadsheet

[HaHoBe]

Hi, colins5286,

each name in the list is a hyperlink to the actual worksheet.

Maybe you could show how the hyperlinks are created - if I add a hyperlink via formula with the fitting friendly name the code works fine.

but it doesn't seem to copy any data from the worksheets.

Have you checked the target sheet for any result?

Ciao,
Holger

[colins5286]

Hi,

The code I use to create the hyperlinks is:

Please Login or Register  to view this content.

I have checked the target sheet and there is nothing added to it. The macro runs, I get no errors, but it doesn't seem to past any data.

[HaHoBe]

Hi, colins5286,

with your sample sheet the code should be altered from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and try again.

Ciao,
Holger

[colins5286]

Hi,

Thanks for that - it now copies the data from the worksheets, which is great. It is leaving several blank rows between each section of data though. Is there anyway of amending this?

Thanks


SORRY!! - Ignore this - my mistake I had set the offset to 4 instead of 1. works great now. Thanks so much for your help.

[HaHoBe]

Hi, colins5286,

if your problem is solved by now do you mind marking this thread as Solved via Thread Tools over the first post?

Ciao,
Holger