VBA - Copy row based on text in cell to a new sheet into first empty row

randomintel

New Member
Joined
Apr 23, 2019
Messages
7
Dear all,

I've been looking high and low for a macro that will help me copy a certain row based a specific string in a cell to a new sheet and the first empty row in that sheet. Please help me figure this out.

Context:
- One Excel file
- Two sheets "JiraData" and "Dash". Please note that "JiraData" is updated daily through a query and can thus be seen as master data.
- I have a macro that copies all the filled rows from "JiraData" to "Dash". Used before each new sprint to plan capacity.
- I have a macro that compares rows between "JiraData" and "Dash" using a formula, and adds string "Missing" behind those rows that aren't present in the "Dash". This is used to visually spot new rows that appear in "JiraData", but weren't previously copied over to "Dash".

Challenge:
I am trying to create a macro that will scan column I in sheet "JiraData" for the string "Missing". Then copy that whole respective row (A:H) from "JiraData" to the first empty row in "Dash" starting from B3 down. I would also like to add a date stamp in column A that shows when the new row was added to "Dash".

Goal:
Be able to add new rows to "Dash" when "JiraData" has new rows that aren't already entered in "Dash".


Thank you so much beforehand, I really appreciate your time reading this.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sounds like:

VBA Code:
Sub copy()

Dim sht As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim LastRow2 As Long

Set sht = Sheets("Dash")
Set sht2 = Sheets("JiraData")

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
LastRow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row

With sht2
    .AutoFilterMode = False
    With .Range("A1:I" & LastRow2)
        .AutoFilter Field:=9, Criteria1:="Missing"
        .SpecialCells(xlCellTypeVisible).copy Destination:=sht.Range("A" & LastRow)
    End With
End With


End Sub
 
Upvote 0
Here another macro for you to consider:

VBA Code:
Sub Copy_Missing_Rows()
  With Sheets("JiraData")
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("A1:I" & .Range("I" & Rows.Count).End(xlUp).Row).AutoFilter 9, "Missing"
    .AutoFilter.Range.Range("A2:H" & .Range("I" & Rows.Count).End(xlUp).Row).Offset(1).Copy
    With Sheets("Dash")
      .Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
      .Range("A" & .Range("A" & Rows.Count).End(xlUp)(2).Row & ":A" & .Range("B" & Rows.Count).End(xlUp).Row).Value = Date
    End With
    .ShowAllData
  End With
  Application.CutCopyMode = False
End Sub
 
Upvote 0
Here another macro for you to consider:

VBA Code:
Sub Copy_Missing_Rows()
  With Sheets("JiraData")
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("A1:I" & .Range("I" & Rows.Count).End(xlUp).Row).AutoFilter 9, "Missing"
    .AutoFilter.Range.Range("A2:H" & .Range("I" & Rows.Count).End(xlUp).Row).Offset(1).Copy
    With Sheets("Dash")
      .Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
      .Range("A" & .Range("A" & Rows.Count).End(xlUp)(2).Row & ":A" & .Range("B" & Rows.Count).End(xlUp).Row).Value = Date
    End With
    .ShowAllData
  End With
  Application.CutCopyMode = False
End Sub

Thank you Dante Amor, that worked really well!:biggrin:

I am however struggling with the .ShowAllData command. It returns an error since the sheet I am in when I run the macro is "Dash". I've tried rewriting it, but can't seem to figure why it doesn't work:

VBA Code:
  If (Sheets("JiraData").AutoFilterMode And Sheets("JiraData").FilterMode) Or Sheets("JiraData").FilterMode Then
    Sheets("JiraData").ShowAllData

Thoughts?
 
Upvote 0
Sounds like:

VBA Code:
Sub copy()

Dim sht As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim LastRow2 As Long

Set sht = Sheets("Dash")
Set sht2 = Sheets("JiraData")

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
LastRow2 = sht2.Cells(sht2.Rows.Count, "A").End(xlUp).Row

With sht2
    .AutoFilterMode = False
    With .Range("A1:I" & LastRow2)
        .AutoFilter Field:=9, Criteria1:="Missing"
        .SpecialCells(xlCellTypeVisible).copy Destination:=sht.Range("A" & LastRow)
    End With
End With


End Sub

Thank you for the effort, this almost did the trick but DanteAmor nailed it ;)
 
Upvote 0
It doesn't matter on which sheet you perform the execution.
Try this:

VBA Code:
Sub Copy_Missing_Rows()
  With Sheets("JiraData")
    If .AutoFilterMode Then .AutoFilterMode = False
    If .FilterMode Then .ShowAllData
    .Range("A1:I" & .Range("I" & Rows.Count).End(xlUp).Row).AutoFilter 9, "Missing"
    .AutoFilter.Range.Range("A2:H" & .Range("I" & Rows.Count).End(xlUp).Row).Offset(1).Copy
    With Sheets("Dash")
      If .AutoFilterMode Then .AutoFilterMode = False
      If .FilterMode Then .ShowAllData
      .Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
      .Range("A" & .Range("A" & Rows.Count).End(xlUp)(2).Row & ":A" & .Range("B" & Rows.Count).End(xlUp).Row).Value = Date
    End With
    If .AutoFilterMode Then .AutoFilterMode = False
  End With
  Application.CutCopyMode = False
