how change code from range to TABLE and from sheet to multiple

[Maklil]

Hi guys

is there any way to change this code please?

what I want implementing code for table and loop for multiple sheets.

every sheet contains table and contains three columns A,B,C . the item is in column B and numeric values in column C . so should merge duplicates items across sheets into one sheet " OUTPUT".

Sub MG02Sep59()
Dim Rng As Range, Dn As Range, n As Long, nRng As Range, ws As Worksheet
Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

For Each Dn In Rng
    If Not .exists(Dn.Value) Then
        .Add Dn.Value, Dn
    Else
        If nRng Is Nothing Then Set nRng = _
        Dn Else Set nRng = Union(nRng, Dn)
        .Item(Dn.Value).Offset(, 1) = .Item(Dn.Value).Offset(, 1) + Dn.Offset(, 1)
    End If
Next

thanks

 

[DanteAmor]

Thanks for posting on the forum.

Try this:

Sub multiple_sheets()
  Dim dic As Object
  Dim sh As Worksheet
  Dim c As Range
  
  Set dic = CreateObject("Scripting.Dictionary")
  For Each sh In Sheets
    Select Case LCase(sh.Name)
      Case "output", "sheet1", "sheet2", "sheet3", "etc"    ''excluded sheet in lowercase
        
      Case Else
        For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(xlUp))
          dic(c.Value) = dic(c.Value) + c.Offset(, 1).Value
        Next
    End Select
  Next
  
  Sheets("output").Range("B2").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Maklil]

thanks
almost very good , but I need fixing somethings
1- when merge in output sheet doesn't autonumber in column A like 1,2,3....
2- there is problem when run the macro more than one time will summing increasingly . it should replace data to avoid this problem .
3- this point I forgot mentioned , sorry
there is text with value for all of sheets in column C like this
45 QTY
66 QTY
so I want summing values with text "QTY" in OUTPUT sheet .

 

[DanteAmor]

1- when merge in output sheet doesn't autonumber in column A like 1,2,3....
2- there is problem when run the macro more than one time will summing increasingly . it should replace data to avoid this problem .
3- this point I forgot mentioned , sorry
there is text with value for all of sheets in column C like this
45 QTY
66 QTY
so I want summing values with text "QTY" in OUTPUT sheet .

None of those points you did not mention in your original post.
In fact you didn't mention how you want the output, nor do the lines to output exist in your macro.

So for me to understand how you want the output.
You must give examples:
1. Minimum of 2 sheets to consolidate.
2. How is your sheet "output" before the macro.
3. How do you want the result in the "output" sheet after the macro.

To put the examples of your sheet use the XL2BB tool
Note XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

For example:

Dante Amor
	A	B	C
1	DATA	ITEM	VALUE
2		it1	95
3		it1	42
4		it1	68
5		it2	38
6		it2	83
sh2

Dante Amor
	A	B	C
1	DATA	ITEM	VALUE
2		it1	88
3		it1	29
4		it1	53
5		it2	75
6		it3	67
sh3

Dante Amor
	A	B	C
1
2		it1	375
3		it2	196
4		it3	67
Output

Remember those examples are mine, you must put your examples of how the data really is in your sheets and very important, how you want the result.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Maklil]

here are two sheets

as
	A	B	C
1	ITEM	BRAND	PACAGE
2	1	MK-GH220	200 QTY
3	2	MK-GH221	100 QTY
4	3	MK-GH222	50 QTY
5	4	MK-GH223	120 QTY
6	5	MK-GH224	100 QTY
7	6	MK-GH225	5 QTY
8	7	MK-GH226	12 QTY
9	8	MK-GH227	12 QTY
10	9	MK-GH228	11 QTY
11
12
13
14
SH

as
	A	B	C
1	ITEM	BRAND	PACAGE
2	1	MK-GH220	200 QTY
3	2	MK-GH225	5 QTY
4	3	MK-GH226	12 QTY
5	4	MK-GH227	12 QTY
6	5	MK-GH228	10 QTY
7	6	MK-GH221	100 QTY
8	7	MK-GH222	50 QTY
9	8	MK-GH22	200 QTY
10
11
12
13
14
15
16
MTRS

as
	A	B	C
1	ITEM	BRAND	PACAGE
2
3
4
5
6
7
8
9
10
11
12
13
14
15
OUTPUT

result

as
	A	B	C
1	ITEM	BRAND	PACAGE
2	1	MK-GH22	200 QTY
3	2	MK-GH220	400 QTY
4	3	MK-GH221	200 QTY
5	4	MK-GH222	100 QTY
6	5	MK-GH223	120 QTY
7	6	MK-GH224	100 QTY
8	7	MK-GH225	10 QTY
9	8	MK-GH226	24 QTY
10	9	MK-GH227	24 QTY
11	10	MK-GH228	21 QTY
12
13
14
15
OUTPUT

