I am currently working on a spreadsheet for my workplace that makes use
of the AutoFilter feature to provide reports on our inventory prices
etc. using criteria entered via a UserForm

Some of the items in the inventory list have comments attached and i
was wondering if it is possible to bring these comments with the cell
when it is transferred to my report page

my module is below, excuse the severely bloated code, i'm not too
efficient with VBA as yet but it works. (apart from the comments of
course)

TIA!
Dan
------------------------------------------------------------------------------
Private Sub Button_Create_Report_Click()
Application.ScreenUpdating = False

If TextBox_Product_Name.Value < "" Or False Then
Sheets("Inventory_Report").Range("Searched_Name_Re sult").Value
= "*" & TextBox_Product_Name.Value & "*"
End If
If ComboBox_Category.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_Categor y_Result").Value =
"*" & ComboBox_Category.Value & "*"
End If
If ComboBox_SubCategory.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_SubCate gory_Result").Value =
"*" & ComboBox_SubCategory.Value & "*"
End If
If ComboBox_Supplier.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_Supplie r_Result").Value =
"*" & ComboBox_Supplier.Value & "*"
End If
If TextBox_Product_Code.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_Product _Code_Result").Value
= "*" & TextBox_Product_Code.Value & "*"
End If
If TextBox_Price_Ex_GST.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_Price_E xGST_Result").Value =
TextBox_Price_Ex_GST.Value
End If
If ComboBox_UOM.Value < "" Or False Then
Sheets("Inventory_Report").Range("Searched_UOM_Res ult").Value =
"*" & ComboBox_UOM.Value & "*"
End If

ActiveWorkbook.Sheets("Inventory_Report").Activate
Unload Form_Create_Report
Set DataRange = Sheets("Inventory").Range("A1:G" &
Sheets("Inventory").Range("G65536").End(xlUp).Row)
DataRange.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("Searched_Name:Searched_UOM_R esult"),
CopyToRange:=Sheets("Inventory_Report").Range("A2: G2"), Unique:=False

ActiveWorkbook.Sheets("Inventory_Report").Activate

Range("Searched_Name_Result").ClearContents
Range("Searched_Category_Result").ClearContents
Range("Searched_SubCategory_Result").ClearContents
Range("Searched_Supplier_Result").ClearContents
Range("Searched_Product_Code_Result").ClearContent s
Range("Searched_Price_ExGST_Result").ClearContents

Range("G1").Formula = "FALSE"

Range("A1").Select

Application.ScreenUpdating = True

End Sub

Instead of advance filtering to a new location, how about just advance filtering
in place.

Then you could copy those visible cells to the new location and the comments
will follow those cells.



Centurius wrote:

I am currently working on a spreadsheet for my workplace that makes use
of the AutoFilter feature to provide reports on our inventory prices
etc. using criteria entered via a UserForm

Some of the items in the inventory list have comments attached and i
was wondering if it is possible to bring these comments with the cell
when it is transferred to my report page

my module is below, excuse the severely bloated code, i'm not too
efficient with VBA as yet but it works. (apart from the comments of
course)

TIA!
Dan
------------------------------------------------------------------------------
Private Sub Button_Create_Report_Click()
Application.ScreenUpdating = False

If TextBox_Product_Name.Value < "" Or False Then
Sheets("Inventory_Report").Range("Searched_Name_Re sult").Value
= "*" & TextBox_Product_Name.Value & "*"
End If
If ComboBox_Category.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_Categor y_Result").Value =
"*" & ComboBox_Category.Value & "*"
End If
If ComboBox_SubCategory.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_SubCate gory_Result").Value =
"*" & ComboBox_SubCategory.Value & "*"
End If
If ComboBox_Supplier.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_Supplie r_Result").Value =
"*" & ComboBox_Supplier.Value & "*"
End If
If TextBox_Product_Code.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_Product _Code_Result").Value
= "*" & TextBox_Product_Code.Value & "*"
End If
If TextBox_Price_Ex_GST.Value < "" Or False Then

Sheets("Inventory_Report").Range("Searched_Price_E xGST_Result").Value =
TextBox_Price_Ex_GST.Value
End If
If ComboBox_UOM.Value < "" Or False Then
Sheets("Inventory_Report").Range("Searched_UOM_Res ult").Value =
"*" & ComboBox_UOM.Value & "*"
End If

ActiveWorkbook.Sheets("Inventory_Report").Activate
Unload Form_Create_Report
Set DataRange = Sheets("Inventory").Range("A1:G" &
Sheets("Inventory").Range("G65536").End(xlUp).Row)
DataRange.AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("Searched_Name:Searched_UOM_R esult"),
CopyToRange:=Sheets("Inventory_Report").Range("A2: G2"), Unique:=False

ActiveWorkbook.Sheets("Inventory_Report").Activate

Range("Searched_Name_Result").ClearContents
Range("Searched_Category_Result").ClearContents
Range("Searched_SubCategory_Result").ClearContents
Range("Searched_Supplier_Result").ClearContents
Range("Searched_Product_Code_Result").ClearContent s
Range("Searched_Price_ExGST_Result").ClearContents

Range("G1").Formula = "FALSE"

Range("A1").Select

Application.ScreenUpdating = True

End Sub

--

Dave Peterson

I May have to do this if there isn't an easier way. i was hoping for a
switch to add to the autofilter command but i'm guessing my chances are
slim to none.

when the macro is finished the autofilter would cancel out correct?
returning my inventory sheet to displaying all records again?

Advanced filter???

Nope, but you could record a macro when you do:
data|Filter|show all
and merge that with your code.

Centurius wrote:

I May have to do this if there isn't an easier way. i was hoping for a
switch to add to the autofilter command but i'm guessing my chances are
slim to none.

when the macro is finished the autofilter would cancel out correct?
returning my inventory sheet to displaying all records again?

--

Dave Peterson

sorry, meant to type advanced filter.

i'd just add that 'show all' to the end after copying out the cells

Thanks Dave for the help, shouldnt be too hard.. i'll have to take a
look, meanwhile, if anyone else knows any possible solutions. please
post up, might suit my application better.

got this all working, few more procedures to carry out with the
clearing/copying/pasting to be done, but it does work, thanks heaps!