none
Copying Specific data from one worksheet to another RRS feed

  • Question

  • Good morning,

    I am trying to fix my code to transfer specific data from one worksheet to another. The code I created was successful, however, I am wanting it to start plugging in the data on row 12 and not row 1. Can someone please help me fix my code. My code is as follows:

    Private Sub CommandButton1_Click()
    Dim i, LastRow

    LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet2").Range("A12:Q500").ClearContents
    For i = 2 To LastRow
    If Sheets("Sheet1").Cells(i, "L").Value = "Rehab apt. pending." Then
    Sheets("Sheet1").Cells(i, "L").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
    Next i

    End Sub

    If possible too i'd also like to transfer that worksheet into a new workbook. Is there a  code I could add to this?

    Thank you for reading.

    Thursday, February 12, 2015 2:40 PM

Answers

  • To start on row 12, you could add a value into row 11 on sheet2:

    Sheets("Sheet2").Range("A11").Value = "Values below here"

    You could also not loop:

    With Sheets("Sheet1")
    With Intersect(.Range("L:L"), .UsedRange)
    .AutoFilter Field:=1, Criteria1:="=Rehab apt. pending."
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Range("A12").EntireRow
    .AutoFilter
    End With
    End With

    To "Transfer" a sheet, use this to create a copy in a new workbook

    Sheets("Sheet2").Copy

    or this to move the sheet to a new workbook

    Sheets("Sheet2").Move


    Thursday, February 12, 2015 3:10 PM
  • What did not work? Note that my code was not complete - you still need to clear the contents with

    Sheets("Sheet2").Range("A12:Q500").ClearContents

    • Marked as answer by LuiMo Thursday, February 12, 2015 4:57 PM
    Thursday, February 12, 2015 4:25 PM

All replies

  • To start on row 12, you could add a value into row 11 on sheet2:

    Sheets("Sheet2").Range("A11").Value = "Values below here"

    You could also not loop:

    With Sheets("Sheet1")
    With Intersect(.Range("L:L"), .UsedRange)
    .AutoFilter Field:=1, Criteria1:="=Rehab apt. pending."
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Sheet2").Range("A12").EntireRow
    .AutoFilter
    End With
    End With

    To "Transfer" a sheet, use this to create a copy in a new workbook

    Sheets("Sheet2").Copy

    or this to move the sheet to a new workbook

    Sheets("Sheet2").Move


    Thursday, February 12, 2015 3:10 PM
  • thank you for your assistance, but I was able to get it to work. Any more advice?
    Thursday, February 12, 2015 3:27 PM
  • What did not work? Note that my code was not complete - you still need to clear the contents with

    Sheets("Sheet2").Range("A12:Q500").ClearContents

    • Marked as answer by LuiMo Thursday, February 12, 2015 4:57 PM
    Thursday, February 12, 2015 4:25 PM
  • I apologize. It has been a very long time since I did any coding in excel. It did work for me now. Thank you!

    • Edited by LuiMo Thursday, February 12, 2015 4:47 PM
    Thursday, February 12, 2015 4:30 PM