PDA

View Full Version : [SOLVED] Inquiry: VBA Code in Excel



hmp_khauff
09-11-2013, 10:01 AM
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

SamT
09-11-2013, 04:44 PM
Post the code that (almost) worked perfect

largefarva
09-11-2013, 07:45 PM
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

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


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:


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.

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