I have this code:
Sub Button26_Click()
Dim s1, s2
Set s1 = Worksheets("Invoice Generator")
Set s2 = Worksheets("Past Invoices")
With s2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
.Cells(, "a").Value = s1.Range("f27").Value
End With
End Sub
To copy the cell f27 (initially) from sheet one to sheet two (on a new row each time) when the button is clicked. However, if I change "a" to any other cell reference then the data only gets copied once - subsequent clicks do not work.
Anyone have any ideas? Thanks.
You are setting the position by starting at the bottom of column A and looking up to the first non-blank cell then offsetting down one row to a blank cell. If you take this position but stuff values into the row starting at column B, you never fill that empty cell in column A so subsequent calls to the same routine will reposition to exactly the same spot. If you are going to use column B as the target, you need to position the new transfer of values based on the first blank cell in columns B, not column A.
Dim targetCOL as long
targetCOL = 2
With s2.Cells(Rows.Count, targetCOL).End(xlUp).Offset(1, 0).EntireRow
.Cells(, targetCOL).Value = s1.Range("f27").Value
End With
Since you have to change the column of both the positioning method and the target of the values it makes sense to put either the alphabetic columns reference or the numerical column index into a variable so that changing it there will change it in both required places. In the above suggested modification, I've used the column's numerical index to set column B.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加