Need help copying results from an autofilter to a new worksheet

[ozarkhigh]

I'm working on a quote template that has 600+ products with descriptions and prices that gets autofiltered down to one product. After it has been filtered down to that one product how do I link that to a new worksheet?

 

[wigi]

Not sure I understand your question though.

You can select the returned results and copy-paste them to another sheet.

 

[ozarkhigh]

This is for sales guys and they will work the quote up on one sheet and then send the quote from different sheet. They're computer skills are pretty bad, so copying and pasting something still leaves room for error.

Ideally, when the sale guys finishes sorting down to the product they want to sale it automatically populates the quote sheet. So all they have to do is click on the tab at the bottom and print the quote out.

 

[wigi]

Maybe something along the lines of:

ThisWorkbook.Sheets(1).UsedRange.SpecialCells(xlCellTypeVisible).Copy ThisWorkbook.Sheets(2).Range("A1")

This code will copy the visible cells (those that are not hidden through the autofilter) of the first sheet in the workbook to cell A1 on the second sheet.

 

[ozarkhigh]

I'm new to macros. My Sheets are titled "Worksheet" for the where they do the filtering and "Quote to Customer" is what they send to customer.

My autofiltered range is in A through H and goes from 12 to 612.

 

[wigi]

Like this:

Sheets("Worksheet").Range("A12").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Sheets("Quote to Customer").Range("A1")

 

[ozarkhigh]

It's giving me an error...this is what I have.

Sub Macro2()
'
' Macro2 Macro
' Sheets("Worksheet").Range("A12").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Sheets("Quote to Customer").Range("A13")
'

Sheets("Sheet2").Select
End Sub

 

[wigi]

Hmmm.

- Which error do you get?

- The sheet names are correct?

- There are visible cells I presume?

- What does this give you:
Msgbox Sheets("Worksheet").Range("A12").CurrentRegion.Address

 

[ozarkhigh]

It says "Can't execute code in break mode" and there is a yellow highlight on Sub Macro2()

This is what I have:
Sub Macro2()
'
' Macro2 Macro
' Msgbox Sheets("Worksheet").Range("A12").CurrentRegion.Address.SpecialCells(xlCellTypeVisible).Copy Sheets("Quote to Customer").Range("A13")
'

'
Sheets("Sheet2").Select
End Sub

 

[wigi]

I only asked you to run this simple procedure:

Msgbox Sheets("Worksheet").Range("A12").CurrentRegion.Address

to know whether the part in the code on:

Sheets("Worksheet").Range("A12").CurrentRegion

is what we expect / need. The result in the MsgBox should be something like: $A$12:$H$612

First, stop all macros / debugging by clicking Run in the menu bar, and pick Reset in the Run menu.

Then execute the macro with the Msgbox, tell us the result, and after that we'll look further.