CODEWITHSUNDEEP
vbaexcel
Nolemonkey
Nolemonkey

Reputation: 159

Excel Copy on certain cells to a new worksheet row if value is not blank

I have this code that will check column E in my xls file and if it is not blank copy the row to a new worksheet. However I can't find how to exclude cells. Columns C, D, E, F, and G and the search columns. If the macro I am running (in this case E) has data in it, I want to copy every cell on the row Except C, D, F, G. Is this possible?

Sub CopyYes()
    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("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")

    J = 1     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("E1:E1000")   ' Do 1000 rows
        If c <> "" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

Upvotes: 0

Views: 636

Answers (2)

ManishChristian
ManishChristian

Reputation: 3784

This is how you can add the delete part in your code: 

Sub CopyYes()
    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("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")

    j = 1     ' Start copying to row 1 in target sheet
    For Each c In Source.Range("E1:E1000")   ' Do 1000 rows
        If c <> "" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           With Target
              .Range("C" & j).ClearContents
              .Range("D" & j).ClearContents
              .Range("F" & j).ClearContents
              .Range("G" & j).ClearContents
            End With
           j = j + 1
        End If
    Next c
End Sub

Let me know if you have any doubts.

Upvotes: 1

dyslexicgruffalo
dyslexicgruffalo

Reputation: 374

Easiest thing to do is to write an array of strings which contain the column/rows that you do not want to copy.

Then write an if statement saying that if the current column/row the code is currently looking at then it should move onto the next section.

Here is how to define an array if you didn't know

Dim DoNotCopy(1 to 10) as ListRows *OR as ListColumns*

There you can define each part of the array as columns/rows

Set DoNotCopy as Sheet("blah").Range("D:D")

Hope this helps!

Upvotes: 0

Related Questions