Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
    ActiveWorkbook.Save
    Range("C5:C12", ["E5:E12"]).Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Range("A1048576").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlPasteValues, Transpose:=True
    Sheets("Sheet1").Select
    Range("C13").Copy
    Sheets("Sheet2").Select
    ActiveSheet.Range("A1048576").End(xlUp).Offset(0, 20).Select
    Selection.PasteSpecial xlPasteValues
    Sheets("Sheet1").Select
    [B2:M15].ClearContents
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim lastRow As Long
    lastRow = Worksheets("Sheet2").Range("a" & _
        Worksheets("Sheet2").Rows.Count).End(xlUp).Offset(1, 0).Row
    Range("C5:C12").Copy
    Worksheets("Sheet2").Range("a" & lastRow).PasteSpecial xlPasteValues, _
        Transpose:=True
    Range("E5:E12").Copy
    Worksheets("Sheet2").Range("t" & lastRow).PasteSpecial xlPasteValues, _
        Transpose:=True
    'Other code...
    Sheets("Sheet1").Range("C2:C12, E2:E12, G2:G12").ClearContents
    Application.CutCopyMode = False
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim lastRow As Long
    lastRow = Worksheets("Sheet2").Range("a" & _
        Worksheets("Sheet2").Rows.Count).End(xlUp).Offset(1, 0).Row
    Range("C5:C21").Copy
    Worksheets("Sheet2").Range("a" & lastRow).PasteSpecial xlPasteValues, _
        Transpose:=True
    Range("E5:E21").Copy
    Worksheets("Sheet2").Range("q" & lastRow).PasteSpecial xlPasteValues, _
        Transpose:=True
    Range("G5:G21").Copy
    Worksheets("Sheet2").Range("ah" & lastRow).PasteSpecial xlPasteValues, _
        Transpose:=True
    Sheets("Sheet1").Range("C2:C21, E2:E21, G2:G21").ClearContents
    Application.CutCopyMode = False
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim lastRow As Long
    lastRow = Worksheets("Sheet2").Range("a" & _
        Worksheets("Sheet2").Rows.Count).End(xlUp).Offset(1, 0).Row
    Range("C5:C21").Copy
    Worksheets("Sheet2").Range("a" & lastRow).PasteSpecial xlPasteValues, _
        Transpose:=True
    Range("E5:E21").Copy
    Worksheets("Sheet2").Range("q" & lastRow).PasteSpecial xlPasteValues, _
        Transpose:=True
    Range("G5:G21").Copy
    Worksheets("Sheet2").Range("ah" & lastRow).PasteSpecial xlPasteValues, _
        Transpose:=True
    If lastRow > 100 Then Exit Sub
    Sheets("Sheet1").Range("C2:C21, E2:E21, G2:G21").ClearContents
    Application.CutCopyMode = False
    If lastRow > 100 Then Exit Sub
End Sub

Code: Select all

'Other code...
Dim lastRow As Long
lastRow = Worksheets("Sheet2").Range("a" & _
    Worksheets("Sheet2").Rows.Count).End(xlUp).Offset(1, 0).Row
if lastRow > 100 then exit sub
'Other code...
[/code