End Sub
 
Upvote 0
It doesn't matter on which sheet you perform the execution.
Try this:

VBA Code:
Sub Copy_Missing_Rows()
  With Sheets("JiraData")
    If .AutoFilterMode Then .AutoFilterMode = False
    If .FilterMode Then .ShowAllData
    .Range("A1:I" & .Range("I" & Rows.Count).End(xlUp).Row).AutoFilter 9, "Missing"
    .AutoFilter.Range.Range("A2:H" & .Range("I" & Rows.Count).End(xlUp).Row).Offset(1).Copy
    With Sheets("Dash")
      If .AutoFilterMode Then .AutoFilterMode = False
      If .FilterMode Then .ShowAllData
      .Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
      .Range("A" & .Range("A" & Rows.Count).End(xlUp)(2).Row & ":A" & .Range("B" & Rows.Count).End(xlUp).Row).Value = Date
    End With
    If .AutoFilterMode Then .AutoFilterMode = False
  End With
  Application.CutCopyMode = False
End Sub


Thank you, the error is eliminated. :)

Still, the filter stays on in "JiraData" and I'd like to see all entries in "JiraData" after running the copy-macro. Any ideas? I've attached a snippet.
 

Attachments

  • Problem.PNG
    Problem.PNG
    1.8 KB · Views: 14
Upvote 0
Still, the filter stays on in "JiraData"
That is because you have the range in a table.

Try this:
VBA Code:
Sub Copy_Missing_Rows()
  Application.ScreenUpdating = False
  With Sheets("JiraData")
    If .AutoFilterMode Then .AutoFilterMode = False
    If .FilterMode Then .ShowAllData
    .Range("A1:I" & .Range("I" & Rows.Count).End(xlUp).Row).AutoFilter 9, "Missing"
    .AutoFilter.Range.Range("A2:H" & .Range("I" & Rows.Count).End(xlUp).Row).Offset(1).Copy
    With Sheets("Dash")
      If .AutoFilterMode Then .AutoFilterMode = False
      If .FilterMode Then .ShowAllData
      .Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
      .Range("A" & .Range("A" & Rows.Count).End(xlUp)(2).Row & ":A" & .Range("B" & Rows.Count).End(xlUp).Row).Value = Date
    End With
    If .AutoFilterMode Then .AutoFilterMode = False
    On Error Resume Next
    .ShowAllData
  End With
  Application.CutCopyMode = False
End Sub
 
Upvote 0
That is because you have the range in a table.

Try this:
VBA Code:
Sub Copy_Missing_Rows()
  Application.ScreenUpdating = False
  With Sheets("JiraData")
    If .AutoFilterMode Then .AutoFilterMode = False
    If .FilterMode Then .ShowAllData
    .Range("A1:I" & .Range("I" & Rows.Count).End(xlUp).Row).AutoFilter 9, "Missing"
    .AutoFilter.Range.Range("A2:H" & .Range("I" & Rows.Count).End(xlUp).Row).Offset(1).Copy
    With Sheets("Dash")
      If .AutoFilterMode Then .AutoFilterMode = False
      If .FilterMode Then .ShowAllData
      .Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
      .Range("A" & .Range("A" & Rows.Count).End(xlUp)(2).Row & ":A" & .Range("B" & Rows.Count).End(xlUp).Row).Value = Date
    End With
    If .AutoFilterMode Then .AutoFilterMode = False
    On Error Resume Next
    .ShowAllData
  End With
  Application.CutCopyMode = False
End Sub

Good morning.
Thank you for the effort. Unfortunately, this didn't solve the issue. Still, given the cost/benefit of this, I'd like to just say thank you and instruct the users to manually turn off the filters.

You have been instrumental in this, thank you. Have a great start to your week! :)
 
Upvote 0
to manually turn off the filters.

In my tests the filters are off, try the following:

VBA Code:
Sub Copy_Missing_Rows()
  Application.ScreenUpdating = False
  With Sheets("JiraData")
    If .AutoFilterMode Then .AutoFilterMode = False
    If .FilterMode Then .ShowAllData
    .Range("A1:I" & .Range("I" & Rows.Count).End(xlUp).Row).AutoFilter 9, "Missing"
    .AutoFilter.Range.Range("A2:H" & .Range("I" & Rows.Count).End(xlUp).Row).Offset(1).Copy
    With Sheets("Dash")
      If .AutoFilterMode Then .AutoFilterMode = False
      If .FilterMode Then .ShowAllData
      .Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
      .Range("A" & .Range("A" & Rows.Count).End(xlUp)(2).Row & ":A" & .Range("B" & Rows.Count).End(xlUp).Row).Value = Date
    End With
    If .AutoFilterMode Then .AutoFilterMode = False
    On Error Resume Next
    .ShowAllData
    .ListObjects(1).AutoFilter.ShowAllData
    .ListObjects(1).Range.AutoFilter
  End With
  Application.CutCopyMode = False
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,225
Messages
6,177,273
Members
452,765
Latest member
Erka Gizli

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