Скачать или смотреть Use advanced filter with VBA

Use advanced filter with VBA. Filter data more quickly. Free templates and templates with code are available for purchase for $50 USD
https://www.easyexcelanswers.com/temp...

Click this link to check out my one-on-one training http://www.calendly.com/easyexcelanswers

For more help visit my website www.easyexcelanswers.com or email me at [email protected].

Contact me regarding customizing this template for your needs.

Click for online Excel Consulting http://www.calendly.com/easyexcelanswers

Are you struggling to update your Microsoft SQL Server data?
I recommend using well-known Excel to update and manage your data in SQL Server using the SQL Spreads Excel add-in.
Click https://sqlspreads.com/?pid=barbhende... to find out how SQL Spreads works best for non-technical end users and download our free 14-day trial. You will get two months free when purchasing an annual subscription using our link.

I am able to provide online help on your computer at a reasonable rate.

https://www.amazon.com/shop/barbhende...

I use a Blue condenser Microphone to record my videos, here is the link
https://amzn.to/37gyyGa

Check out Crowdcast for creating your webinars
https://app.linkmink.com/a/crowdcast/83


I use Tube Buddy to help promote my videos
Check them out
https://www.Tubebuddy.com/easyexcelan...

Follow me on Facebook
  / easyexcel.answers  

TWEET THIS VIDEO

Follow me on twitter
easyexcelanswers

IG @barbhendersonconsulting

You can help and generate a translation to you own language
http://www.youtube.com/timedtext_cs_p...

*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.

How to insert VBA code in Excel    • How to insert VBA code in Excel  

Private Sub CommandButton1_Click()
sname = sname.Text
Dim datasheet, temp As Worksheet
Dim ws As Worksheet
Dim erow As Integer
Dim sdata As Range
Dim sCriteria As Range
Dim valoutput As Range
Set datasheet = Sheet1
'copy the criteria name over to critera region
datasheet.Range("N1").Value = datasheet.Range("J1").Value
'create a new worksheet for the data
With ThisWorkbook
Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
ws.Name = "temp"
End With
'specify the range of data that you want
ws.Range("A1:c1").Value = datasheet.Range("f1:H1").Value
'put the salesperson name in the search critera
datasheet.Range("D2").Value = sname
'set the data to search
Set sdata = ThisWorkbook.Worksheets("Sheet1").Range("e2").CurrentRegion
'set the criteria to search by
Set sCriteria = ThisWorkbook.Worksheets("Sheet1").Range("N1").CurrentRegion
'set location that you want to send filtered data
Set valoutput = ThisWorkbook.Worksheets("temp").Range("a1").CurrentRegion
'filter the data
sdata.AdvancedFilter xlFilterCopy, sCriteria, valoutput
'activate the out put
ws.Activate
'determine the end row
erow = ws.Cells(Rows.Count, 1).End(xlUp).Row
'total the sales of the salesperson
sotot = Application.Sum(ws.Range(Cells(2, 1), Cells(erow, 1)))
'put it on the sheet
ws.Range("E1").Value = sname
ws.Range("f1").Value = "Total Sales"
'put the sales person name
ws.Range("f2").Value = sotot
'make the colums fit
ws.UsedRange.Columns.AutoFit
End Sub