sotong
06-06-2012, 08:38 AM
Hi,
I am trying to build up VBA code in 1 command button when click, it will
1) advanced filter based on 2 criteria (Column field 1 and column field 2) of 2 different worksheets and range cells
2) after filtering, copy and paste filtered data into 2 different target worksheets
3) Source worksheets have different number of columns
4) after performing copy and paste function, the source data should unfilter to showalldata to prepare for next selection
5) When the same button is click again, the target worksheets should clearcontents
I had the first target worksheet worked as desired. However, the 2nd worksheets, either a) once filtered it doesn't copy and past to the target worksheet, b) showalldata function doesn't work on it for next selection to happen, c) clearcontent of second worksheet did not happen
As i am not too familiar with VBA of combining the codes into 1 button, i build them separately as "Sub cmdrun_Click()" and "Sub cmdrun_Click2".
Appreciate any helps out here to take a look at my VBA code for the command that i could have missed out. Thanks peeps.
Sub cmdrun_Click()
Dim ws As String
Dim Lastinrow, Lastincolumn As Long
Dim rngcopy As Range
Dim rngpaste As Range
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
Set rngpaste = Worksheets("Sheet1").Range("B20:BJ60000")
rngpaste.Select
rngpaste.ClearContents
Sheets("Input Data").Select
Lastinrow = ActiveSheet.UsedRange.Rows.Count
Set rngcopy = Worksheets("Input Data").Range("A4:BI" & Lastinrow) 'set filter range
rngcopy.Select
ShowAllRecords
If ComboBox1.Value <> "APLA" And ComboBox2.Value <> "All" Then
With rngcopy
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
.AutoFilter Field:=2, Criteria1:=ComboBox2.Value
.Select
End With
ElseIf ComboBox1.Value <> "APLA" And ComboBox2.Value = "All" Then
With rngcopy
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
.Select
End With
ElseIf ComboBox1.Value = "APLA" And ComboBox2.Value <> "All" Then
With rngcopy
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox2.Value
.Select
End With
Else
With rngcopy
.Select
End With
End If
Selection.Copy
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
Worksheets("Sheet1").Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Sheet1").Rows("20:20").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Worksheets("Sheet1").Range("B20").Select
End Sub
Sub ShowAllRecords()
If Worksheets("Input Data").FilterMode Then
Worksheets("Input Data").ShowAllData
End If
End Sub
Sub cmdrun_Click2()
Dim ws As String
Dim Lastinrow, Lastincolumn As Long
Dim rngcopy2 As Range
Dim rngpaste2 As Range
Sheets("Control").Visible = True
Sheets("Control").Select
Set rngpaste2 = Worksheets("Control").Range("E27:BC60000")
rngpaste2.Select
rngpaste2.ClearContents
Sheets("Input Data - BU").Select
Lastinrow = ActiveSheet.UsedRange.Rows.Count
Set rngcopy2 = Worksheets("Input Data - BU").Range("A4:AY" & Lastinrow) 'set filter range
rngcopy2.Select
ShowAllRecords_2
If ComboBox1.Value <> "APLA" And ComboBox2.Value <> "All" Then
With rngcopy2
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
.AutoFilter Field:=2, Criteria1:=ComboBox2.Value
.Select
End With
ElseIf ComboBox1.Value <> "APLA" And ComboBox2.Value = "All" Then
With rngcopy2
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
.Select
End With
ElseIf ComboBox1.Value = "APLA" And ComboBox2.Value <> "All" Then
With rngcopy2
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox2.Value
.Select
End With
Else
With rngcopy2
.Select
End With
End If
Selection.Copy
Sheets("Control").Visible = True
Sheets("Control").Select
Worksheets("Control").Range("E27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Control").Rows("27:27").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Worksheets("Control").Range("E27").Select
End Sub
Sub ShowAllRecords_2()
If Worksheets("Input Data - BU").FilterMode Then
Worksheets("Input Data - BU").ShowAllData
End If
'Selection.FilterMode
End Sub
I am trying to build up VBA code in 1 command button when click, it will
1) advanced filter based on 2 criteria (Column field 1 and column field 2) of 2 different worksheets and range cells
2) after filtering, copy and paste filtered data into 2 different target worksheets
3) Source worksheets have different number of columns
4) after performing copy and paste function, the source data should unfilter to showalldata to prepare for next selection
5) When the same button is click again, the target worksheets should clearcontents
I had the first target worksheet worked as desired. However, the 2nd worksheets, either a) once filtered it doesn't copy and past to the target worksheet, b) showalldata function doesn't work on it for next selection to happen, c) clearcontent of second worksheet did not happen
As i am not too familiar with VBA of combining the codes into 1 button, i build them separately as "Sub cmdrun_Click()" and "Sub cmdrun_Click2".
Appreciate any helps out here to take a look at my VBA code for the command that i could have missed out. Thanks peeps.
Sub cmdrun_Click()
Dim ws As String
Dim Lastinrow, Lastincolumn As Long
Dim rngcopy As Range
Dim rngpaste As Range
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
Set rngpaste = Worksheets("Sheet1").Range("B20:BJ60000")
rngpaste.Select
rngpaste.ClearContents
Sheets("Input Data").Select
Lastinrow = ActiveSheet.UsedRange.Rows.Count
Set rngcopy = Worksheets("Input Data").Range("A4:BI" & Lastinrow) 'set filter range
rngcopy.Select
ShowAllRecords
If ComboBox1.Value <> "APLA" And ComboBox2.Value <> "All" Then
With rngcopy
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
.AutoFilter Field:=2, Criteria1:=ComboBox2.Value
.Select
End With
ElseIf ComboBox1.Value <> "APLA" And ComboBox2.Value = "All" Then
With rngcopy
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
.Select
End With
ElseIf ComboBox1.Value = "APLA" And ComboBox2.Value <> "All" Then
With rngcopy
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox2.Value
.Select
End With
Else
With rngcopy
.Select
End With
End If
Selection.Copy
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Select
Worksheets("Sheet1").Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Sheet1").Rows("20:20").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Worksheets("Sheet1").Range("B20").Select
End Sub
Sub ShowAllRecords()
If Worksheets("Input Data").FilterMode Then
Worksheets("Input Data").ShowAllData
End If
End Sub
Sub cmdrun_Click2()
Dim ws As String
Dim Lastinrow, Lastincolumn As Long
Dim rngcopy2 As Range
Dim rngpaste2 As Range
Sheets("Control").Visible = True
Sheets("Control").Select
Set rngpaste2 = Worksheets("Control").Range("E27:BC60000")
rngpaste2.Select
rngpaste2.ClearContents
Sheets("Input Data - BU").Select
Lastinrow = ActiveSheet.UsedRange.Rows.Count
Set rngcopy2 = Worksheets("Input Data - BU").Range("A4:AY" & Lastinrow) 'set filter range
rngcopy2.Select
ShowAllRecords_2
If ComboBox1.Value <> "APLA" And ComboBox2.Value <> "All" Then
With rngcopy2
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
.AutoFilter Field:=2, Criteria1:=ComboBox2.Value
.Select
End With
ElseIf ComboBox1.Value <> "APLA" And ComboBox2.Value = "All" Then
With rngcopy2
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
.Select
End With
ElseIf ComboBox1.Value = "APLA" And ComboBox2.Value <> "All" Then
With rngcopy2
.Select
.AutoFilter Field:=1, Criteria1:=ComboBox2.Value
.Select
End With
Else
With rngcopy2
.Select
End With
End If
Selection.Copy
Sheets("Control").Visible = True
Sheets("Control").Select
Worksheets("Control").Range("E27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets("Control").Rows("27:27").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Worksheets("Control").Range("E27").Select
End Sub
Sub ShowAllRecords_2()
If Worksheets("Input Data - BU").FilterMode Then
Worksheets("Input Data - BU").ShowAllData
End If
'Selection.FilterMode
End Sub