I hope to understand point2

 

[DanteAmor]

Hi @Maklil.

At this point, you hadn't asked for it in your original post and it's impossible for anyone to guess.

1- when merge in output sheet doesn't autonumber in column A like 1,2,3....

So if you don't mention a point in your original post, don't expect it to be resolved.
As a plus, the macro already does it.


Next point:

2- there is problem when run the macro more than one time will summing increasingly .

It is important that you follow the instructions for the macro to work correctly.
So that additional data is not added, you must put in this line of the macro, the sheets that should not be added, including the "OUTPUT" sheet.

Case "output", "sheet1", "sheet2", "sheet3", "etc"    ''excluded sheet in lowercase

So, in that line of the macro you must write the sheets that you do not want to add but in lower case.
I already put the "output" sheet and other examples, but you must write the real names of the sheets that you do not want to add.
In your example you put that the sheet is called "output" if so then I already put it in the line, if you are not going to exclude more sheets, then do not modify anything in the macro.
Try the following code:

Sub multiple_sheets()
  Dim dic As Object
  Dim sh As Worksheet
  Dim c As Range
 
  Set dic = CreateObject("Scripting.Dictionary")
  For Each sh In Sheets
    Select Case LCase(sh.Name)
      Case "output", "sheet1", "sheet2", "sheet3", "etc"    ''excluded sheet in lowercase
       
      Case Else
        For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(xlUp))
          dic(c.Value) = dic(c.Value) + Val(Trim(Replace(c.Offset(, 1).Value, " QTY", "")))
        Next
    End Select
  Next
 
  With Sheets("OUTPUT")
    .Range("A1:C1").Value = Array("ITEM", "BRAND", "PACAGE")
    .Range("A2:C" & Rows.Count).ClearContents
    .Range("B2").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
    .Range("A2").Value = 1
    Range("A2:A" & dic.Count + 1).DataSeries xlColumns, xlLinear, xlDay, 1
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Maklil]

At this point, you hadn't asked for it in your original post and it's impossible for anyone to guess.

sorry I thought the original code does it, but doesn't seem doing that !

It is important that you follow the instructions for the macro to work correctly.
So that additional data is not added, you must put in this line of the macro, the sheets that should not be added, including the "OUTPUT" sheet.

not really sure why you say ''excluded sheet"
what I understand for this line

      [B]Case "output", "sheet1", "sheet2", "sheet3", "etc"    ''excluded sheet in lowercase[/B]

I have to put sheets names should implement code for them to merge data !
even that I still see the problems as in point 2 after run more than one time you can calculate and see the result in output it will summ result increasingly every time .
also will change TABLE formatting and show empty row contains zero !
attached file
sample (1).xlsm

 

[DanteAmor]

sorry I thought the original code does it, but doesn't seem doing that !

Check your original post, your macro is incomplete, as I mentioned before it doesn't have a result.


-----------------------

... in output it will summ result increasingly every time ....

Obviously you have to tell the macro not to consider the "output" sheet so that it doesn't duplicate the values.


----------------------------
Try the following code.
Do not modify anything in the macro. The macro works with the file you shared.

Sub multiple_sheets()
  Dim dic As Object
  Dim sh As Worksheet
  Dim c As Range
 
  Set dic = CreateObject("Scripting.Dictionary")
  For Each sh In Sheets
    Select Case LCase(sh.Name)
      Case LCase("output")  ''excluded sheet in lowercase
      
      Case Else
        For Each c In sh.Range("B2", sh.Range("B" & Rows.Count).End(xlUp))
          If c.Value <> "" Then
            dic(c.Value) = dic(c.Value) + Val(Trim(Replace(c.Offset(, 1).Value, " QTY", "")))
          End If
        Next
    End Select
  Next
 
  With Sheets("OUTPUT")
    .Range("A1:C1").Value = Array("ITEM", "BRAND", "PACAGE")
    .Range("A2:C" & Rows.Count).ClearContents
    .Range("B2").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
    .Range("A2").Value = 1
    .Range("A2:A" & dic.Count + 1).DataSeries xlColumns, xlLinear, xlDay, 1
  End With
End Sub

I put your example file with the macro working correctly.

sample dam.xlsm

drive.google.com

 

[Maklil]

perfect !

Do not modify anything in the macro

in reality I don't as in previous , but I told you in post#7

also will change TABLE formatting

did you note it ?
seem when autonumbering in column A will copy cell formatting . could auto-number without copy formatting ?
should keep the original formatting as in original TABLE formatting when autonumbering .