VB help

Posted on 2012-09-11
Last Modified: 2012-09-11
Please can you help with the attached file.

I would like to copy & paste special the values on sheet 1 into sheet 3

Sheet 1 Range B9:S108

Sheet 3 starting with B9 and onward

However I only wish to export lines which have an end date in column N, the lines which do not will then shift up the page towards B9 etc

The idea is to do this once a week so that in sheet 3 old lines are added and used as an archive
Question by:Kiwi-123
    LVL 10

    Accepted Solution

    I think you want your button to do this:
    Private Sub CommandButton3_Click()
    Dim i As Long
    Dim row As Range
        j = Sheets("Archive").Range("B" & ActiveSheet.Rows.Count).End(xlUp).row + 1
        For i = 9 To ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).row
            Set row = ActiveSheet.Range("A" & i)
            If row.Cells(1, 14).Value <> "" Then
                Sheets("Archive").Range("B" & j & ":S" & j).Value = ActiveSheet.Range("B" & i & ":S" & i).Value
                j = j + 1
            End If
        Next i
        ' Delete the archived rows
        For i = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).row To 9 Step -1
            Set row = ActiveSheet.Range("A" & i)
            If row.Cells(1, 14).Value <> "" Then
            End If
        Next i
    End Sub

    Open in new window


    Author Closing Comment

    Perfect! Many thanks for all your help.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Run code from text file in vb 1 18
    Cannot get VBScript to run as schedules task 10 19
    Excel 2010 question 3 26
    Excel If tests 10 37
    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now