User Form as a tool to import data

[johnj81]

Hi Excel Community,

Need a little...a lot of help on this one. I'm trying to create a User form tool that will pull data based on a certain criteria by the user, from an Excel 2007 spreadsheet onto a different spreadsheet, and that will allow the user to name and save the new spreadsheet from the tool. It would kind of look like this:

[Label] "Insert Group Number" _ [textbox]
[Label] "Insert Sub Group" _ [textbox]
[Label] "Save As" _ [textbox]

[Button] "Generate"

Group numbers will be listed in column "A" and sub group numbers will be listing in column "B". There will be 10 other columns of data following, that correspond to those fields, but "A" and "B" will be the criteria elements. So if the user inserts "10" in the "Group Number" textbox and "2" in the "Sub Group" textbox, then names the file and clicks the generate button, all the data that corresponds to those criteria elements will populate on another workbook and saved as the desired saved name. I know...difficult...I took a VB class and I still don't know where to start other then declaring the variables and creating the form. The class I should have taken was VBA...= ( . Anyhow, any bit help would be much appreciated.

Thanks Peeps,

 

[Trevor G]

John,

If you look at recording a macro to apply a filter and test it with some sample, then stop recording you then have the code that you would need, and you can then adpat it so it runs the filter from the TextBoxes on your UserForm.

Then it would be a case of selecting the data and copying to a new workbook and save as you have planed.

Selecting filtered data could be done by using currentregion. so you would state something like

Sub rout()
Range("A2").CurrentRegion.Copy
End Sub

 

[johnj81]

Hi Trev,

That makes sense. Have the Macro do a filter for each criteria and then copy the data from that region. OK, I'll try that out.

Thanks,

 

[johnj81]

So this is what I came up with and theres 2 issues I can't seem to figure out. The macro doesn't seem to be selecting the correct criteria from the autofilter to pull up correct data, it sets the autofilter and then selects nothing, thus I get no data in the region. The other issue is that I'm not sure how to code the FileName textbox to be the name the file is saved as.

Dim RegionNumber As String
Dim GroupNumber As String
Dim FileName As String

Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$P$69233").AutoFilter Field:=1, Criteria1:="RegionNumber"
Range("B1").Select
ActiveSheet.Range("$A$1:$P$69233").AutoFilter Field:=2, Criteria1:="GroupNumber"
Range("A1").CurrentRegion.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir "M:\SampleJJJ"
ActiveWorkbook.SaveAs FileName:="M:\SampleJJJ\missing3.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close

Hopefully theres some parts that are correct in this code

 

[johnj81]

OK almost there. I figured out the criteria issue for the autofilter. Now I just need to figure out how to allow the user to create a saved name from the USER FORM, this is what I have so far:

Private Sub Generate_Click()
'Pulls date based on a certain criteria
Dim RegionNumber As Integer
Dim GroupNumber As Integer
Dim FileName As String

Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$P$69233").AutoFilter Field:=1, Criteria1:=Me.RegionNumber.Value
Range("B1").Select
ActiveSheet.Range("$A$1:$P$69233").AutoFilter Field:=2, Criteria1:=Me.GroupNumber.Value
Range("A1").CurrentRegion.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir "M:\SampleJJJ"
ActiveWorkbook.SaveAs FileName:="M:\SampleJJJ\missing3.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub

Ideas anyone? Thanks = )