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.
> > >
|
Similar Threads
