Copy Range of Cells to Another Sheet

[JayB0730]

Hello,

I have two Worksheets; "DataSet" & "SubmitSheet".

DataSet:
This worksheet contains data in columns A:I (Row 1 contains Headers); I want to copy all data residing in cells "A:D" where adjacent cell "I" is blank (starting on Row 2 because of headers). There will be multiple rows and I want to copy every row of data (A:D) where "I" is blank & paste into "SubmitSheet". Please note that I will be running another macro that will enter a date in column "I" after this macro is completed (I already have that script & will call that after this process is run). This way when I add information in the future in columns A:H, I can run this macro again for new information where "I" is blank. Should there be no blank cells in Column I (i.e. dates are already populated), I would like to have a message pop up alerting user that information is up-to-date (I'll update message accordingly)

SubmitSheet:
This sheet will always contain Headers (A:D) that is shown on "DataSet". This sheet should be overwritten with data copied from DataSet each time the Macro is run.

Thank you, in advance, for your help!
Jay

 

[Fluff]

How about

Sub CopyIfColIisBlank()

   Dim Ar As Areas
   Dim Rng As Range
   
   Sheets("SubmitSheet").UsedRange.Offset(1).Clear
   On Error Resume Next
   Set Ar = Sheets("DataSet").Columns(9).SpecialCells(xlBlanks).Areas
   On Error GoTo 0
   If Ar Is Nothing Then
      MsgBox "Nothing to copy"
      Exit Sub
   End If
   For Each Rng In Ar
      Rng.Offset(, -8).Resize(, 4).Copy Sheets("SubmitSheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
   Next Rng
End Sub

 

[JayB0730]

Worked great, thank you!

 

[Fluff]

Glad to help & thanks for the feedback