Move data from one wrksheet to another using submit button

[Closed]
Report
Posts
1
Registration date
Friday February 27, 2015
Status
Member
Last seen
February 28, 2015
-
Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
-
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

2 replies

Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
238
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.
Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
238
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.