Dowsey1977
08-09-2006, 09:48 AM
Hi,
I am using the below code (taken from the Knowledge base, adapted slightly) to copy and paste rows of information to other sheets, based on what is in the first column.
In UserForm1
Option Explicit
Private Sub CommandButton1_Click()
Dim rng As Range
Application.ScreenUpdating = False
'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Sheets("TSC work").Select
Set rng = ActiveSheet.UsedRange
'Cancel if no value entered in textbox
If ComboBox1.Value = "" Then GoTo ws_exit:
'Call function Filterandcopy
FilterAndCopy rng, ComboBox1.Value
rng.AutoFilter
'Exit sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
Sheets("Lists").Select
Application.ScreenUpdating = True
End Sub
and
In standard module
Option Explicit
Function FilterAndCopy(rng As Range, Choice As String)
Dim WkSheet As String
WkSheet = Choice
Dim FiltRng As Range
'Clear Contents to show just new search data
Worksheets(Choice).Cells.ClearContents
'Set the column to filter (In This Case 1 or A)
'Change as required
rng.AutoFilter Field:=1, Criteria1:=Choice
On Error Resume Next
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0
'Copy Data across to sheet 2
FiltRng.Copy Worksheets(Choice).Range("A1")
'Display Data
Worksheets(Choice).Select
Range("A1").Select
Set FiltRng = Nothing
End Function
So a form appears, and the user selects a name from the list. Then all the rows with that value in the first column are pasted to the sheet with the same name.
What I want to be able to do, is add something somewhere on the form (be it a checkbox or something in the combobox) that copies and pastes all records to their relevant sheet.
To make this a bit clearer, the contents of column A are names of people, and then there are sheets with the names. So, I want the 'Copy All' addition to copy all the rows with Name1 to sheet Name1, and all the rows with Name2 to sheet Name2 etc...
Any ideas if this is possible?
I am using the below code (taken from the Knowledge base, adapted slightly) to copy and paste rows of information to other sheets, based on what is in the first column.
In UserForm1
Option Explicit
Private Sub CommandButton1_Click()
Dim rng As Range
Application.ScreenUpdating = False
'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Sheets("TSC work").Select
Set rng = ActiveSheet.UsedRange
'Cancel if no value entered in textbox
If ComboBox1.Value = "" Then GoTo ws_exit:
'Call function Filterandcopy
FilterAndCopy rng, ComboBox1.Value
rng.AutoFilter
'Exit sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
Sheets("Lists").Select
Application.ScreenUpdating = True
End Sub
and
In standard module
Option Explicit
Function FilterAndCopy(rng As Range, Choice As String)
Dim WkSheet As String
WkSheet = Choice
Dim FiltRng As Range
'Clear Contents to show just new search data
Worksheets(Choice).Cells.ClearContents
'Set the column to filter (In This Case 1 or A)
'Change as required
rng.AutoFilter Field:=1, Criteria1:=Choice
On Error Resume Next
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0
'Copy Data across to sheet 2
FiltRng.Copy Worksheets(Choice).Range("A1")
'Display Data
Worksheets(Choice).Select
Range("A1").Select
Set FiltRng = Nothing
End Function
So a form appears, and the user selects a name from the list. Then all the rows with that value in the first column are pasted to the sheet with the same name.
What I want to be able to do, is add something somewhere on the form (be it a checkbox or something in the combobox) that copies and pastes all records to their relevant sheet.
To make this a bit clearer, the contents of column A are names of people, and then there are sheets with the names. So, I want the 'Copy All' addition to copy all the rows with Name1 to sheet Name1, and all the rows with Name2 to sheet Name2 etc...
Any ideas if this is possible?