PDA

View Full Version : Error of Advanced filter, copy & paste from different worksheets using 1 cmd button



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