Command Button to Move Data from One Sheet to Another
Closed
Red1985
Posts
5
Registration date
Wednesday September 7, 2016
Status
Member
Last seen
September 19, 2016
-
Sep 7, 2016 at 05:30 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Sep 19, 2016 at 09:20 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Sep 19, 2016 at 09:20 PM
Related:
- Command Button to Move Data from One Sheet to Another
- Transfer data from one excel worksheet to another automatically - Guide
- Cs 1.6 money command - Guide
- Csgo speed command - Guide
- Google sheet right to left - Guide
- Little alchemy cheat sheet - Guide
4 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Sep 7, 2016 at 05:52 AM
Sep 7, 2016 at 05:52 AM
Hello Red1985,
Could you please upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here. Please use dummy data and indicate which "status" you will be using to transfer the relevant rows of data to the Data Removed sheet before having them deleted from the main sheet.
Thanks Red.
Cheerio,
vcoolio.
Could you please upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here. Please use dummy data and indicate which "status" you will be using to transfer the relevant rows of data to the Data Removed sheet before having them deleted from the main sheet.
Thanks Red.
Cheerio,
vcoolio.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Sep 7, 2016 at 10:28 PM
Sep 7, 2016 at 10:28 PM
Hello Red,
There's nothing at the link you provided. The message "Share not Found" comes up.
Maybe try again at ge.tt and another file sharing site such as DropBox.
Cheerio,
vcoolio.
There's nothing at the link you provided. The message "Share not Found" comes up.
Maybe try again at ge.tt and another file sharing site such as DropBox.
Cheerio,
vcoolio.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Sep 8, 2016 at 05:43 AM
Sep 8, 2016 at 05:43 AM
Hello again Red,
In the meantime, while you battle with the file sharing site, have a look at this sample that I knocked up for you:-
https://www.dropbox.com/s/hu4dvsa18hkx2a2/Red1985%28Transfer%20from%20sht1%20to%20sht2%2C%20autofilter%29.xlsm?dl=0
Click on the "RUN" button to see it work.
The code filters Column Q for any cell that has "Remove" typed in it and then transfers the relevant row of data to sheet2. The "used" data is then deleted from sheet1 leaving you with the data that you wish to analyse. Following is the VBA code:-
Still upload a sample as we'll need to see how you have your sheets set up.
Cheerio,
vcoolio.
In the meantime, while you battle with the file sharing site, have a look at this sample that I knocked up for you:-
https://www.dropbox.com/s/hu4dvsa18hkx2a2/Red1985%28Transfer%20from%20sht1%20to%20sht2%2C%20autofilter%29.xlsm?dl=0
Click on the "RUN" button to see it work.
The code filters Column Q for any cell that has "Remove" typed in it and then transfers the relevant row of data to sheet2. The "used" data is then deleted from sheet1 leaving you with the data that you wish to analyse. Following is the VBA code:-
Sub TransferData()
Dim lr As Long
Application.ScreenUpdating = False
Sheet1.Range("Q1", Sheet1.Range("Q" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
If lr > 1 Then
Sheet1.Range("A2", Sheet1.Range("AC" & Sheet1.Rows.Count).End(xlUp)).Copy
Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Sheet1.Range("A2", Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp)).EntireRow.Delete
Sheet2.Columns.AutoFit
End If
Sheet1.[Q1].AutoFilter
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Still upload a sample as we'll need to see how you have your sheets set up.
Cheerio,
vcoolio.
Red1985
Posts
5
Registration date
Wednesday September 7, 2016
Status
Member
Last seen
September 19, 2016
Sep 8, 2016 at 05:50 AM
Sep 8, 2016 at 05:50 AM
Good day vcoolio,
Thank you so much. It works and I will try it with my sheets. I will send you the sample from home tonight.
Thanks for you help I appreciate it.
Thank you so much. It works and I will try it with my sheets. I will send you the sample from home tonight.
Thanks for you help I appreciate it.
Red1985
Posts
5
Registration date
Wednesday September 7, 2016
Status
Member
Last seen
September 19, 2016
Sep 19, 2016 at 07:41 AM
Sep 19, 2016 at 07:41 AM
Good day vcoolio,
I've only managed to use your sheet you sent me today. Can you please explain to me what the following line means? As I am trying to use your sheet and code to add my statuses.
Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
I have only changed the headings to my reports headings on both sheet 1 and sheet 2 and I have changed the "Remove" as the status on your report to the statuses on my report, but when I run the report, it doesn't do anything. If you could assist I would really appreciate it.
Hoping to hear from you soon.
Thank you.
I've only managed to use your sheet you sent me today. Can you please explain to me what the following line means? As I am trying to use your sheet and code to add my statuses.
Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
I have only changed the headings to my reports headings on both sheet 1 and sheet 2 and I have changed the "Remove" as the status on your report to the statuses on my report, but when I run the report, it doesn't do anything. If you could assist I would really appreciate it.
Hoping to hear from you soon.
Thank you.
Red1985
Posts
5
Registration date
Wednesday September 7, 2016
Status
Member
Last seen
September 19, 2016
Sep 19, 2016 at 07:49 AM
Sep 19, 2016 at 07:49 AM
Sorry I forgot to mention that the different statuses I have is say for example "Remove", "Duplicate", "A: Active", "L: Deposit; Less Reg Fee" So what I have done is I have changed the code from:
Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
To:
Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove, Duplicate, A: Active, L: Deposit; Less Reg Fee", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
So I take it the 1 and the 7 in the code has something to do with it that the data is not transferred.
I also tried:
Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove", "Duplicate", "A: Active", "L: Deposit; Less Reg Fee", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
But that gives me a compile error.
Thanks for the help.
Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
To:
Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove, Duplicate, A: Active, L: Deposit; Less Reg Fee", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
So I take it the 1 and the 7 in the code has something to do with it that the data is not transferred.
I also tried:
Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, "Remove", "Duplicate", "A: Active", "L: Deposit; Less Reg Fee", 7
lr = Sheet1.Range("Q" & Rows.Count).End(xlUp).Row
But that gives me a compile error.
Thanks for the help.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Sep 19, 2016 at 09:20 PM
Sep 19, 2016 at 09:20 PM
Hello Red,
Lines 7 & 8 in the code above are actually one line of code. It appears as two lines just so it can fit into the dialogue box.
Basically, these lines of code are telling Excel to place a filter on the entire Column Q and filter for the criteria "Remove". The number 1 means that the filter is filtering just the one column (Q). The number 7 is just an abbreviation for "xlFilterValues".
Line 9 basically is telling excel to find the last used row of data. It is telling Excel to go to the bottom of the sheet and count the rows all the way back up until the last used row of data. Its done this way as the rows of data will always vary.
The method that you have tried does not work because using this type of code (autofilter) will only allow you to use two criteria at once. Hence, this is why I don't understand what you are trying to do. Your first post states that you are wanting to transfer any row of data that has the status "Remove" in Column Q to the second sheet with the relevant row of data deleted from the first sheet which is what happens in the sample that I supplied (this, at least, is how I have interpreted your first post).
However, do you actually mean that various statuses and their relevant rows of data may need to be transferred/deleted as required? If so, the simplest method of doing this would be to add a "Criteria" column (say Column AD) and then, in the relevant cell, type in the criteria "Remove" to have the relevant row of data transferred/deleted once the button is clicked. You would only have to change any "Q" to "AD" in the code above to have this work.
I hope that this helps.
Cheerio,
vcoolio.
Lines 7 & 8 in the code above are actually one line of code. It appears as two lines just so it can fit into the dialogue box.
Basically, these lines of code are telling Excel to place a filter on the entire Column Q and filter for the criteria "Remove". The number 1 means that the filter is filtering just the one column (Q). The number 7 is just an abbreviation for "xlFilterValues".
Line 9 basically is telling excel to find the last used row of data. It is telling Excel to go to the bottom of the sheet and count the rows all the way back up until the last used row of data. Its done this way as the rows of data will always vary.
The method that you have tried does not work because using this type of code (autofilter) will only allow you to use two criteria at once. Hence, this is why I don't understand what you are trying to do. Your first post states that you are wanting to transfer any row of data that has the status "Remove" in Column Q to the second sheet with the relevant row of data deleted from the first sheet which is what happens in the sample that I supplied (this, at least, is how I have interpreted your first post).
However, do you actually mean that various statuses and their relevant rows of data may need to be transferred/deleted as required? If so, the simplest method of doing this would be to add a "Criteria" column (say Column AD) and then, in the relevant cell, type in the criteria "Remove" to have the relevant row of data transferred/deleted once the button is clicked. You would only have to change any "Q" to "AD" in the code above to have this work.
I hope that this helps.
Cheerio,
vcoolio.
Sep 7, 2016 at 07:46 AM
I have replied to your answer regarding my sample of the workbook. I am not sure if you have received it. I have added notes on the remove tab on the sample workbook as to what "Status" must be removed. Here is the link for the file http://ge.tt/9JuBF8e2 My apologies if you have received it more than once.
Thanking you in advance.