Help

mike2903

New Member
Joined
Jun 22, 2010
Messages
7
Morning All

I've never used Visual Basics before so bear with me. I've been told that the summaries/results I require from my information require using VB rather than vlookups etc so I'm stumped.

Here's what I'll have

3 sheets - INBOUND, OUTBOUND and SUMMARY

Outbound Columns
Depot No / Depot Name / Consignment No / Trailer No / Ref / Pallets Qty / Boards Qty / Collar Qty

Inbound Columns
Depot No / Depot Name / Collecting Site / Pallets Qty / Boards Qty / Collars Qty / Trailer No

Both Inbound and Outbound will have data added to it every day from every depot, in a year there will be around 30000 outbound entries.


And this is where i need need help. On the summary page I need to be able to select a depot number or name from a list and for it to display every outbound and inbound entry. Is it possible? If so, how do I do manage it?

Hope it all makes sense, many thanks for you help.

Mike
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
Hi,

Could try comething like

for Depot No:

Code:
Sub GetDepNo ()
Dim DepNo As Range
Set DepNo = sheets("Summary").Range("A1")
 
Dim inbrng As Range
    Set inbrng = Sheets("INBOUND").Range("A2:H50000").SpecialCells(xlCellTypeVisible)
 
 
Dim outrng As Range
    Set outrng = Sheets("OUTBOUND").Range("A2:H50000").SpecialCells(xlCellTypeVisible)
 
Sheets("INBOUND").Select
 
Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:= DepNo.Value
 
rng.select
Selection.Copy
Sheets("Summary).Range("A3").Select
ActiveSheet.Paste
 
Sheets("OUTBOUND").Select
 
Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:= DepNo.Value
 
rng.select
Selection.Copy
Sheets("Summary).Range("I3").Select
ActiveSheet.Paste
 
 
End Sub

*untested*
 

Dannyh1

Well-known Member
Joined
Nov 18, 2009
Messages
1,144
Hi,

It's not stupid question if you don't use code

Tools - Macros - Visual basic - Insert - Module

This will give you white space on the right, put code here.

Note - I've made some assumptions on where your data starts & where you want it to go so this may need tweeking.

Also realised I missed code that clears out data each time

Add this part to before where it says "Sheets("INBOUND").Select"
Code:
Sheets("Summary").Select
Range("A3:R3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

There will be a better solution available, but I am still learning also - should dot the trick if no other replies.
 

Forum statistics

Threads
1,141,019
Messages
5,703,766
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top