Need help copying results from an autofilter to a new worksheet

ozarkhigh

New Member
Joined
Aug 27, 2009
Messages
12
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?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Not sure I understand your question though.

You can select the returned results and copy-paste them to another sheet.
 
Upvote 0
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.
 
Upvote 0
Maybe something along the lines of:

Code:
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.
 
Upvote 0
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.
 
Upvote 0
Like this:

Code:
Sheets("Worksheet").Range("A12").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Sheets("Quote to Customer").Range("A1")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I only asked you to run this simple procedure:

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

to know whether the part in the code on:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,454
Messages
6,178,765
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top