Results 1 to 8 of 8

Assigning/Applying Variables to Copy/Paste Columns

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-06-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    225

    Post Assigning/Applying Variables to Copy/Paste Columns

    Hello Macro Veterans,

    I am trying to write a macro that filters a worksheet, and then copies over certain columns to another worksheet in the same workbook. I want to assign variables because new columns are frequently added, changing the coordinates of other column titles. As of now, I have the variables working for the filtering part, but I am not sure how to create/apply variables to the part of the code that grabs the column and pastes it into another worksheet. I have placed dotted lines around the part of the code that I am having trouble with. It is currently hard-coded with the U2:U, but variables are necessary for flexibility.

    The worksheet i want to copy from is "MOD"
    The worksheet i want to copy to is "NO WELCOME OR 30MDL"

    Hope someone can help, thank you.

    Nick


    Sub Prac2()
    
    Dim i As Integer, rngData As Range
    
    Set rngData = Range("A1").CurrentRegion
    
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    
    With Worksheets("MOD")
    i = Application.WorksheetFunction.Match("MGR_NAME", Range("A1:ZZ1"), 0)
    rngData.AutoFilter Field:=i, Criteria1:="P, STEVEN", Operator:=xlOr, Criteria2:="M, LUCINDA"
    
    i2 = Application.WorksheetFunction.Match("7060", Range("A1:ZZ1"), 0)
    rngData.AutoFilter Field:=i2, Criteria1:="=", Operator:=xlOr, Criteria2:="="
    End With
    
    
    ---------------------------------------------------------------------------
    With Worksheets("MOD")
    If Cells(Rows.Count, "A").End(xlUp).Row > 1 Then
        .Range("U2:U" & lrow).SpecialCells(xlCellTypeVisible).Copy _
        Sheets("NO WELCOME OR 30MDL").Cells(Sheets("NO WELCOME OR 30MDL").Cells(Rows.Count, "A").End(xlUp).Row + 1, "A")
    End If
    End With
    ----------------------------------------------------------------------------
        Sheets("MOD").Select
    
    End Sub
    Last edited by Gard5096; 12-05-2012 at 03:22 PM. Reason: clarification

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1