Trouble with compiling 2 ranges into a summary

[RumJellybean]

I have looked all over the place but nothing seems to work for me.
In detail, I have a quote book i made with two sheets for different types of items on them. For example:

Sheet 1 is for wooden items, and sheet 2 is for metal items.
So after i enter all the items on sheet one and sheet two, I want to be able to combine all the items from both sheets into one summary sheet that i can send to the customer.

I tried consolidate, and messed around with pivot tabels but it didnt seem to be what i'm looking for.

 

[dave3009]

Hi RunJellyBean

I saw your last/first post. A bit of displaying a before and after would do you well. Please we can create magic but only with a good set of trick

 

[RumJellybean]

I would like to take the items from sheet 1 and 2 in these ranges and combine them into a single range as shown on page 3. I really don't have any work to show on it besides what is shown here. I was thinking consolidate would work but i dont know how to make it work in this way.
Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 167px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD></TR><TR style="HEIGHT: 31px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 14pt; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">Wood Materials</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>1" oak dowel rod</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Cedar Swing</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Landscape timber</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD></TR></TBODY></TABLE>
Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 167px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD></TR><TR style="HEIGHT: 31px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 14pt; BACKGROUND-COLOR: #ccccff; TEXT-ALIGN: center">Metal Materials</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>4" channel</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>2" angle iron</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>sheet metal</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>6"pipe</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD> </TD></TR></TBODY></TABLE>Sheet3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 167px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD></TR><TR style="HEIGHT: 31px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 14pt; BACKGROUND-COLOR: #00ccff; TEXT-ALIGN: center">ALL ITEMS</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #ccffff">4" channel</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ccffff">2" angle iron</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ccffff">sheet metal</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #ccffff">6"pipe</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #ccffff">1" oak dowel rod</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BACKGROUND-COLOR: #ccffff">Cedar Swing</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BACKGROUND-COLOR: #ccffff">Landscape timber</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BACKGROUND-COLOR: #ccffff"> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[dave3009]

Sub meh()
Sheet3.Activate
ActiveSheet.UsedRange.Offset(0).Clear
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then
ws.UsedRange.Copy
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
End Sub

Code was amended from here

 

[RumJellybean]

WOW. Thank you! That is exactly what i needed!! But i still am left wondering, why was that a hard thing to acomplish? It seems to me that there should be a simple command for it.

 

[RumJellybean]

Actually, there is a small problem. I have other sheets in the work book. I apologize, I should have mentioned that earlier. It worked great on the sample page that I posted, but it compiles more pages. Is there a way to limit the gathered data to those two specific ranges?

 

[dave3009]

Its untested but theoretically it should work

Sub meh()
Sheet3.Activate
ActiveSheet.UsedRange.Offset(0).Clear
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name And ws.name = "Sheet1" or ws.Name="Sheet2" Then
ws.UsedRange.Copy
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
End Sub

 

[RumJellybean]

Thanks thats much better! I am trying to get a grasp on how the variables are structured. So the specific "usedrange" has to be in the same place in all three sheets right? Also, what does the blue high-lighted string accomplish? I hope i'm not bugging you, but I have never messed aroung with VB and its quite tricky sometimes. Do you know of a good site with the commands posted so i can do some research for myself? I have looked at sites and watched videos all day trying to find something helpful. Now I feel like this > I need a beer.

Sub meh()
Sheet3.Activate
ActiveSheet.UsedRange.Offset(0).Clear
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name And ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then
ws.UsedRange.Copy
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
End If
Next
End Sub

 

[dave3009]

Well I'll start you off by pointing out that I quickly changed a code from a previous post I did state this. I personally would avoid UsedRange as this is more volatile than nitroglycerine.

I also don't approve of the Range("A65536").... Syntax so what I'll probably do tomorrow is rewrite the whole code in my style, it's nice to cut corners until you get pulled up

As for resources, hang about here read a few posts read the suggested solutions and try them, see what they do. Books help and MrExcel.com sells books and electronic learning there is some of it that can be found in any local book shop.

Don't be afraid to post here, everyone has a similar mindset, we learn from each other it's the way it works. The MVP's also ask questions when stumped, who knows everything?

 

[RumJellybean]

See I thought Usedrange was the actual range name so i tried editing them around, untill i realized that they werent names lol. Its hard to understand the way they describe/structure certain commands and alot of times the name of the command does something totally different thant you would have ever thought and alot of times seem backwards.

Yeah you are right i've been reading some posts, and there are some good people here. I'm pretty sure i'll be a regular now. I found this forum through Excelisfun. Him and Mr.Excel are the only two guys I can watch without going to sleep!