Copy selected cells to end of row in another worksheet
I am trying to figure out the code to copy selected cells on a worksheet and then paste the selected cells to the last row in another worksheet in the same workbook. The worksheet that I am copying from will have an employees name, such as Mary, and the selected cells will be paste to a worksheet called Current Projects. I was able to get the paste part to work, but it is only copying the first cell on the employee worksheet & pasting the same information for all the cells. Any help would be greatly appreciated.
Welcome to the Forum. It would be good if you attach a sample workbook along with the code you are using so we can help you better.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Here is a copy of the Excel workbook.
Would you/user select the cells which need to be copied? Or should the macro select certain cells based on a condition?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
the user would select the cells that need to be copied
All you need is 1 line of code -
Sub copy_range() Selection.EntireRow.copy Worksheets("Current Projects").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End Sub
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
This works! Thanks so much. Of course, thinks have change & I know need to have the line that is being copied deleted once it is moved to the current projects tab. Any suggestions?
You have 2 options in hand -
1. Use cut paste and not copy paste. This will give you blanks that you need to go back to the sheet and delete. OR
2. Use cut paste and then sort the sheet where you cut from to get rid of the blanks.
If you choose option 1 - your code will be -Code for option 2 is -Selection.EntireRow.cut Worksheets("Current Projects").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) selection.entireRow.deleteSelection.EntireRow.Cut Worksheets("Current Projects").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) ActiveWorkbook.Worksheets("XXX").Sort.SortFields.Clear -> Replace XXX with your sheet name ActiveWorkbook.Worksheets("XXX").Sort.SortFields.Add Key:=Range("A:A") _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("XXX").Sort .SetRange Range("A:G") -> change the column references as required .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks