excel - create a button to copy data to new rows...

[inetquestion]

I'm not sure what to call what it is i'm looking for, but can describe the desired result.. I've got an excel spreadsheet with values across the top row, and months down the first column. IE:


0 | 100 250 500 300 4525
--------------------------------------------
Jan|
FEB|
MAR|
APR|
MAY|
...|
...|
...|

The sheet is used to track amounts invested over the course of a year, and the data on the first row is the suggested amount for each month. So after several months, the sheet would look like this:

0 | 100 250 500 300 4525
--------------------------------------------
Jan| 100 250 500 300 4525
FEB| 100 250 500 300 4525
MAR| 100 250 500 300 4525
APR| 100 250 500 300 4525
MAY| 100 250 500 300 4525
...| 100 250 500 300 4525
...| 100 250 500 300 4525
...| 100 250 500 300 4525

I would like a button to invoke beside each month that would copy the default suggested amounts to that month. Or a single button that would ask me what month to copy the default values to. Any suggestions? I'm completely new to any sort of ms application programming so the more detailed the better.

TIA,

Inet

 

[VBAjedi]

Here's an easy way to do it without a button. This code I just put together will insert the default values if you double-click any cell in the "A" column (my example watches rows 2 through 10000, but you can obviously adjust that to suit your needs).

Select the default amounts range (in your example B1:F1) and name it "DefaultAmounts". Then put the following into the Sheet's code area:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim C As Range
Set C = Intersect(Target.Cells(1, 1), Range("A2:A10000"))
If Not C Is Nothing Then
   Range("DefaultAmounts").Copy C.Offset(0, 1)
End If
Cancel = True ' cancels default double-click behavior
End Sub

Let me know if that does the trick for you!

VBAjedi

 

[inetquestion]

I'm unsure how to do this:

Select the default amounts range (in your example B1:F1) and name it "DefaultAmounts".


I was assuming you meant insert the value of defaultamounts in cell A1, but this doens't appear to do it. The error I'm getting is:

Run-Time error: '1004'
Method 'Range' of object '_Worksheet' failed

 

[inetquestion]

Ok....I'm learning, but still have a few problems. Now I'm trying to get it to only paste the values of (b1:f1) instead of the borders and formatting. I've been looking at the pastespecial function, but have no idea what the heck is going wrong.

Destination:=C.Offset(0, 1).PasteSpecial(xlPasteValues, xlPasteSpecialOperationNone, False, False)


Changed code to:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim C As Range
Set C = Intersect(Target.Cells(1, 1), Range("A2:A10000"))
If Not C Is Nothing Then
Range("B1:F1").Copy _
Destination:=C.Offset(0, 1)

End If
Cancel = True ' cancels default double-click behavior
End Sub

 

[inetquestion]

Finally figured this one out... Apparently the carrage return after ".copy" is very important. I have no idea why you can't combine this all on one line as in your initial example (which worked) but you can't.

In any regard thanks for your help; it's very much appreciated! To anyone else who is interested...here is the finished product:



************************************************
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim C As Range
Set C = Intersect(Target.Cells(1, 1), Range("A4:A15"))
If Not C Is Nothing Then

Range("B2:Z2").Copy
C.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Range("AC2").Copy
C.Offset(0, 28).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End If
Cancel = True ' cancels default double-click behavior
End Sub

 

[inetquestion]

What would be involved to only perform the copy/paste if the destination cells were all empty?

TIA

-Inet