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

nhbartos

Board Regular
Joined
May 23, 2015
Messages
148
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!!!!!!!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.


Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,127
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top