VBA question

Yuenja - Feb 21, 2018 at 01:26 PM - Latest reply: ac3mark 10260 Posts Monday June 3, 2013Registration dateModeratorStatus August 15, 2018 Last seen
- Feb 21, 2018 at 05:02 PM
Hi Trowa (experienced VBA help community),

Sorry if my earlier posted question was not clear.

Here is the situation:
Sheet" Initial": A (FirmID) , B(FirmName) , C (prod ID), D (Prod Name), E, F..(Status).....K(FirstName) M(email)....
Sheet "Deactivate": A, B, C, D, E, F....K...M .same type of info in each column ( but not values) as Initial sheet

Match values in Column C (prod ID) on both sheets
Deselect from the matched value in column C of Sheet "Deactivate" any row that has "Y-C" in column F(Status) on Sheet Deactivate .

So in Sheet "Deactivate" now, the newly matched and filtered rows should only have values that match values in column C and has eliminated "Y-C" in column F.

Then copy from Sheet "Deactivate" based on the new screened and matched info, columns M (email), K(FirstName),M (email) ,B Firm Name) ,D (Prod Name), C (Prod ID) into new sheet (Sheet2) in that order(that is Column M start in column 1 of the Sheet2 and etc).

On the newly formed Sheet2, turn information in column C (Prod ID)which is now the last column on the new Sheet2 into a delimited value row onto Sheet3.

So if column c has:
123
456
789
then Column C will show up as 123,4546,789 in Sheet 3 as a row of information
For this part, I got the delimited script for column C

Sub generatecsv()
Dim i As Integer
Dim s As String

i = 1

Do Until Cells(i, 1).Value = ""
If (s = "") Then
s = Cells(i, 1).Value
Else
s = s & "," & Cells(i, 1).Value
End If
i = i + 1
Loop

Cells(1, 2).Value = s

End Sub

Many thanks for your help in advance again!!! And I hope I explained the situation to you more clearly this time.
See more 

Your reply

1 reply

ac3mark 10260 Posts Monday June 3, 2013Registration dateModeratorStatus August 15, 2018 Last seen - Feb 21, 2018 at 05:02 PM
0
Thank you
First off, in your loop, you really need to define what sheet you are expecting to write to, as if the user clicks on another tab while the loop is running, it will be the active sheet. So, I guess the cut and paste from another solution still needs to be refined.

If you take a look at this:
http://ccm.net/faq/54862-how-to-copy-data-from-one-excel-sheet-to-another-using-a-formula

You will find code that will do exactly what you are asking. Let Trowa know if you need help! :)
Respond to ac3mark