One button, Multi Cells

  • Thread starter Thread starter Axanamoon
  • Start date Start date
A

Axanamoon

I'm somewhat new to VBA and I sure could use some help. I'm trying to put a
command button in Excel (2003) on sheet1 that when clicked will select 4
certain cells (a3, b5, c8 and b11) and paste them onto the next blank row on
sheet2 in that order. Can this be done without setting up 4 different
buttons? Any help anyone could give would be so appreciated. Thanks.
 
Can you be a bit more specific on where you want to paste them on Sheet2?
What column/s do you want them in? You have specified cells a3, b5, c8 and
b11 to copy. 2 of these are in column B so I am assuming you don't want them
in the corresponding columns on Sheet2. Perhaps you want them one under the
other in the same column; but which column?
 
What I need is for those particular cells go into the next blank ROW on
sheet2 filling in columns a b c d left to right. My boss wants me to keep a
log of voids and since my void form is in excel I thought it would be easier
if I could get a button that would automatically take the information from
cells a3 b5, c8 and b11 and put it the log rather than me having to copy and
paste. I can get it to do this one by one but I was hoping to get a single
click to do it. Do you think it can be done?
 
If you have column headers on the destination sheet the data will be copied
on to the next row each time you run the code. If no column headers then a
blank row will be left at the top of the sheet but after that the data will
all be on the next available row.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Private Sub CommandButton1_Click()
Dim rngDest As Range

'Edit "Sheet2" to your destination sheet name
With Sheets("Sheet2")
Set rngDest = .Cells(.Rows.Count, "A") _
.End(xlUp).Offset(1, 0)
End With

With ActiveSheet
.Range("A3").Copy _
Destination:=rngDest

.Range("B5").Copy _
Destination:=rngDest.Offset(0, 1)

.Range("C8").Copy _
Destination:=rngDest.Offset(0, 2)

.Range("B11").Copy _
Destination:=rngDest.Offset(0, 3)
End With
End Sub
 
Back
Top