Using Closed Workbook as Data Book

D

DarnTootn

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
 
D

DarnTootn

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

Ron de Bruin

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

Ron de Bruin

Hi DarnTootn

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

Ron de Bruin

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top