Help with VBA code to copy & paste text that meet criteria to another sheet

[Azza87]

Hello all,

I am a newbie with VBA and macros in excel. I am trying to copy and paste text from one worksheet to another if it matches a certain criteria.

Ideally, I would like to copy All the text in Column B, C, and D from Worksheet 1 and paste them in Worksheet 2 based on the Company name. Worksheet 2 would ultimately be the Master Sheet which feeds from several excel files that look like Worksheet 1.


Worksheet 1


Worksheet 2

One way I tried is to add a button, which, once is clicked, would allow me to update the list of action, responsibility and time frames if any changes were made to Worksheet 1.

I am finding alot of scripts that involve copying numbers, but not so much about copying text.

Your help would be very appreciated.

Thanks,

Azza

 

[YasserKhalil]

Hello
It is better to upload sample of your file with some expected output .. No one would like to work on snapshots

 

[Marc L]

Hi,

easy with advanced filters …

• Introduction to Excel Advanced Filters
• Extract data using Advanced Filter and VBA
​

 

[Azza87]

I have attached a sample of the worksheet and expected output.

hope this helps.

 

[YasserKhalil]

Try this code (Mr. Marc would provide concise version)

Sub Test()
    Dim ws          As Worksheet
    Dim sh          As Worksheet
    Dim dic        As Object
    Dim ky          As Variant
    Dim c          As Variant
    Dim cel        As Range
    Dim x          As String

    Application.ScreenUpdating = False
        Set ws = ThisWorkbook.Worksheets("Sheet1")
        Set sh = ThisWorkbook.Worksheets("Sheet2")
        Set dic = CreateObject("Scripting.Dictionary")
   
        With ws
            .AutoFilterMode = False
            On Error Resume Next
                For Each cel In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
                    x = CStr(cel.Value)
                    If x <> "" Then dic.Add x, x
                Next cel
            On Error GoTo 0
   
            For Each ky In dic.keys
                c = Application.Match(ky, sh.Rows(5), 0)
                If Not IsError(c) Then
                    With .Range("A1").CurrentRegion
                        .Parent.AutoFilterMode = False
                        .AutoFilter 1, ky
                        .Offset(1, 1).Resize(.Rows.Count - 1, 3).Copy
                        sh.Cells(7, c).PasteSpecial xlPasteValues
                    End With
                End If
            Next ky
            .AutoFilterMode = False
        End With
       
        Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[vletm]

Azza87
... or something like this?
Press [ Do It ]
ouch ... You sent Your sample file too ... hmm?

 

[Azza87]

Thank you YasserKhalil and vletm. This was very helpful

 

[YasserKhalil]

You're welcome Azza. Glad we can help you out

 

[Azza87]

Azza87
... or something like this?
Press [ Do It ]
ouch ... You sent Your sample file too ... hmm?

Hey vletm,

Your vba script worked wonders!! Thank you for the setup. I have question about the commands. I ran it and I came across a hiccough. Data is copied from one worksheet to another, it stops if it comes across a row with "0" in the cells and will not resume the copying. Even though there are rows below it with data.

I think your code has a command about going through all the rows and stopping when it reaches 0. I have trying to remove that restriction, but sadly I have failed to find which line that command sits in.

I would really appreciate if you can point it out to me!!

Many thanks,
Azza

 

[vletm]

Azza87
My sample runs until there is empty cell in A-column (start from row 2).
In my sample file, that empty cell is A10.
Sheet2's the result 'table's height depends Sheet1's data, it's not fixed size.
If You could send sample of Your needed changes, I would check it.

 

[Azza87]

Hello vltem,

Sorry for the late reply. I have attached a sample of how would my list look like before I would copy/ paste it to another sheet. I hope this will be more clear. I've added a command button (1. Press to Refresh Table) to refresh/ delete the table in Sheet 2 as a precaution before somebody wants to start copying the data.

Quick question, the date input is not always in the right format. The vba script you have written that allows the data to spit out the date in a specific format. THis is great, but I wonder if somebody was to not write the date in day/month/ year but instead write something like: february, or mid march. Would this stop the code from running?

Thanks in advance.

 

[vletm]

Azza87 --- yes!
I find those Your buttons
... but You've used ActiveX-components
and those not work with me.
There were ... many macros
... and I didn't start to guess, which one You use or not!
> I used my file, with Your new data and my macros.
Those '0's can cause something and
You've add one more 'Co' ... maybe later more and more?
That's why the whole 'Sheet2-table' would create every time!
Now, no matter number of 'Co's!
> Only one [button] will need.
If there will be '0' as 'Co' then it would act like other 'Co's!
> It would be more clear if there won't be those '0's!
You asked about TF-marks ... there is no matter what will be there!
'Co' names which column data will use
and the rest of data will copy as those are written!