Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen

VBA

  • VBA von Marc vom 03.11.2014 05:37:07

Betrifft: VBA von: Marc
Geschrieben am: 03.11.2014 05:37:07

Hi,

attached uploaded file. I hope you can help me.

On the Input tab I would like to copy B1:B3 to the Output tab B2:D2 once the Submit button has been pushed. Also automatically a No. " 1 " in A1 should be added.

When there has been another entry it should be copied to the next available space in this case B3:D3 and another No "2" added.

Thanks,

Marc

https://www.herber.de/bbs/user/93497.xlsx

  

Betrifft: AW: VBA von: fcs
Geschrieben am: 03.11.2014 06:49:17

Hello Marc,

here is my solution for the macro for the "Submit"-Button.

Best regards
Franz

'Copy Macro into Code-Modul of Sheet "Input"
Private Sub CommandButton1_Click()
    Dim rngCopy As Range, rowDest As Long
    Dim wksDest As Worksheet
    If MsgBox("Copy Input-Data to sheet ""Output""?", _
        vbQuestion + vbOKCancel, _
        "Copy Input-Data") = vbCancel Then Exit Sub
    Set wksDest = ActiveWorkbook.Worksheets("Output")
    Set rngCopy = Me.Range("B1:B3")
    
    Application.ScreenUpdating = False
    With wksDest
        rowDest = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(rowDest, 1).Value = .Application.WorksheetFunction.Max(.Range(.Cells(1, 1), _
                .Cells(rowDest, 1))) + 1
        rngCopy.Copy
        .Cells(rowDest, 2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Application.CutCopyMode = False
    End With
    Application.ScreenUpdating = True
    
    Set wksDest = Nothing: Set rngCopy = Nothing
End Sub



  

Betrifft: AW: VBA von: Marc
Geschrieben am: 03.11.2014 21:43:56

Hi Franz,

this is great. Thanks. Is it possible to also copy another columns E1:E4 to the output tab. So the range would go to two different column.

Uploaded another Test example.

Thanks,

Marc

https://www.herber.de/bbs/user/93525.xlsx


  

Betrifft: AW: VBA von: fcs
Geschrieben am: 04.11.2014 08:07:52

Hi Marc,

copying a 2nd range is no problem. You "simply" have to add another set of copy and pastespecial lines.

Best regards

Franz

'Copy Macro into Code-Modul of Sheet "Input"
Private Sub CommandButton1_Click()
    Dim rngCopy As Range, rowDest As Long
    Dim wksDest As Worksheet
    If MsgBox("Copy Input-Data to sheet ""Output""?", _
        vbQuestion + vbOKCancel, _
        "Copy Input-Data") = vbCancel Then Exit Sub
    Set wksDest = ActiveWorkbook.Worksheets("Output")
    Set rngCopy = Me.Range("B1:B3")
    
    Application.ScreenUpdating = False
    With wksDest
        rowDest = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(rowDest, 1).Value = .Application.WorksheetFunction.Max(.Range(.Cells(1, 1), _
                .Cells(rowDest, 1))) + 1
        rngCopy.Copy
        .Cells(rowDest, 2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Me.Range("E1:E3").Copy
        .Cells(rowDest, 5).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Application.CutCopyMode = False
    End With
    Application.ScreenUpdating = True
    
    Set wksDest = Nothing: Set rngCopy = Nothing
End Sub