Paste Value into separate workbook
 

On Jun 26, 12:06*pm, "Ron de Bruin" wrote:
Maybe this will helphttp://www.rondebruin.nl/copy1.htm

See the last example
"What if the Database sheet is in another workbook"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Bull" wrote in ...
How would I take the data and send it to the sheet called log in a
whole different workbook located at c:\text.xls * ...

Right now, I have the data goign to a sheet called log in the same
workbook ...:

Sub test()
*With Worksheets("LOG")
* *.Cells(Rows.Count, 1).End(xlUp)(2).Value = _
* * * *Worksheets("Waiver Brief Sheet").Range("FULLNAME").Value
* *.Cells(Rows.Count, 2).End(xlUp)(2).Value = _
* * * *Worksheets("Waiver Brief Sheet").Range("DEPT").Value
* *.Cells(Rows.Count, 3).End(xlUp)(2).Value = _
*End With
End Sub

Thanks... bull- Hide quoted text -

- Show quoted text -

Ron, great info, I figured out how to transfer FULLNAME ; What if I
have multpile ranges to transfer i.e. FULLNAME, DEPT, etc....

Separate code for each, how?

Thanks Bull

Paste Value into separate workbook
 

I like to use a row that I hide with links to the cells

Tip: Use a row below your data with links to cells you want (=C3 in A50, =G15 in B50, ...).
You can hide this row if you want and copy a range like A50:Z50 for example

Can you work with this ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bull" wrote in message ...
On Jun 26, 12:06 pm, "Ron de Bruin" wrote:
Maybe this will helphttp://www.rondebruin.nl/copy1.htm

See the last example
"What if the Database sheet is in another workbook"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Bull" wrote in ...
How would I take the data and send it to the sheet called log in a
whole different workbook located at c:\text.xls ...

Right now, I have the data goign to a sheet called log in the same
workbook ...:

Sub test()
With Worksheets("LOG")
.Cells(Rows.Count, 1).End(xlUp)(2).Value = _
Worksheets("Waiver Brief Sheet").Range("FULLNAME").Value
.Cells(Rows.Count, 2).End(xlUp)(2).Value = _
Worksheets("Waiver Brief Sheet").Range("DEPT").Value
.Cells(Rows.Count, 3).End(xlUp)(2).Value = _
End With
End Sub

Thanks... bull- Hide quoted text -

- Show quoted text -

Ron, great info, I figured out how to transfer FULLNAME ; What if I
have multpile ranges to transfer i.e. FULLNAME, DEPT, etc....

Separate code for each, how?

Thanks Bull

Paste Value into separate workbook
 

On Jun 27, 8:50*am, "Ron de Bruin" wrote:
I like to use a row that I hide with links to the cells

Tip: Use a row below your data with links to cells you want (=C3 in A50, =G15 in B50, ...).
You can hide this row if you want and copy a range like A50:Z50 for example

Can you work with this ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Bull" wrote in ...

On Jun 26, 12:06 pm, "Ron de Bruin" wrote:





Maybe this will helphttp://www.rondebruin.nl/copy1.htm

See the last example
"What if the Database sheet is in another workbook"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Bull" wrote in ...
How would I take the data and send it to the sheet called log in a
whole different workbook located at c:\text.xls ...

Right now, I have the data goign to a sheet called log in the same
workbook ...:

Sub test()
With Worksheets("LOG")
.Cells(Rows.Count, 1).End(xlUp)(2).Value = _
Worksheets("Waiver Brief Sheet").Range("FULLNAME").Value
.Cells(Rows.Count, 2).End(xlUp)(2).Value = _
Worksheets("Waiver Brief Sheet").Range("DEPT").Value
.Cells(Rows.Count, 3).End(xlUp)(2).Value = _
End With
End Sub

Thanks... bull- Hide quoted text -

- Show quoted text -

Ron, great info, *I figured out how to transfer FULLNAME ; What if I
have multpile ranges to transfer i.e. FULLNAME, DEPT, etc....

Separate code for each, how?

Thanks Bull- Hide quoted text -

- Show quoted text -

Ok, great...Appreciate the help

Paste Value into separate workbook
 

