Overwrite - Vb code with macro
- Thread starter ganeshm
- Start date
Ganeshm
Control Button's are normally linked to a cell and so that cell changes when the button is Pressed True or not Pressed False
You can also link the button to some VBA code which will activate when pressed
To overwrite or place a value in a cell it is as simple as
[pre]
[/pre]
What are you trying to do?
Control Button's are normally linked to a cell and so that cell changes when the button is Pressed True or not Pressed False
You can also link the button to some VBA code which will activate when pressed
To overwrite or place a value in a cell it is as simple as
[pre]
Code:
Sub Button_Pressed()
Worksheets("My sheet").Range("A1").value = 1234
' or
' Worksheets("My sheet").Range("A1").value = my formula
End SubWhat are you trying to do?
hi hui,
i have designed an excel sheet to retrieve the values from sheet 2 to sheet 1 and then overwrite if there are any changes.
retrieval code is working as desired. But when i save and transfer values to sheet 2 for any changes, it doesn't work.
Note:
Sheet 1 - the values start from A5 to D12.
Sheet 2 - the values (transferred from Sheet 1), starts from A2:D20.
The following is the formula:
Sub Save()
Dim LastRow As Integer
Dim InputCell As Range
Dim OutputCell As Range
'Where is the last cell with data?
LastRow = Worksheets("Sheet2").Range("A65536").End(xlUp).Row
'Transfer data
Sheets("Sheet1").Range("A5:D12").Value = Worksheets("Sheet2").Range("A5:D12").Cells(LastRow + 1, "A")
With Worksheets("Sheet1").Activate
Range("A5").Select
End With
End Sub
i have designed an excel sheet to retrieve the values from sheet 2 to sheet 1 and then overwrite if there are any changes.
retrieval code is working as desired. But when i save and transfer values to sheet 2 for any changes, it doesn't work.
Note:
Sheet 1 - the values start from A5 to D12.
Sheet 2 - the values (transferred from Sheet 1), starts from A2:D20.
The following is the formula:
Sub Save()
Dim LastRow As Integer
Dim InputCell As Range
Dim OutputCell As Range
'Where is the last cell with data?
LastRow = Worksheets("Sheet2").Range("A65536").End(xlUp).Row
'Transfer data
Sheets("Sheet1").Range("A5:D12").Value = Worksheets("Sheet2").Range("A5:D12").Cells(LastRow + 1, "A")
With Worksheets("Sheet1").Activate
Range("A5").Select
End With
End Sub
Ganeshm
I've never seen Cells(LastRow + 1, "A") used like that
I suspect it should be Cells(LastRow + 1, 1)
I'm also not sure that Worksheets("Sheet2").Range("A5:D12").Cells(LastRow + 1, "A")
is correct
Why specify the range as Range("A5:D12") and then modify that with cells()
what is that trying to do
I'm also not sure how 18 rows can fit into 7 rows as per your notes
Sheet 1 - the values start from A5 to D12.
Sheet 2 - the values (transferred from Sheet 1), starts from A2:D20.
Also you will have to manually run the code each time there is a change
Can you specify what your trying to do with an example file possibly
refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
I've never seen Cells(LastRow + 1, "A") used like that
I suspect it should be Cells(LastRow + 1, 1)
I'm also not sure that Worksheets("Sheet2").Range("A5:D12").Cells(LastRow + 1, "A")
is correct
Why specify the range as Range("A5:D12") and then modify that with cells()
what is that trying to do
I'm also not sure how 18 rows can fit into 7 rows as per your notes
Sheet 1 - the values start from A5 to D12.
Sheet 2 - the values (transferred from Sheet 1), starts from A2:D20.
Also you will have to manually run the code each time there is a change
Can you specify what your trying to do with an example file possibly
refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
SirJB7
Excel Rōnin
@Hui
Hi!
I agree regarding "Cells(LastRow + 1, "A")", I've never seen that, and I'd sweared it didn't work... just until five minutes ago when I downloaded ganeshm's file and run macro with F8 (the second time when I didn't believe it worked the first time and it didn't raise any error).
Regards!
Hi!
I agree regarding "Cells(LastRow + 1, "A")", I've never seen that, and I'd sweared it didn't work... just until five minutes ago when I downloaded ganeshm's file and run macro with F8 (the second time when I didn't believe it worked the first time and it didn't raise any error).
Regards!