0

I’ve got the following bit of code that copies rows from one sheet to another if it is the same region that has been selected in a combo box. The problem I have is that it is copying into row 5 rather than row 6 and copying over the column titles. Do you know why this might be happening? I thought the fourth line starting "Worksheets("NLE_").Range("A")... would pick the first empty row i.e. A6, but it picks A5? 

If chkNLE.Value = True And cboLA = "All" Then
LastRow = Worksheets("NLE_").Range("A" & Rows.Count).End(xlUp).Row *– clears any data that is already in the worksheet*
Worksheets("NLE_").Range("A6:W" & LastRow).Clear

For Each i In Sheets("NLE").Range("NLEregion") *– NLEregion is a named range for the column with region in it in the sheet when the data is being copied from*
    If i.Value = cboRegion.Value Then
        i.EntireRow.Copy
        Worksheets("NLE_").Range("A" & Rows.Count).End(xlUp).Offset (1)
        Sheets("NLE_").Visible = True
        Sheets("Front Page").Visible = False
        UserForm1.Hide
    End If
Next i     

End If
Community
  • 1
  • 1
Vicky
  • 1
  • Where is your "Paste" code – Siddharth Rout Sep 09 '14 at 09:59
  • Also you can use [AUTOFILTER](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) to copy the data in one go to the destination sheet. – Siddharth Rout Sep 09 '14 at 10:04
  • @Siddharth Rout thanks but I'm not quite sure how to change the code to add in a paste code. I'm assuming it would have to change all of the third and 4th lines (actually these are both on the same line in my code)? Column A has numbers 1 -5 in the first 5 rows so that excel knows there is something in those cells so I thought that would work but it doesn't. – Vicky Sep 09 '14 at 12:44

1 Answers1

0

Thanks for the suggestion @Siddarth Rout. I went away and worked out how to use Autofilter to do this and it is much quicker than my previous code, which at work was a little slow becasue we are on a thin client.

There maybe a more efficient way of doing this as I am new to VBA but this does work.

If chkNLE.Value = True And cboLA = "All" And cboLA <> "" And cboRegion <> "All" Then
Application.ScreenUpdating = False 'stops the screen from updating while copying and pasting

LR = Sheets("NLE").Range("A" & Rows.Count).End(xlUp).row ' sets LR to the last cell number in A
Set rng = Sheets("NLE").Range("A5:V" & LR) ' Sets rng from A5 to the last cell in column V - includes second row of titles

    With rng
        .AutoFilter
        .AutoFilter Field:=13, Criteria1:=cboRegion.Value 'filters on the region selected in the drop down box
        .SpecialCells(xlCellTypeVisible).Copy 'copies just the filtered data
    End With

Sheets("NLE_").Range("A5").PasteSpecial xlPasteAll 'pastes just the filtered data into NLE_
Sheets("NLE").AutoFilterMode = False

Application.CutCopyMode = False
Sheets("NLE_").Range("A5:V5").AutoFilter 'Adds the filter back to NLE_
Sheets("NLE").Range("A5:V5").AutoFilter

Application.ScreenUpdating = True 'allows the screen to update
Vicky
  • 1