Excel 2007 - VBA to copy data from one sheet and pasting to another sheet

[abhimails]

I am trying to copy columns A:N from sheets "Deliverables" to worksheet "Next7". I have limited knowledge of VBA, please help. I am also trying to delete the data in Next7 prior to pasting the data. Post copying data I wish to check if data in column B is "Current Date" +/- 7 AND if column 6 value is pending. This will give me a list of all pending reports which I intend to use for my scrollable dashboard in home tab (Need Help in that too).

Please find my code below.

Sub Next7DaysReports()
'
' Next7DaysReports Macro
' Reports pending in the next 7 days
Dim x As Long, lastrow As Long


Worksheets(“Next7”).Range("A:N").ClearContents
Sheets("Deliverables").Active
Range("A:K").Select
Selection.Copy
Sheets("Next7").Range("A:K").Select
ActiveSheet.Paste
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  For x = lastrow To 1 Step -1
     If Cells(x, 2).Value >= Date + 7 And Cells(x, 27) = "Pending" Then
          Rows(x).Delete
     End If
     Next x



End Sub

 

[venkat1926]

I have slightly modified you macro. see my comments in the macro

As I do not have your data sheets I am not able to test modified macro. KEEP ORIGINAL DATA FILE CAREFULLY SOMEWHERE FOR RETRIEVAL IF SOMETHING GOES WRONG. TEST THE MACRO IN A TEMPORARY FILE

check whether the macro gives what you want.

Sub Next7DaysReports()
'
' Next7DaysReports Macro
' Reports pending in the next 7 days
    Dim x As Long, lastrow As Long






    'Worksheets(“Next7”).Range("A:N").ClearContents
    'Sheets("Deliverables").Active
    ' dont type active type activate, even that is not necessary
    'Sheets("deliverables").Activate
    With Sheets("deliverables")
        Range("A:K").Copy
        With Worksheets("next7")
            .Range("A1").PasteSpecial


            'Range("A:K").Select
            'Selection.Copy
            'Sheets("Next7").Range("A:K").Select
            'ActiveSheet.Paste
            ''Range("A:K").Copy


            'I presume the next codes are with respect to sheets("deliverable")


            lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
            For x = lastrow To 1 Step -1
                If .Cells(x, 2).Value >= Date + 7 And .Cells(x, 27) = "Pending" Then
                    .Rows(x).Delete
                End If
            Next x
        End With
    End With


End Sub