Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Copy Paste from within FUNCTION

  1. #1
    Join Date
    21st June 2011
    Posts
    2

    Copy Paste from within FUNCTION

    Hi All,

    Does anyone know of how to copy / paste cells from within a function (as opposed to a sub)?

    I have the following function:
    Function KMLMoveRight()

    Range("H6:H15").Copy
    Range("I6").Select

    Selection.PasteSpecial Paste:=xlPasteValues

    Range("I6").Select

    Application.CutCopyMode = False

    KMLMoveRight = "Done"


    End Function

    Which does not work, nor does placing the contents in a sub and calling the sub from the function. The sub will run, but not perform the actual copy / paste.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th August 2011
    Posts
    10

    Re: Copy Paste from within FUNCTION

    I am not the world's foremost authority in VBA but I thought I'd try and and help as I came across a similar issue a while ago. The problem is that the purpose of functions is strictly to return values and cannot be used to make changes on the spreadsheet. The same rule is then applied to any sub called from within that function.

    Depending on what you need to do this for, it may be worth using one of the worksheet events or simply adding a command button object to the worksheet and running your code from an event of that.

    Eg: If you wanted the cells to only copy when a user types "Copy" into cell A1 you could use (in the ThisWorkbook module):
    VB:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
         ' VBA is case sensitive, so use UCase to convert string to upper case if _
         '    you want to avoid case sensitivity
        If UCase(Cells(1, 1)) = "COPY" Then 
             'do stuff
        End If 
    End Sub 
    
    
    There are other/better ways but this seems to me to be the closest solution to how you were trying to do this.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    21st June 2011
    Posts
    2

    Re: Copy Paste from within FUNCTION

    Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 3
    Last Post: December 8th, 2010, 22:44
  2. Copy/Paste Result Of Formula/Function
    By macro blues in forum EXCEL HELP
    Replies: 2
    Last Post: May 20th, 2008, 16:00
  3. copy and paste function
    By Susan Soo in forum EXCEL HELP
    Replies: 4
    Last Post: March 7th, 2005, 11:23
  4. Copy and Paste issue - Using workbook function
    By james_skeggs in forum EXCEL HELP
    Replies: 6
    Last Post: November 11th, 2004, 23:50

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
  •  
porno