Copy rows from one sheet to another

[VegasNath]

Please see attachment:

What i am trying to do is:

On the Data Lookup sheet in columns O&P, I have the row start and row end numbers of data that I would like to copy from sheet 1 to the next blank row of column A in sheet 2. However, where the rowsta & rowend numbers are #N/A, I would like to copy the data from columna A:F from that row in the lookup sheet to columns A:F in sheet 2 and then add a zero to the next n rows in column A of sheet 2, n being (65-1). So for example, row 19 of the lookup sheet shows N/A, so copy A19:F19 to the next available row in sheet 2, then zero in column A on the next 64 rows.

I would really appreciate any help that can be offered with this. Thanks

[HansV]

Try this macro, and don't forget to save the workbook as a .xlsm:

Code: Select all

Sub CopyRows()
  Dim r As Long
  Dim m As Long
  Dim t As Long
  Dim lngStart As Long
  Dim lngEnd As Long
  Dim wshD As Worksheet
  Dim wshS As Worksheet
  Dim wshT As Worksheet
  Set wshD = Worksheets("Data Lookup")
  Set wshS = Worksheets("Sheet1")
  Set wshT = Worksheets("Sheet2")
  m = wshD.Range("O" & wshD.Rows.Count).End(xlUp).Row
  t = 1
  For r = 2 To m
    If IsError(wshD.Range("O" & r)) Then
      wshD.Range("A" & r & ":F" & r).Copy _
        Destination:=wshT.Range("A" & t)
      wshT.Range("A" & (t + 1) & ":A" & (t + 64)) = 0
      t = t + 65
    Else
      lngStart = wshD.Range("O" & r)
      lngEnd = wshD.Range("P" & r)
      wshS.Range(lngStart & ":" & lngEnd).Copy _
        Destination:=wshT.Range("A" & t)
      t = t + lngEnd - lngStart + 1
    End If
  Next r
End Sub

[VegasNath]

Thankyou very much Hans, this works like a dream. One small thing, how can I substitute the 64 & 65 in the following rows for calculations based on the result of column M?

Code: Select all

      wshT.Range("A" & (t + 1) & ":A" & (t + 64)) = 0
      t = t + 65

[HansV]

Replace 64 with wshD.Range("M" & r) - 1.

[VegasNath]

Great, Cheers!

In columns R&S, I now have the new rowsta & rowend numbers as a list. Is it possible to use that list of numbers as an array to format cells in sheet 2 as a single statement, for example:

rowsta = 1, 91, 154, 244, 307 etc... Format A1, A91, A154........

[HansV]

You'd have to loop through the cells in column R to build the array needed, so you might as well loop through the cells to format them - it's less work!

[VegasNath]

Ok, this works:

Code: Select all

    For r = 2 To lngMaxRow
        t = ws1.Range("R" & r).Value
        ws3.Range("A" & t).Interior.ColorIndex = 44
        ws3.Range("F" & t).Interior.ColorIndex = 44
        ws3.Range("D" & t + 5).Interior.ColorIndex = 44
        ws1.Range("U" & r).Value = ws3.Range("D" & t + 5).Value
    Next r

Is there a way that I can add into this loop the creation of page breaks, or would I need to go down the following route?

Code: Select all

    Set ws3.HPageBreaks(1).Location = Range("A" & ws1.Range("S2").Value + 1)
    Set ws3.HPageBreaks(2).Location = Range("A" & ws1.Range("S3").Value + 1)
    Set ws3.HPageBreaks(3).Location = Range("A" & ws1.Range("S4").Value + 1)
.....etc 32 times

[HansV]

You should be able to use the following line within the loop:

Set ws3.HPageBreaks(r - 1).Location = ws3.Range("A" & (ws1.Range("S" & r).Value + 1))

[VegasNath]

Cheers Hans, another project done & dusted.