Having issues getting rows to copy over.

[jmoore3274]

I'm trying to get rows from my "Thermo" (Sheet1) worksheet copied over to the "Thermal_Report" (Sheet7) worksheet. These need to be copied over one at a time. There will be more code that I will write late on. For now I'm focused on getting the loop that will copy and paste the data working correctly. I started off by filtering the data for what I will be looking for. From there I want to select the first line and copy it over to a specified line in another worksheet. I want this to loop till it runs out of stuff to copy. Can someone take a look at this code and help me out with this. I'm getting out of range errors a the " Worksheets("Thermo").Range.src_cursor.Copy _ Destination:=dest_cursor" and I'm stumped. By the way I'm not very good at this so please be gentle.

Sub GenerateThermalReport()
  
  Dim src_cursor As Range
  Dim dest_cursor As Range
  
  Set src_cursor = ActiveSheet.Range("A3:X3")
  Set dest_cursor = Sheets("Thermal_Report").Range("A2:X2")

 'Code below filters the "Thermo" sheet for only prioirty 20 problems.
  
  With Sheet1
      .Unprotect Password:="HurleyIR"

         Sheet1.Range("$A$2:$X$24").AutoFilter Field:=7, Criteria1:="20"
  End With

  'Code Below loops through each line copying it then pasting it to the report.
  
 Do Until IsEmpty(src_cursor(1))
    
    Worksheets("Thermo").Range.src_cursor.Copy _
    Destination:=dest_cursor
       
    ' move to next row
    Set src_cursor = src_cursor.Offset(1, 0)
    
Loop

  ' Resets the filter to show all data in the sheets and protects the "Thermo" Sheet
 
 With Sheet1
      
     ActiveSheet.Range("$A$2:$X$24").AutoFilter Field:=7
  
     .Protect Password:="HurleyIR"
 
 End With
End Sub

[gsnidow]

Try this...

Sub GenerateThermalReport()
  
  Dim src_cursor As Range
  Dim dest_cursor As Range
  
  Set src_cursor = ActiveSheet.Range("A3:X3")
  Set dest_cursor = Sheets("Thermal_Report").Range("A2:X2")

 'Code below filters the "Thermo" sheet for only prioirty 20 problems.
  With Sheets("Thermo")
    .Unprotect Password:="HurleyIR"
    .Range("$A$2:$X$24").AutoFilter Field:=7, Criteria1:="20"
  End With

  'Code Below loops through each line copying it then pasting it to the report.
    Do Until IsEmpty(src_cursor(1))
        src_cursor.Copy dest_cursor
        ' move to next row
        Set src_cursor = src_cursor.Offset(1, 0)
    Loop

    ' Resets the filter to show all data in the sheets and protects the "Thermo" Sheet
    With Sheets("Thermo")
        .Range("$A$2:$X$24").AutoFilter Field:=7
        .Protect Password:="HurleyIR"
    End With
    
End Sub

Are you intentionally over-writing each new row? There is nothing in there to offset the destination by one row for every insert. Also, I changed all instances of "Sheet1" to point to Sheets("Thermo").

Greg

[jmoore3274]

Thank you fellas! I got it working. It's the simple things in life that can tie you up for a while! I appreciate the help!

[Norie]

You don't need the sheet reference.

src_cursor.Copy Destination:=dest_cursor

[jmoore3274]

Yes for right now I'm over writing the same row in the destination. Whats going on is that the one row in the destination sheet is populating a report on that same page. Once that report is populated it will be saved as a new workbook then a new Line will be copied over and that one saved as its own workbook. Rinse and repeat.

Right now I want to make sure the loop is working correctly before I add more to it.

Thanks for the fast reply! I'm going to test it right now.