Get Sepcified Detail From Worksheets

[ayazgreat]

Hi All

I want to copy some specified detail form worksheets to sheet named "Report" for example in each sheet in column a in product name and in column b is City name and in column c quntity If I want all records of Oxfort in column b to be copied from all sheets to report sheet but there is some confusion that I want first it copies sheet name in row a2 in report sheet then and in column c it copies its quantity to in row2 in coloum b in report sheet and if product name repeat in any sheet it sums qty. Kindly see my attached example sheet.

[Bob Phillips]

How much of the info in Report is pre-loaded? I assume Oxford is there, but are the JB, JA etc., labels pre-loaded, or does the code need to get that?

[ayazgreat]

Thanks Sir for your reply, only City Name Oxford is preloaded in report sheet in column a and its header and remaining information i want to be copied through macro as you have seen in my attached with my given comments to some cells for your info. I asked for codes only for one city like "Oxford" , so I want to know that can it be possible that if wish to export other Cities info like "Wellingbrough and London" which starts from coloums c to d and e to f with same headers.

[ayazgreat]

Sir

I wish to say that only my said cities info to be copied not other cities info if there will be.

[ayazgreat]

Could anyone help?

[ayazgreat]

could anyone please solve it?

[ayazgreat]

Could anyone help please

[Aussiebear]

Hi ayazgreat, You need to show some patience here, I'm afraid. We understand that you are seeking this answer as soon as possible, but others on this forum are dealing with issues in thier own lives right now. This is not to say that we don't think your issue isn't important.

Be patient, and someone will happen along to assist you.

[ayazgreat]

Thanks Sir for your reply and advice
Ok Sir I will wait

[ayazgreat]

Still waiting for reply

[Aussiebear]

ayazgreat, thank you for your patience. Can you repost a workbook with examples of the outcomes you are seeking. I have read your request in posts 1 & 3, but am having some troubles in following your request.

[ayazgreat]

I want to copy some specified detail form worksheets to sheet named "Report" for example in each sheet in column a in product name and in column b is City name and in column c quntity If I want all records of Oxfort in column b to be copied from all sheets to report sheet but there is some confusion that I want first it copies sheet name in row a2 in report sheet then and in column c it copies its quantity to in row2 in coloum b in report sheet and if product name repeat in any sheet it sums qty. Kindly see my attached example sheet.

only City Name Oxford is preloaded in report sheet in column a and its header and remaining information i want to be copied through macro as you have seen in my attached with my given comments to some cells for your info. I asked for codes only for one city like "Oxford" , so I want to know that can it be possible that if wish to export other Cities info like "Wellingbrough and London" which starts from coloums c to d and e to f with same headers.

[pvanrooijen]

Just a suggestion:
Why not use one sheet with data and possibilties of Pivot table?

Regards Atmaram

[ayazgreat]

Please I need Vb code to do it if possible

[mdmackillop]

As I understand this

Option Explicit
Sub test()
Dim sh As Worksheet, Tgt As Worksheet, i As Long, j As Long
Dim d, a
Dim c As Range, Rng As Range, cel As Range

Set Tgt = Sheets("Report")
Set d = CreateObject("Scripting.Dictionary")
j = 0
On Error Resume Next
For i = 2 To Sheets.Count
Set sh = Sheets(i)
Set Rng = Intersect(sh.Columns(2), sh.UsedRange)
For Each cel In Rng
If cel = Tgt.Range("A2") Then
d.Add cel.Offset(, -1).Text, cel.Offset(, -1).Text
End If
Next

Set c = Tgt.Cells(Rows.Count, 1).End(xlUp).Offset(j + 1)
c = sh.Name
a = d.items
c.Offset(1).Resize(d.Count) = Application.Transpose(a)
c.Offset(1, 1).Formula = "=SUMPRODUCT(--(" & sh.Name & "!" & Rng.Address _
& "=Report!$A$2),--(" & sh.Name & "!" & Rng.Offset(, -1).Address & "=Report!$A" & _
c.Offset(1, 1).Row & "),(" & sh.Name & "!" & Rng.Offset(, 1).Address & "))"
c.Offset(1, 1).Resize(d.Count).FillDown

j = 1
Set d = CreateObject("Scripting.Dictionary")
Next
End Sub

[ayazgreat]

Thank you very much Sir for your reply it realy works but onr thing I want to say if I type London in a2 in report sheet instead of oxford the given codes only copies sheet name but not other detail i mean to say that qty result is blank can it be possible if I type any other city name, existing in worksheets , given codes get result and ignore/ not look for a sheet named "Total" but go through other worksheets.

