PDA

View Full Version : Copying and Pasting



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?

Zack Barresse
08-09-2006, 11:31 AM
Change this line in your command button routine ...

FilterAndCopy rng, ComboBox1.Value, CheckBox1.Value

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

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

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

See if that helps.

Dowsey1977
08-10-2006, 05:47 AM
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
08-10-2006, 08:36 AM
may be a good idea not to use the combo value for the sheet name when you do an all



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



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