Referencing A Workbook
 

I have a command button that works well but I've decided I do not want my
form and my Log in the same workbook. How can I change this code to paste
the information into a different workbook named 'Void Log' rather than the
sheet named "Void Log". Also, I can't get my paste special to work
correctly. I only want the values to paste and not the formats or formulas.
I'm using Excel 2003 and this code is letting me paste multiple cells from
one worksheet (my Void Form) into the next blank row of another worksheet (my
Void Log).

With Sheets("Void Log")
Set myDest = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With ActiveSheet
.Range("a3").Copy
Paste (myDest)
.Range("b5").Copy
Paste (myDest.Offset(0, 1))
.Range("c8").Copy
Paste (myDest.Offset(0, 2))
.Range("b10").Copy
Paste (myDest.Offset(0, 3))
.Range("A3").Activate
End With

Any help would be appreciated.

Referencing A Workbook
 

Check out the below code..Mention the sheet name for the workbook Void Log.
The default is given as Sheet1.

Sub Macro()

Dim ws As Worksheet

Set ws = ActiveSheet
With Workbooks("Void Log.xls").Sheets("Sheet1")
Set mydest = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

mydest.Value = ws.Range("a3").Value
mydest.Offset(0, 1) = ws.Range("b5").Value
mydest.Offset(0, 2) = ws.Range("c8").Value
mydest.Offset(0, 3) = ws.Range("b10").Value

End Sub

--
Jacob


"Axanamoon" wrote:

I have a command button that works well but I've decided I do not want my
form and my Log in the same workbook. How can I change this code to paste
the information into a different workbook named 'Void Log' rather than the
sheet named "Void Log". Also, I can't get my paste special to work
correctly. I only want the values to paste and not the formats or formulas.
I'm using Excel 2003 and this code is letting me paste multiple cells from
one worksheet (my Void Form) into the next blank row of another worksheet (my
Void Log).

With Sheets("Void Log")
Set myDest = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With ActiveSheet
.Range("a3").Copy
Paste (myDest)
.Range("b5").Copy
Paste (myDest.Offset(0, 1))
.Range("c8").Copy
Paste (myDest.Offset(0, 2))
.Range("b10").Copy
Paste (myDest.Offset(0, 3))
.Range("A3").Activate
End With

Any help would be appreciated.

Referencing A Workbook
 

It works perfect, thank you so much. You don't know how much easier my job
is going to be now.

"Jacob Skaria" wrote:

Check out the below code..Mention the sheet name for the workbook Void Log.
The default is given as Sheet1.

Sub Macro()

Dim ws As Worksheet

Set ws = ActiveSheet
With Workbooks("Void Log.xls").Sheets("Sheet1")
Set mydest = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

mydest.Value = ws.Range("a3").Value
mydest.Offset(0, 1) = ws.Range("b5").Value
mydest.Offset(0, 2) = ws.Range("c8").Value
mydest.Offset(0, 3) = ws.Range("b10").Value

End Sub

--
Jacob


"Axanamoon" wrote:

I have a command button that works well but I've decided I do not want my
form and my Log in the same workbook. How can I change this code to paste
the information into a different workbook named 'Void Log' rather than the
sheet named "Void Log". Also, I can't get my paste special to work
correctly. I only want the values to paste and not the formats or formulas.
I'm using Excel 2003 and this code is letting me paste multiple cells from
one worksheet (my Void Form) into the next blank row of another worksheet (my
Void Log).

With Sheets("Void Log")
Set myDest = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With ActiveSheet
.Range("a3").Copy
Paste (myDest)
.Range("b5").Copy
Paste (myDest.Offset(0, 1))
.Range("c8").Copy
Paste (myDest.Offset(0, 2))
.Range("b10").Copy
Paste (myDest.Offset(0, 3))
.Range("A3").Activate
End With

Any help would be appreciated.