Previous Topic Next Topic

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 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Sep 19, 2016 at 09:20 PM
Hello,

I have a report with a data sheet and a data removed sheet. The data sheet is from column A:AC. In column Q I have different statuses that I must base the figures on for the report. There are some statuses that has to be removed so I would like to move the ones that has to be removed from the data sheet to the Data Removed sheet, but at the same time delete it from the data sheet using a command button.

I am new to macros and would like to know how to do that, if you could please assist me.

Thanking you in advance for your assistance.

Related:

4 responses

Answer 1 / 4
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
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.
0
Red1985 Posts 5 Registration date Wednesday September 7, 2016 Status Member Last seen September 19, 2016
Sep 7, 2016 at 07:46 AM
Good day vcoolio,

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.
0
Answer 2 / 4
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
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.
0
Answer 3 / 4
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
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:- 


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.
0
Red1985 Posts 5 Registration date Wednesday September 7, 2016 Status Member Last seen September 19, 2016
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.
0
Red1985 Posts 5 Registration date Wednesday September 7, 2016 Status Member Last seen September 19, 2016
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.
0
Red1985 Posts 5 Registration date Wednesday September 7, 2016 Status Member Last seen September 19, 2016
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.
0
Answer 4 / 4
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
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.
0

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response