0

I'm using the code below to copy data entered into a table into another larger database in another sheet (original question and information on my worksheets here).

For Each cl In Sheet2.Range("A8:A23")
    If Not IsEmpty(cl.Value) Then
        With Worksheets("Database")
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(1, 18).Value = cl.Resize(1, 18).Value
        End With
    End If
Next

This has been working great so far, however I've recently added another 2 columns of data into the data entry sheet (Row S & T). I need these new columns to also be pasted into the new sheet when the command button is pressed, but not into columns S & T - I want them to be pasted into columns AL & AM.

Any ideas how best to do this?

Any help much appreciated.

Community
  • 1
  • 1
Seb Moran
  • 5
  • 2

1 Answers1

0

I'd go as follows:

With Worksheets("Database")
    For Each cl In Sheet2.Range("A8:A23").SpecialCells(xlCellTypeConstants)
        With .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Resize(1, 18).Value = cl.Resize(1, 18).Value
            .Offset(, 37).Resize(, 2).Value = cl.Offset(, 18).Resize(, 2)
        End With
    Next
End With

where:

  • I took With Worksheets("Database") outside the loop since it's not affected by it

  • I used SpecialCells(xlCellTypeConstants) method to directly iterate through not-blank cells and avoid the If Not IsEmpty(cl.Value) Then check

user3598756
  • 28,893
  • 4
  • 18
  • 28
  • Thanks for the help. This code successfully copies the data in A8:R23, however it hasn't successfully copied the data in S8:T23 to anywhere that I can find. – Seb Moran Mar 30 '17 at 10:24
  • Figured it out - just a missing ".Value" on a line. Seems to be working fine now. So thanks! – Seb Moran Mar 30 '17 at 10:33