VB Code: Copy Row(s) with content to another tab table based on value

[nhbartos]

Hi folks,

Spreadsheet with 3 tabs: Position By Location, Hold, Vacancies

All three have identical headers in A1:M1.
Raw data is entered in the Position By Location tab starting with row 2, and may extend to 1000+ rows.


1. I would like help with code:

Position By Location tab:

If there is ANY content in Column K, starting with row 2...
"COPY" entire row to the Vacancies tab
beginning A2:M2 and copying down as needed.

Keep all rows copied to the Vacancies tab sorted by Column E (A-Z).


2. I would like help with code:


Position By Location tab:

If the word "HOLD" in Column J, starting with row 2...
"COPY" entire row to the Hold tab
beginning A2:M2 and copying down as needed.

Keep all rows copied to the Hold tab sorted by Column E (A-Z).


Thanks!!!!!!!

 

[nhbartos]

I got as far as this code, but I have a few issues.

1. It is only copying rows from M2:M102.
2. How would I make these run automatically, anytime a "Yes" and/or "Hold" is entered. I don't want to have to run the macro to update.

Sub CopyVacancies()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Positions")
    Set Target = ActiveWorkbook.Worksheets("Vacancies")


    j = 2     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("M2:M1000")   ' Do 1000 rows
        If c = "yes" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub




Sub CopyHold()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet


    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Positions")
    Set Target = ActiveWorkbook.Worksheets("Hold")


    j = 2     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("L2:L1000")   ' Do 1000 rows
        If c = "HOLD" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

 

[nhbartos]

Hi folks,

Any help?

Here is where I am on this:

1. I have a three sheet workbook, Positions (source), Hold (Target), and Vacancies (Target).
I have two macros. Can they can be combined to one for smoother operation?

2. I would like these macros to run automatically whenever there is an update to either macros source ranges, including deletions.

3. The HOLD macro seems to work fine, could use some fresh eyes.
Hold macro: is intended to copy rows from the Positions tab that meet the criteria in the source range to the Hold tab whenever a user enters a “Hold”, and pastes those to the Hold tab, starting on row 2. If and existing “Hold” is deleted from the source range, I want the Hold tab to update automatically.

4. PROBLEM with the Vacancies macro. I have it set up identically to the Hold macro, almost, but when it copys rows meeting the criteria, it only seems to look or only copy those meeting the criteria to row 112. Nothing after. I have about 60 rows meeting the criteria, but is is only copying the first 12.
Both macros need to work within a table with 1000 rows.

Also with the Vacancies macro...I have the criteria as "Yes". I don't want that. I want the macro to run ONLY when a number is entered, 1 to 10.