Using Closed Workbook as Data Book
 

My problem is ... everthing opens and saves and closes. What happens is it
does not go to the next line when pasting to the worksheet. It only pastes
any further data to the same row ie. A2:AC2. So I need it to open the closed
workbook copy the source range A2:AC2, paste in the open worksheet to the
NEXT open row. Can someone help me with my code??

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Sub Copy_To_Another_Workbook()
Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("Glenns Stats.xls") Then
Set destWB = Workbooks("Glenns Stats.xls")
Else
Set destWB = Workbooks.Open("F:\FILE_CAB\FIREFFTR\Crew
Statistics\Glenns Stats.xls")
End If
Lr = LastRow(destWB.Worksheets("STATS")) + 1
Set sourceRange = ThisWorkbook.Worksheets("STATS").Range("A2:AC2")
Set destRange = destWB.Worksheets("STATS").Range("A" & Lr)
sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Using Closed Workbook as Data Book
 

Where do you copy the code ?

In a normal module ?

--

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


"DarnTootn" wrote in message ...
My problem is ... everthing opens and saves and closes. What happens is it
does not go to the next line when pasting to the worksheet. It only pastes
any further data to the same row ie. A2:AC2. So I need it to open the closed
workbook copy the source range A2:AC2, paste in the open worksheet to the
NEXT open row. Can someone help me with my code??

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Sub Copy_To_Another_Workbook()
Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("Glenns Stats.xls") Then
Set destWB = Workbooks("Glenns Stats.xls")
Else
Set destWB = Workbooks.Open("F:\FILE_CAB\FIREFFTR\Crew
Statistics\Glenns Stats.xls")
End If
Lr = LastRow(destWB.Worksheets("STATS")) + 1
Set sourceRange = ThisWorkbook.Worksheets("STATS").Range("A2:AC2")
Set destRange = destWB.Worksheets("STATS").Range("A" & Lr)
sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Using Closed Workbook as Data Book
 

Yes so far it is in a module, module2, and is run with a cmdClose radio
button on another sheet. Thank you soooo much

"Ron de Bruin" wrote:

Where do you copy the code ?

In a normal module ?

--

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


"DarnTootn" wrote in message ...
My problem is ... everthing opens and saves and closes. What happens is it
does not go to the next line when pasting to the worksheet. It only pastes
any further data to the same row ie. A2:AC2. So I need it to open the closed
workbook copy the source range A2:AC2, paste in the open worksheet to the
NEXT open row. Can someone help me with my code??

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Sub Copy_To_Another_Workbook()
Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("Glenns Stats.xls") Then
Set destWB = Workbooks("Glenns Stats.xls")
Else
Set destWB = Workbooks.Open("F:\FILE_CAB\FIREFFTR\Crew
Statistics\Glenns Stats.xls")
End If
Lr = LastRow(destWB.Worksheets("STATS")) + 1
Set sourceRange = ThisWorkbook.Worksheets("STATS").Range("A2:AC2")
Set destRange = destWB.Worksheets("STATS").Range("A" & Lr)
sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Using Closed Workbook as Data Book
 

Are you sure that the sheet is named "STATS"
and not "STATS " with a space or so that you not know ?



--

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


"DarnTootn" wrote in message ...
Yes so far it is in a module, module2, and is run with a cmdClose radio
button on another sheet. Thank you soooo much

"Ron de Bruin" wrote:

Where do you copy the code ?

In a normal module ?

--

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


"DarnTootn" wrote in message ...
My problem is ... everthing opens and saves and closes. What happens is it
does not go to the next line when pasting to the worksheet. It only pastes
any further data to the same row ie. A2:AC2. So I need it to open the closed
workbook copy the source range A2:AC2, paste in the open worksheet to the
NEXT open row. Can someone help me with my code??

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Sub Copy_To_Another_Workbook()
Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("Glenns Stats.xls") Then
Set destWB = Workbooks("Glenns Stats.xls")
Else
Set destWB = Workbooks.Open("F:\FILE_CAB\FIREFFTR\Crew
Statistics\Glenns Stats.xls")
End If
Lr = LastRow(destWB.Worksheets("STATS")) + 1
Set sourceRange = ThisWorkbook.Worksheets("STATS").Range("A2:AC2")
Set destRange = destWB.Worksheets("STATS").Range("A" & Lr)
sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Using Closed Workbook as Data Book
 

No everything is good, I checked.. I copied the code as is in the module.

"Ron de Bruin" wrote:

Are you sure that the sheet is named "STATS"
and not "STATS " with a space or so that you not know ?



--

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


"DarnTootn" wrote in message ...
Yes so far it is in a module, module2, and is run with a cmdClose radio
button on another sheet. Thank you soooo much

"Ron de Bruin" wrote:

Where do you copy the code ?

In a normal module ?

--

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


"DarnTootn" wrote in message ...
My problem is ... everthing opens and saves and closes. What happens is it
does not go to the next line when pasting to the worksheet. It only pastes
any further data to the same row ie. A2:AC2. So I need it to open the closed
workbook copy the source range A2:AC2, paste in the open worksheet to the
NEXT open row. Can someone help me with my code??

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Sub Copy_To_Another_Workbook()
Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook
Dim Lr As Long

Application.ScreenUpdating = False
If bIsBookOpen("Glenns Stats.xls") Then
Set destWB = Workbooks("Glenns Stats.xls")
Else
Set destWB = Workbooks.Open("F:\FILE_CAB\FIREFFTR\Crew
Statistics\Glenns Stats.xls")
End If
Lr = LastRow(destWB.Worksheets("STATS")) + 1
Set sourceRange = ThisWorkbook.Worksheets("STATS").Range("A2:AC2")
Set destRange = destWB.Worksheets("STATS").Range("A" & Lr)
sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub

Using Closed Workbook as Data Book
 

Now I see it you changed the function

Use A1 and not A2

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Website example is here
http://www.rondebruin.nl/copy1.htm#workbook



--

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


"Ron de Bruin" wrote in message ...
Hi DarnTootn

Send me the workbook you are using now private.
I look at it for you then

--

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


"DarnTootn" wrote in message ...
No everything is good, I checked.. I copied the code as is in the module.

"Ron de Bruin" wrote:

Are you sure that the sheet is named "STATS"
and not "STATS " with a space or so that you not know ?



--

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


"DarnTootn" wrote in message ...
Yes so far it is in a module, module2, and is run with a cmdClose radio button on another sheet. Thank you soooo much

"Ron de Bruin" wrote:

Where do you copy the code ?

In a normal module ?

--

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


"DarnTootn" wrote in message ...
My problem is ... everthing opens and saves and closes. What happens is it does not go to the next line when pasting to
the worksheet. It only pastes any further data to the same row ie. A2:AC2. So I need it to open the closed workbook copy
the source range A2:AC2, paste in the open worksheet to the NEXT open row. Can someone help me with my code??

Function bIsBookOpen(ByRef szBookName As String) As Boolean
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A2"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Sub Copy_To_Another_Workbook()
Dim sourceRange As Range
Dim destRange As Range
Dim destWB As Workbook
Dim Lr As Long
Application.ScreenUpdating = False
If bIsBookOpen("Glenns Stats.xls") Then
Set destWB = Workbooks("Glenns Stats.xls")
Else
Set destWB = Workbooks.Open("F:\FILE_CAB\FIREFFTR\Crew Statistics\Glenns Stats.xls")
End If
Lr = LastRow(destWB.Worksheets("STATS")) + 1
Set sourceRange = ThisWorkbook.Worksheets("STATS").Range("A2:AC2")
Set destRange = destWB.Worksheets("STATS").Range("A" & Lr)
sourceRange.Copy
destRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub