Results 1 to 9 of 9

Thread: Inquiry: VBA Code in Excel

  1. #1

    Inquiry: VBA Code in Excel



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone,

    I have a form in the sheet1 where I have fields that are filled out with vlookup function. I want to copy or save the value of the fields in a row. The code worked perfect, but the only thing it is copying in the same sheet where the fields are located. I want to copy and paste the values in the Sheet2 instead of Sheet1. I tried to change ActiveSheet.Cells to Sheet2.Cells, but it does not work. Can you help me on this?

    Public Sub PasteData()
    Dim PCount As Integer

    Cells(1, 2).Select

    PCount = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

    Cells(PCount, 1).Value = "Par1"
    Cells(PCount, 2).Value = "Par2"
    Cells(PCount, 3).Value = "Par3"
    Cells(PCount, 4).Value = "Par4"
    Cells(PCount, 5).Value = "Par5"

    End Sub

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Hello
    The problem is your not selecting a sheet at any stage so everything will happen in the currently selected sheet.
    To make the sub enter the values in sheet2, after the Dim statement insert:

    Sheets("Sheet2").Select


    HTH

    Hercules

    Also make sure that the code is in a module rather than an individual sheet (an easy mistake to make)

  3. #3
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    You don't need to select the sheet, you must tell excel which sheet though.

    Code:
    Option Explicit
    Public Sub PasteData()
        Dim PCount As Integer
    
    
        With sheet2
    
    
            PCount = .Cells(Rows.Count, "A").End(xlUp).Row + 1
            'note using a With Statement the cell references are qualified with .
            'e.g. .Range("A1")
            .Cells(PCount, 1).Value = "Par1"
            .Cells(PCount, 2).Value = "Par2"
            .Cells(PCount, 3).Value = "Par3"
            .Cells(PCount, 4).Value = "Par4"
            .Cells(PCount, 5).Value = "Par5"
        End If
    End Sub
    Hope that helps

    Roy

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by royUK View Post
    You don't need to select the sheet, you must tell excel which sheet though.

    Code:
    Option Explicit
    Public Sub PasteData()
        Dim PCount As Integer
    
    
        With sheet2
    
    
            PCount = .Cells(Rows.Count, "A").End(xlUp).Row + 1
            'note using a With Statement the cell references are qualified with .
            'e.g. .Range("A1")
            .Cells(PCount, 1).Value = "Par1"
            .Cells(PCount, 2).Value = "Par2"
            .Cells(PCount, 3).Value = "Par3"
            .Cells(PCount, 4).Value = "Par4"
            .Cells(PCount, 5).Value = "Par5"
        End If
    End Sub
    A good alternative, but the OP describes the problem as being with Cut and Paste, so I went with Sheet.Select

  5. #5
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    The code posted actually only puts Strings intio the cells. Are these supposed to be named Ranges?

    Cut and paste or copy, the code actually does neither! Whatever the intention it is still not necessary to select the sheet, which is what I demonstrated.
    Hope that helps

    Roy

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by royUK View Post
    The code posted actually only puts Strings intio the cells. Are these supposed to be named Ranges?

    Cut and paste or copy, the code actually does neither! Whatever the intention it is still not necessary to select the sheet, which is what I demonstrated.
    Hello Roy
    My interpretation (based on the narrative in the OP) is that this code was a digression to try and work out why it wouldn't output to Sheet2, and has no standalone purpose beyond this. My intention was simply to point out that this was probably because Sheet2 was not the active sheet, although perhaps I should have worded it differently.

    Sorry for any confusion.

    Cheers

    Hercules

  7. #7
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    I didn't disagree with you about the problem. I just pointed out that selecting the sheet wasn't even necessary.

    It was only looking at it again that I realised it was only using Strings so not even copying or cutting.

    How's York today anyway? My son is up there with the army for a couple of years.
    Hope that helps

    Roy

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Its a bit cloudy today. Just been out with my daughter whos learning to drive. She persuaded me that I might need her to drive me and the missus around when we are too old in a few years!




    Hercules

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Hello Hmp_Khauff
    If (as I understand it) you want to copy data from cells in Sheet1 to cells in Sheet2, then you can do it as follows with a single code statement
    It copies 4 cells from Sheet1 to the same location in Sheet2.

    Sub CopyPaste()
    ' This assumes that the active Workbook is the
    ' one your wanting to update.
    Sheet1.Range("C11:F11").Copy Sheet2.Range("C11")
    End Sub

Posting Permissions

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