Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy between sheets
I have a macro that when the user delete the content of any value between the
range of B10:B500 do the following things. 1.Message box asking if they are sure to delete. 2.If yes is selected, a. Create one last new row on sheet2. b. Cut the name to be deleted from Sheet1. c. Paste the name deleted on the (column F, last new row on sheet2). d. Exit from macro. 3. If No is selected, exit from macro. My macro code is below but I feel that I missing something or I'm doing something wrong: Private Sub Exits() Dim rng As Range Dim lasrow As Cell Set rng = Sheets("Headcount as of April-2007").Range("B10:B500") If rng Is Delete Then Answer = MsgBox(Prompt:="Delete this name?", Buttons:=vbYesNo + vbQuestions) If Answer = vbYes Then lastrow = Sheets("EXIT - 2007").UsedRange.Rows.Count Rows(lastrow).Select Selection.Insert Shift:=xlDown Cells(lastrow - 1, 1).Select Range(ActiveCell, ActiveCell.Offset(0, lastcolumn)).Select Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(1, lastcolumn)), Type:=xlFillDefault With rng .Cut .Paste Destination:=Sheets("EXIT - 2007").Range(6, Rows(lastrow)) End With End If End Sub Any help will be appreciated and thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy between sheets
try this
Sub Exits() Dim rng As Range Dim lasrow As Cell Set rng = Sheets("Headcount as of April-2007").Range("B10:B500") If rng Is Delete Then If MsgBox("Delete this name?", vbYesNoCancel) < vbYes Then Exit Sub End If lastrow = Sheets("EXIT - 2007").UsedRange.Rows.Count Rows(lastrow).Select Selection.Insert Shift:=xlDown Cells(lastrow - 1, 1).Select Range(ActiveCell, ActiveCell.Offset(0, lastcolumn)).Select Selection.AutoFill Destination:=Range(ActiveCell, _ ActiveCell.Offset(1, lastcolumn)), Type:=xlFillDefault With rng .Cut .Paste Destination:=Sheets("EXIT - 2007").Range(6, Rows(lastrow)) End With End If End Sub "abdrums" wrote: I have a macro that when the user delete the content of any value between the range of B10:B500 do the following things. 1.Message box asking if they are sure to delete. 2.If yes is selected, a. Create one last new row on sheet2. b. Cut the name to be deleted from Sheet1. c. Paste the name deleted on the (column F, last new row on sheet2). d. Exit from macro. 3. If No is selected, exit from macro. My macro code is below but I feel that I missing something or I'm doing something wrong: Private Sub Exits() Dim rng As Range Dim lasrow As Cell Set rng = Sheets("Headcount as of April-2007").Range("B10:B500") If rng Is Delete Then Answer = MsgBox(Prompt:="Delete this name?", Buttons:=vbYesNo + vbQuestions) If Answer = vbYes Then lastrow = Sheets("EXIT - 2007").UsedRange.Rows.Count Rows(lastrow).Select Selection.Insert Shift:=xlDown Cells(lastrow - 1, 1).Select Range(ActiveCell, ActiveCell.Offset(0, lastcolumn)).Select Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(1, lastcolumn)), Type:=xlFillDefault With rng .Cut .Paste Destination:=Sheets("EXIT - 2007").Range(6, Rows(lastrow)) End With End If End Sub Any help will be appreciated and thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
Set sem__ = Sheets("template").Copy(befo=Sheets(1)) | Excel Programming | |||
Create New WB with copy of Sheets 1 & 2 when sheets count reaches 100 | Excel Programming | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions |