Inquiry: VBA Code in Excel

[hmp_khauff]

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

[Hercules1946]

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)

[royUK]

You don't need to select the sheet, you must tell excel which sheet though.

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

[Hercules1946]

You don't need to select the sheet, you must tell excel which sheet though.

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

[royUK]

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.

[Hercules1946]

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

[royUK]

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.

[Hercules1946]

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

[Hercules1946]

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