With Statement Questions

Hi everybody,

I have two (2) questions.

1.
Can you copy a range within one "With" statement and paste it in another "With" statement immediately following the first? I've tried and "CutCopyMode" seems to turn off when the first "With" statement is ended.

2.
The following code was originally written to run outside a "With" statement. I have placed it in a "With" statement but I know the syntax is wrong on several lines. Can anybody give me the correct syntax?

With Workbooks("ONLINE.xls").Worksheets("System Export")
    ' Select cell A4 and PasteSpecial values.
    ActiveSheet.Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False  ' Cancel Cut or Copy mode.
    ' Resize the current selection to remove the header row from the data sort.
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select
    ' Sort the slected range by txtSurname then txtFirstName.
    Selection.Sort Key1:=ActiveCell.Offset(0, 4), Order1:=xlAscending, Key2:=ActiveCell.Offset(0, 5), Order2:=xlAscending
End With

royUK,

Sorry it was 1:30am and brain fade/frustration had set in.
I'm new to VBA which doesn't help either.
Hopefully this will give you a clearer picture.

Sub Copy_Centre_System_Data()
'
' Macro created 30/06/2007 by Craig.
' Copy the Centre System data from the User nominated file.
'
    Dim rBottomCellA As Range  ' Declare the local variable rBottomCellA as a range.
    
    ' Execute a series of statements on the "varOpenFile" workbook in the "Details - Alphabetic" worksheet.
    With Workbooks(varOpenFile).Worksheets("Details - Alphabetic")
        ' This User selected worksheet is produced by the Centre System 'Details' report (LAR127).
        ' The data in this worksheet is not contiguous.  To overcome this scenario manually identify the top left cell
        ' (A1) of the dataset and test to identify the bottom left cell of the dataset.
        Set rBottomCellA = .Cells(65536, 5)  ' Set the range rBottomCellA equal to the last Row of Column E.
        If IsEmpty(rBottomCellA) Then  ' If the range rBottomCellA is empty.
            ' Set the range rBottomCellA equal to the value of the next populated cell above and 4 cells to the left.
            Set rBottomCellA = rBottomCellA.End(xlUp).Offset(0, -4)
        End If
        ' Select the range A1:rBottomCellA and 33 Columns to the right creating a range and copy it.
        .Range("A1", rBottomCellA.Offset(0, 33)).Copy
    End With
    
    ' ************************************************************************************************************
    ' I want to place the following section of code in a "With" statement so I don't have to activate the workbook
    ' but have struggled with the syntax and the "Paste".
    ' ************************************************************************************************************
    
    ' Activate the "Centre System Export" worksheet in the "Centre System To clubs ONLINE.xls" workbook.
    Workbooks("Centre System To clubs ONLINE.xls").Worksheets("Centre System Export").Activate
    ActiveSheet.Unprotect (conPasswd)  ' Unprotect the active sheet.
    ' Select cell A4 and PasteSpecial values to the active cell.
    ActiveSheet.Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False  ' Cancel Cut or Copy mode and remove the moving border.
    ' Resize the current selection to remove the header row from the data sort.
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Select
    ' Sort the slected range by txtSurname then txtFirstName.
    Selection.Sort Key1:=ActiveCell.Offset(0, 4), Order1:=xlAscending, Key2:=ActiveCell.Offset(0, 5), _
        Order2:=xlAscending
    Workbooks(varOpenFile).Close SaveChanges:=False  ' Close the User selected file without saving changes.
    ActiveSheet.Protect (conPasswd)  ' Protect the active worksheet.

End Sub