Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1388to1392
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA

VBA
03.11.2014 05:37:07
Marc
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

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA
03.11.2014 06:49:17
fcs
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

Anzeige
AW: VBA
03.11.2014 21:43:56
Marc
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

AW: VBA
04.11.2014 08:07:52
fcs
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

Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige