Inquiry: VBA Code in Excel

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?

Code:

---

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

---

Post the code that (almost) worked perfect

Quote:

---

Originally Posted by 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?

Code:

---

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

---

---

If you want to copy an entire row, you can do that and specify where to copy it to:

Code:

---

Worksheets("Sheet1").Range("A1").EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A1")

---

The above works fine when you have just one row to copy and are pasting into a blank sheet. If it's unclear where the last used row of Sheet2 is, this would work better:

Code:

---

dim firstblank as long
firstblank = Worksheets("Sheet2").Range("A65536").end(xlup).row + 1
Worksheets("Sheet1").Range("A1").EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & firstblank)

---

Hope that gets you pointed in the right direction.

Code:

---

Public Sub M-snb()
    sheet2.Cells(rows.count,1).end(xlup).offset(1).resize(,5)= activesheet.cells(2,1).resize(,5).value
End Sub

---