Help

[mike2903]

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

 

[Dannyh1]

Hi,

Could try comething like

for Depot No:

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*

 

[mike2903]

Might sound stupid but what do i attach the macro to?

 

[Dannyh1]

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"

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.