On Jun 27, 8:50*am, "Ron de Bruin" wrote:
I like to use a row that I hide with links to the cells

Tip: Use a row below your data with links to cells you want (=C3 in A50, =G15 in B50, ...).
You can hide this row if you want and copy a range like A50:Z50 for example

Can you work with this ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Bull" wrote in ...

On Jun 26, 12:06 pm, "Ron de Bruin" wrote:





Maybe this will helphttp://www.rondebruin.nl/copy1.htm

See the last example
"What if the Database sheet is in another workbook"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Bull" wrote in ...
How would I take the data and send it to the sheet called log in a
whole different workbook located at c:\text.xls ...

Right now, I have the data goign to a sheet called log in the same
workbook ...:

Sub test()
With Worksheets("LOG")
.Cells(Rows.Count, 1).End(xlUp)(2).Value = _
Worksheets("Waiver Brief Sheet").Range("FULLNAME").Value
.Cells(Rows.Count, 2).End(xlUp)(2).Value = _
Worksheets("Waiver Brief Sheet").Range("DEPT").Value
.Cells(Rows.Count, 3).End(xlUp)(2).Value = _
End With
End Sub

Thanks... bull- Hide quoted text -

- Show quoted text -

Ron, great info, *I figured out how to transfer FULLNAME ; What if I
have multpile ranges to transfer i.e. FULLNAME, DEPT, etc....

Separate code for each, how?

Thanks Bull- Hide quoted text -

- Show quoted text -

The destination workbook is in a shared network folder, called
Log.xls. Everyone keeps trying to use the Data workbook and then an
error comes up and says "runtime error 1004" could not be found...

The code I have now is:
Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Waiver Brief Sheet Log.xls") Then
Set DestWB = Workbooks("DataLog.xls")
Else
Set DestWB = Workbooks.Open("\\Work\My Documents\My Documents
\WAIVERS\DataLog.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("Data").Range("AT1:FK1")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("LOG")

Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

DestWB.Close savechanges:=True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Any suggestions to clear the error.
Bull

Paste Value into separate workbook
 

Hi Bull

You can test with other code if the file is open

See
http://www.rondebruin.nl/exist.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Bull" wrote in message ...
On Jun 27, 8:50 am, "Ron de Bruin" wrote:
I like to use a row that I hide with links to the cells

Tip: Use a row below your data with links to cells you want (=C3 in A50, =G15 in B50, ...).
You can hide this row if you want and copy a range like A50:Z50 for example

Can you work with this ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Bull" wrote in ...

On Jun 26, 12:06 pm, "Ron de Bruin" wrote:





Maybe this will helphttp://www.rondebruin.nl/copy1.htm

See the last example
"What if the Database sheet is in another workbook"

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Bull" wrote in ...
How would I take the data and send it to the sheet called log in a
whole different workbook located at c:\text.xls ...

Right now, I have the data goign to a sheet called log in the same
workbook ...:

Sub test()
With Worksheets("LOG")
.Cells(Rows.Count, 1).End(xlUp)(2).Value = _
Worksheets("Waiver Brief Sheet").Range("FULLNAME").Value
.Cells(Rows.Count, 2).End(xlUp)(2).Value = _
Worksheets("Waiver Brief Sheet").Range("DEPT").Value
.Cells(Rows.Count, 3).End(xlUp)(2).Value = _
End With
End Sub

Thanks... bull- Hide quoted text -

- Show quoted text -

Ron, great info, I figured out how to transfer FULLNAME ; What if I
have multpile ranges to transfer i.e. FULLNAME, DEPT, etc....

Separate code for each, how?

Thanks Bull- Hide quoted text -

- Show quoted text -

The destination workbook is in a shared network folder, called
Log.xls. Everyone keeps trying to use the Data workbook and then an
error comes up and says "runtime error 1004" could not be found...

The code I have now is:
Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Waiver Brief Sheet Log.xls") Then
Set DestWB = Workbooks("DataLog.xls")
Else
Set DestWB = Workbooks.Open("\\Work\My Documents\My Documents
\WAIVERS\DataLog.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("Data").Range("AT1:FK1")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("LOG")

Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

DestWB.Close savechanges:=True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Any suggestions to clear the error.
Bull