Aug 30 2022 08:56 AM
Good morning,
I have been trying to figure this out for several hours over the past 2 day and I am not too sure if what I need done, can be done.
I have a very large Excel workbook w/ several tabs. The main tab consists of roughly 4,000 rows and 50ish columns. Now in my industry we import food in from all over the world. So this excel sheet consists of all the active items we currently have in SAGE. When we do not want to carry a product, I set it to "DNO" (Do not order), so procurement knows not to order it. Now I would like to add a "DNO" tab and as soon as I update my main tab, I would like the information to automatically appear into the "DNO" tab without me having to manually add it each time. So ideally, I would like to have a blank tab and every time I set something to "DNO" on the main tab, it will automatically appear on the blank "DNO" tab. This way, I just have an accurate and on-going list of our "DNO'd" items only.
Hope this makes sense and someone will be able to help me.
Thank you so much in advance.
Steph
Aug 30 2022 10:25 AM
SolutionSub DNO()
Dim i As Long
Dim k As Long
Dim l As Long
l = Range("A" & Rows.Count).End(xlUp).Row
k = 2
Worksheets("DNO").Range("A2:AX1048576").Clear
For i = 2 To l
Select Case Cells(i, 51).Value
Case Is = "DNO"
Range(Cells(i, 1), Cells(i, 50)).Copy Destination:=Worksheets("DNO").Cells(k, 1)
k = k + 1
End Select
Next i
End Sub
Maybe with this code. In the attached file you can click the button in cell BA2 to run the code. In column AY you can enter "DNO" to copy the row to the designated sheet "DNO". I've entered random numbers in columns A to AX for this example.
Aug 30 2022 10:54 AM - edited Aug 30 2022 11:04 AM
OMG, thank you so much for working on this. You are obviously way smarter than I am, as i was hoping this was going to be a simple, or not so simple formula. This being said, I know zero about coding or anything remotely close to it but I am extremely advanced when is comes to formulas. , I did open the file you kindly made for me, but not too sure what to do with it or even what to do with the code you wrote for me in the above answer. When i clicked on the blue button in BA2 its a non-clickable button.
I feel bad and i'm so sorry to be difficult.
Thank you,
Steph
Aug 30 2022 11:31 AM
If there is a warning when you open the file you have to enable the content in order to run the macro. You would have to click the button "enable content" ("Inhalt aktivieren" in german) in this case. Then the button in cell BA2 should work.
I've tried to make an example according to your requirement. In the main sheet there are 50 columns and 200 rows of random data. You can add as many rows as required. Assume that the data in row 10 is "DNO" data. You can enter "DNO" in cell AY10 and click the button in cell BA2. The macro then clears all the data in sheet "DNO" and copies the data from row 10 of the main sheet to the "DNO" sheet. You don't have to change / adapt the code for this. Of course you can copy as many rows as you want at the same time.
Aug 30 2022 12:21 PM
Aug 30 2022 12:32 PM
This is a screenshot of me copying and pasting it into the macro screen. When I run it, it doesn't work. Is there something else that I need to do?
Aug 30 2022 12:52 PM
Besides the missing "Option explicit" i can't tell why it doesn't work.
Does the code return an error message if you run it (with F5)? If there isn't an error message then the code runs until the end. Do you run the macro when the main sheet is selected and did you enter "DNO" in any cells in column AY? If so the respective rows should be copied to the "DNO" sheet.
If this still doesn't work maybe you want to attach your file without sensitive data.
Aug 31 2022 06:06 AM
Aug 30 2022 10:25 AM
SolutionSub DNO()
Dim i As Long
Dim k As Long
Dim l As Long
l = Range("A" & Rows.Count).End(xlUp).Row
k = 2
Worksheets("DNO").Range("A2:AX1048576").Clear
For i = 2 To l
Select Case Cells(i, 51).Value
Case Is = "DNO"
Range(Cells(i, 1), Cells(i, 50)).Copy Destination:=Worksheets("DNO").Cells(k, 1)
k = k + 1
End Select
Next i
End Sub
Maybe with this code. In the attached file you can click the button in cell BA2 to run the code. In column AY you can enter "DNO" to copy the row to the designated sheet "DNO". I've entered random numbers in columns A to AX for this example.