Bringing Comments with cell contents when using AutoFilter

[Centurius]

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_Result").Value
= "*" & TextBox_Product_Name.Value & "*"
End If
If ComboBox_Category.Value <> "" Or False Then

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

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

Sheets("Inventory_Report").Range("Searched_Supplier_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_ExGST_Result").Value =
TextBox_Price_Ex_GST.Value
End If
If ComboBox_UOM.Value <> "" Or False Then
Sheets("Inventory_Report").Range("Searched_UOM_Result").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_Result"),
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").ClearContents
Range("Searched_Price_ExGST_Result").ClearContents

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

Range("A1").Select

Application.ScreenUpdating = True

End Sub

[Dave Peterson]

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_Result").Value
> = "*" & TextBox_Product_Name.Value & "*"
> End If
> If ComboBox_Category.Value <> "" Or False Then
>
> Sheets("Inventory_Report").Range("Searched_Category_Result").Value =
> "*" & ComboBox_Category.Value & "*"
> End If
> If ComboBox_SubCategory.Value <> "" Or False Then
>
> Sheets("Inventory_Report").Range("Searched_SubCategory_Result").Value =
> "*" & ComboBox_SubCategory.Value & "*"
> End If
> If ComboBox_Supplier.Value <> "" Or False Then
>
> Sheets("Inventory_Report").Range("Searched_Supplier_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_ExGST_Result").Value =
> TextBox_Price_Ex_GST.Value
> End If
> If ComboBox_UOM.Value <> "" Or False Then
> Sheets("Inventory_Report").Range("Searched_UOM_Result").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_Result"),
> 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").ClearContents
> Range("Searched_Price_ExGST_Result").ClearContents
>
> Range("G1").Formula = "FALSE"
>
> Range("A1").Select
>
> Application.ScreenUpdating = True
>
> End Sub

--

Dave Peterson

[Centurius]

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]

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

[Centurius]

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.

[Centurius]

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