Results 1 to 2 of 2

Copy a range to another sheet

Threaded View

  1. #1
    Forum Contributor mcinnes01's Avatar
    Join Date
    05-25-2010
    Location
    Manchester
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    449

    Copy a range to another sheet

    Hi,

    I am having a moment and can't work out how to copy a range to another sheet.

    The range "rng" refers to cells "e5,e7,e9,e11,e13,e15,e17,e19,e22,e24" which I have named iRNG.

    *** iSh and pSh are defined further up in the code as sheets "INPUT" and "PENDING" respectively.

    I want to cope each cell value from iRNG to the next blank row on pSh

    However this seems to copy cell iSh.range("L22") to each of the cells in the next blank row on pSh rather that each cell value from iRNG, any ideas where I am going wrong?

    Thanks

    Andy

    Dim NR As Long
    Dim NC As Long
    Dim rng As Range
    Dim i As Range
         
    Application.ScreenUpdating = False
    
    NR = pSh.Range("A" & Rows.Count).End(xlUp).Row + 1
    NC = 1
    Set rng = ish.Range("iRNG")
        
        For Each i In rng
            Cell.Select
            With Selection
                .Copy
            End With
            pSh.Cells(NR, NC).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
        NC = NC + 1
        Next i
            
    Application.ScreenUpdating = True
    Last edited by mcinnes01; 04-05-2011 at 04:06 AM.

Thread Information

Users Browsing this Thread

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

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