Copying and Pasting

[Dowsey1977]

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
[VBA]
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
[/VBA]

and
In standard module
[VBA]
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
[/VBA]

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?

[Zack Barresse]

Change this line in your command button routine ...

[vba]FilterAndCopy rng, ComboBox1.Value, CheckBox1.Value[/vba]

(assuming you add a checkbox and it's codename is CheckBox1)

And change these two lines in your FilterAndCopy routine ...

[vba]Function FilterAndCopy(rng As Range, Choice As String, ckbAll As Boolean)
'...
If ckbAll Then rng.AutoFilter Field:=1, Criteria1:=Choice[/vba]

See if that helps.

[Dowsey1977]

Tried that, but no joy. But I wasn't sure where to put the 2nd line of code in the filter and copy routine.Tried to post an example of the spreadsheet on here, but for some reason I can't do it (seems to be an ongoing problem - but not sure if there's some sort of restriction on the work PC).

[Bob Phillips]

may be a good idea not to use the combo value for the sheet name when you do an all

[vba]

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
If chkbAll Then
Choice = "AllData"
Else
rng.AutoFilter Field:=1, Criteria1:=Choice
End If
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

[/vba]

I don't see where the sheets get created, so you would need a sheet called 'AllData' using this.