Hi,

Currently, Iam doing a very tedious and time consuming job. Please help me
out on this.

I have a workbook having several worksheets like 50+ having the same layout
i.e., Heading1, Heading2, Heading3 .... and may add more with the same
headers in each sheet. The Headings starts from say B8:N8 and have some
headings having a drop down list to be picked up in each row beneath that
till row no. 200.

Each sheet is named differently. My requirement is that I need to collate
these information by copying and pasting in another sheet named "Total Data"
of all these 50+ sheets information one below the other (i.e., from sheet1:
Copy/Paste B9:N200 to Sheet "Total Data" and from sheet2: Copy/Paste B9:N200
to Sheet "Total Data" just below the data copied from sheet1....... and
likewise for all sheets in "Total Data". Secondly, I also want the name of
each sheet to be captured in Column A in the sheet "Total Data" accordingly.

Please note that the sheet "Total Data" has also got the same layout as that
of the other sheets.

Can you please help me out in creating a Macro to do this task.

Your timely help will be greatly appreciated.
Prashanth KR.

Sub copyData()
Dim sh as Worksheet, sh1 as Worksheet
Dim r as Range, r1 as Range
set sh1= Worksheets("Total Data")
for each sh in worksheets
if lcase(sh.name) < "total data" then
set r = sh.Range("A1").CurrentRegion
set r = r.offset(1,0).Resize(r.rows.count-1,1)
set r = Intersect(r.EntireRow,sh.Range("B:N"))
set r1 = sh1.Cells(rows.count,1).End(xlup)(2)
r.copy r1.offset(0,1)
r1.Resize(r.rows.count,1).Value = sh.Name
end if
Next
end sub

--
Regards,
Tom Ogilvy


"Prashanth KR" wrote:

Hi,

Currently, Iam doing a very tedious and time consuming job. Please help me
out on this.

I have a workbook having several worksheets like 50+ having the same layout
i.e., Heading1, Heading2, Heading3 .... and may add more with the same
headers in each sheet. The Headings starts from say B8:N8 and have some
headings having a drop down list to be picked up in each row beneath that
till row no. 200.

Each sheet is named differently. My requirement is that I need to collate
these information by copying and pasting in another sheet named "Total Data"
of all these 50+ sheets information one below the other (i.e., from sheet1:
Copy/Paste B9:N200 to Sheet "Total Data" and from sheet2: Copy/Paste B9:N200
to Sheet "Total Data" just below the data copied from sheet1....... and
likewise for all sheets in "Total Data". Secondly, I also want the name of
each sheet to be captured in Column A in the sheet "Total Data" accordingly.

Please note that the sheet "Total Data" has also got the same layout as that
of the other sheets.

Can you please help me out in creating a Macro to do this task.

Your timely help will be greatly appreciated.
Prashanth KR.

Wow..... it worked like a miracle for me. Thanks a lot Tom.......

Similarly, this brings me to an other question, if you dont mind......

What in case if I have different Workbooks named differently as the name say
sheet1, sheet2 ..... having only one sheet containing the same data and
layout. ie., say 50+ workbook and I want to collate the same in another
workbook named "Total Data".

I would greatly appreciate for your time and effort.
Prashanth KR.


"Tom Ogilvy" wrote:

Sub copyData()
Dim sh as Worksheet, sh1 as Worksheet
Dim r as Range, r1 as Range
set sh1= Worksheets("Total Data")
for each sh in worksheets
if lcase(sh.name) < "total data" then
set r = sh.Range("A1").CurrentRegion
set r = r.offset(1,0).Resize(r.rows.count-1,1)
set r = Intersect(r.EntireRow,sh.Range("B:N"))
set r1 = sh1.Cells(rows.count,1).End(xlup)(2)
r.copy r1.offset(0,1)
r1.Resize(r.rows.count,1).Value = sh.Name
end if
Next
end sub

--
Regards,
Tom Ogilvy


"Prashanth KR" wrote:

Hi,

Currently, Iam doing a very tedious and time consuming job. Please help me
out on this.

I have a workbook having several worksheets like 50+ having the same layout
i.e., Heading1, Heading2, Heading3 .... and may add more with the same
headers in each sheet. The Headings starts from say B8:N8 and have some
headings having a drop down list to be picked up in each row beneath that
till row no. 200.

Each sheet is named differently. My requirement is that I need to collate
these information by copying and pasting in another sheet named "Total Data"
of all these 50+ sheets information one below the other (i.e., from sheet1:
Copy/Paste B9:N200 to Sheet "Total Data" and from sheet2: Copy/Paste B9:N200
to Sheet "Total Data" just below the data copied from sheet1....... and
likewise for all sheets in "Total Data". Secondly, I also want the name of
each sheet to be captured in Column A in the sheet "Total Data" accordingly.

Please note that the sheet "Total Data" has also got the same layout as that
of the other sheets.

Can you please help me out in creating a Macro to do this task.

Your timely help will be greatly appreciated.
Prashanth KR.

Hi Tom,

Can you please look into this. I shall be very appreciative.

Thanks in advance,
Prashanth KR



"Prashanth KR" wrote:


Wow..... it worked like a miracle for me. Thanks a lot Tom.......

Similarly, this brings me to an other question, if you dont mind......

What in case if I have different Workbooks named differently as the name say
sheet1, sheet2 ..... having only one sheet containing the same data and
layout. ie., say 50+ workbook and I want to collate the same in another
workbook named "Total Data". Please note that all these workbooks are in one folder.

I would greatly appreciate for your time and effort.
Prashanth KR.


"Tom Ogilvy" wrote:

Sub copyData()
Dim sh as Worksheet, sh1 as Worksheet
Dim r as Range, r1 as Range
set sh1= Worksheets("Total Data")
for each sh in worksheets
if lcase(sh.name) < "total data" then
set r = sh.Range("A1").CurrentRegion
set r = r.offset(1,0).Resize(r.rows.count-1,1)
set r = Intersect(r.EntireRow,sh.Range("B:N"))
set r1 = sh1.Cells(rows.count,1).End(xlup)(2)
r.copy r1.offset(0,1)
r1.Resize(r.rows.count,1).Value = sh.Name
end if
Next
end sub

--
Regards,
Tom Ogilvy


"Prashanth KR" wrote:

Hi,

Currently, Iam doing a very tedious and time consuming job. Please help me
out on this.

I have a workbook having several worksheets like 50+ having the same layout
i.e., Heading1, Heading2, Heading3 .... and may add more with the same
headers in each sheet. The Headings starts from say B8:N8 and have some
headings having a drop down list to be picked up in each row beneath that
till row no. 200.

Each sheet is named differently. My requirement is that I need to collate
these information by copying and pasting in another sheet named "Total Data"
of all these 50+ sheets information one below the other (i.e., from sheet1:
Copy/Paste B9:N200 to Sheet "Total Data" and from sheet2: Copy/Paste B9:N200
to Sheet "Total Data" just below the data copied from sheet1....... and
likewise for all sheets in "Total Data". Secondly, I also want the name of
each sheet to be captured in Column A in the sheet "Total Data" accordingly.

Please note that the sheet "Total Data" has also got the same layout as that
of the other sheets.

Can you please help me out in creating a Macro to do this task.

Your timely help will be greatly appreciated.
Prashanth KR.

I am posting my question here because I've tried to write in a new post but
it doesn't show up. Anyway, I need to copy cells from one worksheet to
another [same workbook], based on two conditions. The conditions are simple,
e.g. ISBLANK($A30) and $C30="OPEN", etc. The original column has 200 entries.
Only 50 of them may be copied in consecutive cells in a column on the new
worksheet.

Thanks for any help.
Atul


"Prashanth KR" wrote:

Hi,

Currently, Iam doing a very tedious and time consuming job. Please help me
out on this.

I have a workbook having several worksheets like 50+ having the same layout
i.e., Heading1, Heading2, Heading3 .... and may add more with the same
headers in each sheet. The Headings starts from say B8:N8 and have some
headings having a drop down list to be picked up in each row beneath that
till row no. 200.

Each sheet is named differently. My requirement is that I need to collate
these information by copying and pasting in another sheet named "Total Data"
of all these 50+ sheets information one below the other (i.e., from sheet1:
Copy/Paste B9:N200 to Sheet "Total Data" and from sheet2: Copy/Paste B9:N200
to Sheet "Total Data" just below the data copied from sheet1....... and
likewise for all sheets in "Total Data". Secondly, I also want the name of
each sheet to be captured in Column A in the sheet "Total Data" accordingly.

Please note that the sheet "Total Data" has also got the same layout as that
of the other sheets.

Can you please help me out in creating a Macro to do this task.

Your timely help will be greatly appreciated.
Prashanth KR.