Thanks in advance

[mdmackillop]

Add this If statement around this line

If d.Count > 1 Then
c.Offset(1, 1).Resize(d.Count).FillDown
End If

[ayazgreat]

Sir

I need your more help regarding for this post
If i say any city has 0 total 0 value in any sheet so that sheet details not be copied to report sheet kindly see my attached sheet as city oxford has got total 0 value in two sheets meaning to say that no any transaction in two sheets kindly also see sheets name format in report sheet when copied it would like that.

Thanks in advance

[Bob Phillips]

Is this what you mean



Sub test()
Dim sh As Worksheet, Tgt As Worksheet, i As Long, j As Long
Dim d, a
Dim c As Range, Rng As Range, cel As Range

Set Tgt = Sheets("Report")
Set d = CreateObject("Scripting.Dictionary")
j = 0
On Error Resume Next
For i = 2 To Sheets.Count
Set sh = Sheets(i)
Set Rng = Intersect(sh.Columns(2), sh.UsedRange)
For Each cel In Rng
If cel = Tgt.Range("A2") Then
If cel.Offset(0, 1).Value <> 0 Then
d.Add cel.Offset(, -1).Text, cel.Offset(, -1).Text
End If
End If
Next

Set c = Tgt.Cells(Rows.Count, 1).End(xlUp).Offset(j + 1)
c = sh.Name
a = d.items
c.Offset(1).Resize(d.Count) = Application.Transpose(a)
c.Offset(1, 1).Formula = "=SUMPRODUCT(--(" & sh.Name & "!" & Rng.Address _
& "=Report!$A$2),--(" & sh.Name & "!" & Rng.Offset(, -1).Address & "=Report!$A" & _
c.Offset(1, 1).Row & "),(" & sh.Name & "!" & Rng.Offset(, 1).Address & "))"
If d.Count > 1 Then
c.Offset(1, 1).Resize(d.Count).FillDown
End If

j = 1
Set d = CreateObject("Scripting.Dictionary")
Next
End Sub

[ayazgreat]

Yes Sir

Kindly see my new attached sheet I want to take detail of 3 cities in different columns of report.

[ayazgreat]

Could anyone help please ?

[Aussiebear]

ayazgreat, what is the purpose of the layout? Surley a pivot table would have been a better proposition as has been mentioned from an earlier post.

Have a good look at the code that has been provided by both Malcolm & Bob, for there is sufficient hints there as to what you could do. Give us an example of the coding that you have tried here.

[ayazgreat]

Thanks for your reply but how could it be done by Pivot table ?

I have attached a file as an example for your information

[ayazgreat]

Sir

I have made a change in given codes and tried to get my required detail but faild. I wanted to have those product detail which are not blank in qty column e.g " Set Rng = Intersect(sh.Columns(2), sh.UsedRange)" and I changed condition in red font but got worg result.

Could anybody please help me in this regard?



Sub test2()
Dim sh As Worksheet, Tgt As Worksheet, i As Long, j As Long
Dim d, a
Dim c As Range, Rng As Range, cel As Range

Set Tgt = Sheets("Report")
Set d = CreateObject("Scripting.Dictionary")
j = 0
On Error Resume Next
For i = 2 To Sheets.Count
Set sh = Sheets(i)
Set Rng = Intersect(sh.Columns(2), sh.UsedRange)
For Each cel In Rng
If cel <> "" Then
d.Add cel.Offset(, -1).Text, cel.Offset(, -1).Text
End If
Next

Set c = Tgt.Cells(Rows.Count, 1).End(xlUp).Offset(j + 1)
c = sh.Name
a = d.items
c.Offset(1).Resize(d.Count) = Application.Transpose(a)
c.Offset(1, 1).Formula = "=SUMPRODUCT(--(" & sh.Name & "!" & Rng.Address _
& "=Report!$A$2),--(" & sh.Name & "!" & Rng.Offset(, -1).Address & "=Report!$A" & _
c.Offset(1, 1).Row & "),(" & sh.Name & "!" & Rng.Offset(, 1).Address & "))"
If d.Count > 1 Then
c.Offset(1, 1).Resize(d.Count).FillDown
End If

j = 1
Set d = CreateObject("Scripting.Dictionary")
Next
End Sub


Thanks in advance

[ayazgreat]

Could anyone please help to correct these codes?