Move data from one wrksheet to another using submit button

Closed
all4love Posts 1 Registration date Friday February 27, 2015 Status Member Last seen February 28, 2015 - Feb 27, 2015 at 11:29 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 1, 2015 at 03:23 AM
hello im working on a project and ive create a workbook in which I place data into a worksheet known as "processing" which serves as a main page in were I process detailed information from column A-L...i also have I another worksheet in this workbook label (master) and this is copy of everything that's ever been processed from my "processing" sheet..my goal is to create a macro which move the data from "processing" to "master" thru a submit button and then clear the data from "processing" so that I may start a fresh new batch but yet still hold history in the master sheet!!ive tried various methods but all clear my data on master..any excel guru out there can lend me some advise or help me write a macro that can preform this takes..thanks
Related:

2 responses

vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Feb 28, 2015 at 01:28 AM
Hello All4love,

As a starting point, this code should do what you ask:-

Sub CopyData()
Dim ws As Worksheet
Dim ws1 As Worksheet
Set ws = Worksheets("Processing")
Set ws1 = Worksheets("Master")

ws.UsedRange.Offset(2, 0).Copy ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
ws.UsedRange.Offset(2, 0).ClearContents
ws1.Range("A1:L" & Rows.Count).RemoveDuplicates Columns:=1, Header:=xlYes
ws1.Select
End Sub


The code transfers all the data from the "Processing" sheet to the "Master" sheet. I'm not sure if you want it all transferred at once or just certain rows at a time. Anyway, see how it works for you and, if need be, we can refine it for you.

The code is based on data starting in Row 3.

If its not quite what you need, then please upload a sample of your work book using a file sharing site such as Dropbox or ge.tt so that we can see exactly what you need.

Here is my test work book for you to play with:-

https://www.dropbox.com/s/5gdwxjmrh5i9yjz/All4love.xlsm?dl=0

Cheers,
vcoolio.
0
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Mar 1, 2015 at 03:23 AM
Hello,(vcoolio) that was amazing thank for the prompt response ..i used the macro you provided and although it did transfer the data it wipes out my formulas I forgot to mention that I only want the value to be transfer I still need the formula that I have to remain in tact..in addition I have other column that are important data that cant be eliminated..this macro clear the entire wrksheet once transferred!!! so essentially I need from worksheet ("process sheet) column a-L and row 2-1000 to be transfer (value only) to ("employee history") as raw data....thank you so so much for your help ...i will drop a link to my dropbox with demo workbook.......also if you could share with me a possible work around were my content from (process sheet) column c2-c1000 are not being duplicated into d2-d1000 more then once...in other words is I have XYZ more then once it wont duplicate it in row (D) ONLY BUT ONCE..i need it to duplicate all duplicates from row C to row D....thanks in advance..:))))))) dropbox link below 

https://www.dropbox.com/s/obudwtjfn79mnqy/updatefinal%20demo%20master%20list.xlsm?dl=0


Hello All4love,

I've moved your new post to here (quoted above) and deleted the new thread as we need to keep everything together for each query for the sake of other users' ease of understanding of the entire issue from start to finish. If you have any further replies, please use the "Reply" button at the bottom of this thread.
Now, back to the task:-

The following new code should do as you want and keep all the formulae intact in the "Process Sheet":-

Sub TransferData()

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim ws1 As Worksheet
Set ws = Worksheets("Process Sheet")
Set ws1 = Worksheets("Employee History")
lRow = Range("A" & Rows.Count).End(xlUp).Row

ws.Range("A2:L" & lRow).Copy
ws1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ws.Cells.SpecialCells(xlCellTypeConstants, 23).Offset(1, 0).ClearContents
ws1.Columns.AutoFit
ws1.Select
Application.ScreenUpdating = True
End Sub


Here is your updated sample work book:-

https://www.dropbox.com/s/zx3jb3cklj5k7l7/All4love%282%29.xlsm?dl=0

You are going to have to elaborate a little more on this part of your query:-

"...also if you could share with me a possible work around were my content from (process sheet) column c2-c1000 are not being duplicated into d2-d1000 more then once...in other words is I have XYZ more then once it wont duplicate it in row (D) ONLY BUT ONCE..i need it to duplicate all duplicates from row C to row D....thanks in advance..:)))))))"


as I don't exactly follow.

Keep us in touch.

Kind regards,
vcoolio.
0