Upinder Singh
asked on
CUT & PASTE VALUES IN EXCEL USING MACRO -- SPECIFIC CELLS
Hello,
I need help to build a macro that will allow to cut a value from the selected active cell in active worksheet (ie "sheet1") to another worksheet (ie. "sheet2") but paste in specific cell? In other words, want this to be done with button click, meaning I select the cell from the current worksheet "sheet1" and click button to paste cut value in "sheet2" but in a specified cell.
Thanks!
I need help to build a macro that will allow to cut a value from the selected active cell in active worksheet (ie "sheet1") to another worksheet (ie. "sheet2") but paste in specific cell? In other words, want this to be done with button click, meaning I select the cell from the current worksheet "sheet1" and click button to paste cut value in "sheet2" but in a specified cell.
Thanks!
After rereading your question I changed the code I posted.
test this
Sub cutandpaste()
ActiveCell.Cut Sheets(2).Range("A1")
End Sub
Sub cutandpaste()
ActiveCell.Cut Sheets(2).Range("A1")
End Sub
ASKER
Thanks for your feedback. So this code will cut the value in active cell and paste only to cell "A1." Is it possible to define the destination cell so that first pasted value is A1, then second time the pasted value will fall in A2, and so on?
test this codes
Sub cutandpaste()
Dim actCell As String, destCell As String
Dim iRow As Integer
iRow = 1
actCell = ActiveCell.Address
Sheets(2).Activate
Cells(iRow, 1).Activate
Do Until ActiveCell.Value = ""
iRow = iRow + 1
ActiveCell.Offset(1, 0).Activate
Loop
Sheets(1).Activate
Range(actCell).Activate
ActiveCell.Cut Sheets(2).Range("A" & iRow)
End SubASKER
Very creative. with this solution you the second pasted value is entered below the first value, third value below the second. Instead of same column, can we make it so that values are entered in same row but next following cell?
Thanks!
Thanks!
So are you saying you want the pasted values to go in order into A1, B1, C1, etc.?
Better yet, please provide a sample workbook showing "before" and "after" sheet.
Sub cutandpaste2()
Dim actCell As String, destCell As String
Dim iCol As Integer
iCol = 1
actCell = ActiveCell.Address
Sheets(2).Activate
Cells(1, iCol).Activate
Do Until ActiveCell.Value = ""
iCol = iCol + 1
ActiveCell.Offset(0, 1).Activate
Loop
Sheets(1).Activate
Range(actCell).Activate
ActiveCell.Cut Sheets(2).Cells(1, iCol)
End SubASKER
hope this will help...please see attachment
before-after.xlsx
before-after.xlsx
You want to move Column D, E, F, G to J, K, L, M
You mentioned same rows, but whats the row number? From which row to which row?
You mentioned same rows, but whats the row number? From which row to which row?
ASKER
Cells D,E,F,J from the "Before" tab to cells J,K,L,M in the "After" tab... please note that the pasted values should go to "After" tab.
So for example, a value enterd in D17 in "Before" tab should go to J17 in "After" tab (the difference between coumn J & D is 6).
So for example, a value enterd in D17 in "Before" tab should go to J17 in "After" tab (the difference between coumn J & D is 6).
You are saying D17 & J17 are fixed like for all columns E, F, G as well?
Please attached the formatted sheet. in your attachments, its a picture.
@Upinder Singh,
how many times are you going to change the conditions?
how many times are you going to change the conditions?
ASKER
the example in the file should help exactly with what I am looking to do....
cells in row 11 in column D,e,f,g in "Before" tab need to be cut-pasted in row 11 but in column J, K, L, M
cells in row 11 in column D,e,f,g in "Before" tab need to be cut-pasted in row 11 but in column J, K, L, M
ASKER
File uploaded
TEST-FILE.xlsx
TEST-FILE.xlsx
Sub CutNPaste()
Sheets("BEFORE").Select
Range("D11:G11").Select
Selection.Cut
Sheets("AFTER").Select
Range("J11").Select
ActiveSheet.Paste
End Sub
Sheets("BEFORE").Select
Range("D11:G11").Select
Selection.Cut
Sheets("AFTER").Select
Range("J11").Select
ActiveSheet.Paste
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is awesome....good stuff!
Just one small ...last thing to ask... I want to to be able to press the button and stay on the "Before" page. How can I do this?
Just one small ...last thing to ask... I want to to be able to press the button and stay on the "Before" page. How can I do this?
I did sent your sheet, please check, there is button
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you everyone.....really mean this, amazing stuff here!
' Moves the active cell to A1 on Sheet2
Selection.Cut Destination:=sheets("Sheet
End Sub