See attached, with the code I used below...there are many other ways to do
this.
'This is hooked to your Export Specific Data Button on your form
Private Sub cmdExport_Click()
Dim intStart As Integer, intEnd As Integer
On Error GoTo whoops
intStart = Me.txtStart
intEnd = Me.txtEnd
If intStart > intEnd Then GoTo whoops
exportme
Me.Hide
Exit Sub
whoops:
MsgBox "Please enter two integers and try again"
End Sub
'This is put into a Module
Sub exportme()
''''''''''''''''''''''''''
'Original Script Written by www.ozgrid.com
''''''''''''''''''''''''''
Dim rRange As Range
Dim strCriteria As String, strCriteria2 As String
Dim lCol As Long
Dim rHeaderCol As Range
Dim xlCalc As XlCalculation
On Error Resume Next
Step1:
Set rRange = Range("A1").CurrentRegion
'Cancelled or non valid rage
If rRange Is Nothing Then Exit Sub
'Awlays use GoTo when selecting range so doesn't matter which Worksheet
Application.Goto rRange.Rows(1), True
step2:
lCol = 1
Step3:
strCriteria = frmExport.txtStart
strCriteria2 = frmExport.txtEnd
'Store current Calculation then switch to manual.
'Turn off events and screen updating
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
'Remove any filters
ActiveSheet.AutoFilterMode = False
Dim s As Range
With rRange 'Filter, offset(to exclude headers)
.AutoFilter Field:=lCol, Criteria1:=">=" & strCriteria,
Operator:=XlAutoFilterOperator.xlAnd, Criteria2:="<=" & strCriteria2
.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
End With
Workbooks.Add
ActiveSheet.Paste
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\YourFile.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
'Remove any filters
ActiveSheet.AutoFilterMode = False
'Revert back
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With
On Error GoTo 0
End Sub
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
ExportRange.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12
