Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying excel range from worksheet to worksheet (VBA and Excel 2000)

    Hi gurus,

    I am trying to copy a specific range from my main worksheet across into a worksheet that is going to be a filtered view. I will be filtering on the value in the D column.

    The code I have so far is below. Can someone help me with the code that actually copies the range to the current worksheet. eg. I want to copy Payments!A2:H2 to <current worksheet>.new location.

    thanks...

    Code snippet follows...

    Private Sub Worksheet_Activate()

    Dim rngTestCell As Range

    For Each rng In Worksheets("Payments").Range("D")

    If (rngTestCell.Value = "a") Then
    MsgBox "found"
    ' code here to copy the rngTestCell.Row A -> H from the Payments worksheet to the current worksheet...
    End If

    Next rng
    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copying excel range from worksheet to worksheet (VBA and Excel 2000)

    How about using the Autofilter to get the "a" value cells all in one hit, and copying them all, like this example:

    Worksheets("Payment").Range("D").AutoFilter(Field: =1, _
    Criteria1:="a").SpecialCells(xlCellTypeVisible).Co py
    Activesheet.rngTestCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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