PDA

View Full Version : Extracting data from different worksheets in desired format-Part 2 (Editing formula)



paradise
04-20-2016, 01:07 AM
Hi all of you,

This thread I have already posted into another forum at http://www.excelforum.com/excel-programming-vba-macros/1135305-extracting-data-from-different-worksheets-in-desired-format-part-2-editing-formula.html
Kindly click on the link to see the detail descriptions.If anyone require further information,I would be glad to tell so that the issue can be solved.


With Rgds,
Suresh

Aussiebear
04-20-2016, 02:01 AM
You are surely joking Suresh? Please post the topic here in full. This forum is not a jumping off point for any other forum.

paradise
04-20-2016, 04:10 AM
You are surely joking Suresh? Please post the topic here in full. This forum is not a jumping off point for any other forum.

Thanx for the reply.Due to cross post,I was trying to avoid infraction which I used to receive in other forum.

Let me explain in detail again.

I have a workbook (as enclosed in attachment) in which I have the different worksheets which goes on increasing as the sheets are in date form separated by (.) in dd-mm-yyyy.From all those sheets I want to extract certain data from each worksheet from column A:O in a specified format which I have mentioned in worksheet "Expected Result" whose code I received from the post as mentioned in above link.The data is extracted very fine.The code is

Sub Test() Dim wsTarget As Worksheet, wsTemp As Worksheet, rng As Range


Application.ScreenUpdating = False
Set wsTarget = Sheets("Expected Result")
wsTarget.Rows("3:" & Application.Max(wsTarget.Cells.SpecialCells(xlCellTypeLastCell).Row, 3)).Clear


For Each wsTemp In Worksheets
With wsTemp
If .Name <> wsTarget.Name Then
.AutoFilterMode = False
Set rng = .Range("A3:R" & .Cells(.Rows.Count, "A").End(xlUp).Row)
rng.Columns("P").FormulaR1C1 = "=IF(R[-2]C1=""Type"",1,IF(OR(R[-2]C1=""Breakdown Details"",RC1=""""),"""",R[-1]C))"
rng.Columns("Q").Value = .Name
rng.Columns("R").FormulaR1C1 = "=IF(RC16=1,IF(R[-2]C1=""Type"",R[-2]C3,R[-1]C),"""")"
rng.AutoFilter field:=16, Criteria1:=1


Intersect(rng.SpecialCells(xlCellTypeVisible), .Columns("A:O")).Copy wsTarget.Cells(wsTarget.Rows.Count, "C").End(xlUp).Offset(1)
Intersect(rng.SpecialCells(xlCellTypeVisible), .Columns("Q:R")).Copy wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Offset(1)


.AutoFilterMode = False
.Columns("P:S").Clear
End If
End With
Next wsTemp


wsTarget.Range("A2").CurrentRegion.Borders.Weight = xlThin
Application.ScreenUpdating = True
End Sub




Here, now I want to extract the data from each sheet from P:AF in specified format as mentioned in worksheet "Expected Result2" whose headings are from A2:S2.

I tried to modify the above code but could not get success to achieve the target result
Pls note that the sheet goes on increasing.

In summary to say that I want to extract data from each worksheet from A:O in one worksheet "Expected Result" in specified format and from P:AF in another worksheet "Expected Result2" in specified format.

I think I am able make it understand.If any info is required,then kindly let me know.

With Best Rgds,

Aussiebear
04-21-2016, 01:18 AM
Thanx for the reply.Due to cross post,I was trying to avoid infraction which I used to receive in other forum.

The "infraction" comes about not because you post in other forums, but because you failed to notify the forum that you had posted the issue elsewhere. Here you notified us of the cross posted thread and expected us to chase it down. This is the inverse of your issue at the other forum.

paradise
04-21-2016, 08:51 PM
I hope my this issue will be resolved.