Collecting Data

[ads_3131]

Hello again people...

Im stuck :( , I have a workbook that contains two worksheets.

Sheet1 is pure data, & Sheet2 is where im wanting to lookup the data from Sheet1 but in an formatted / organised way.

What i am wanting to do is pull values from sheet1 in a tabled column called supplier, and place them in Sheet2.... but.... in this supplier column there is gaps between the data, unfilled etc

so with this lookup im wanting to place in Sheet2 the data neatly under each other so the result will be a column of suppliers (in sheet2) with no gaps between rows/values.

I doubt that an formula is able to do this? or if a macro is best ?

Any help would be great! thanks :)

[Aussiebear]

Sort the sheet so the blanks in the suppliers column are at the bottom

[ads_3131]

Hi Aussiebear

Good idea... however there needs to be the gaps with the raw data (Sheet1) for other fields/processes etc :/

so sheet1 data cannot be formatted in that way .... All has to happen on sheet2 looking up sheet1

tar

[CodeNinja]

Try this... Just change the sort fields to whatever column you want to... and change the ranges to whatever sheets they are copying and pasting to...


Sub test()
Dim rngSht1 As Range
Dim rngSht2 As Range
Set rngSht1 = Sheet1.Cells
Set rngSht2 = Sheet2.Cells
rngSht1.Copy
Sheet2.Activate
Cells(1, 1).Select
Selection.PasteSpecial
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A:A")
.SetRange rngSht2
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

[ads_3131]

Thankyou for the reply....

i tried that macro :/ cannot get it to work at all.... are you able to provide an example with the macro ?

tar

[Tinbendr]

Please provide sample.