copy to another wb and name the target sheet (XL2000)
 

Good morning all,
I found the following two codes and am having trouble making them both
work together.
The Sub_copy_to_another_workbook works great on its own but when i add
the option of naming the target worksheet it doesn't work.
Any suggestions?

thanks,
Robert

Sub copy_to_another_workbook()

Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long
Dim destSheet As Worksheet
Dim wksName As String

Application.ScreenUpdating = False

If bIsBookOpen("test_target.xls") Then
Set destWB = Workbooks("test_target.xls")
Else
Set destWB = Workbooks.Open("C:\Documents and
Settings\target.xls")
End If

With ActiveWorkbook
wksName = Application.InputBox(prompt:="Copy to what sheet:
", Type:=2)
Set destSheet = .Sheet(wksName)
End With

Lr = LastRow(destWB.Sheets("wksname")) + 1
Set sourceRange = ThisWorkbook.Sheets("DATA_TEST").Range("A1:C10")
Set destrange = destWB.Sheets("wksname").Range("A" & Lr)

sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub
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("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

copy to another wb and name the target sheet (XL2000)
 

Robert,

What exactly doesn't work? and how are you adding the option of naming the
target worksheet?

HTH,
Bernie
MS Excel MVP

"robert burger" wrote in message
...
Good morning all,
I found the following two codes and am having trouble making them both
work together.
The Sub_copy_to_another_workbook works great on its own but when i add
the option of naming the target worksheet it doesn't work.
Any suggestions?

thanks,
Robert

Sub copy_to_another_workbook()

Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long
Dim destSheet As Worksheet
Dim wksName As String

Application.ScreenUpdating = False

If bIsBookOpen("test_target.xls") Then
Set destWB = Workbooks("test_target.xls")
Else
Set destWB = Workbooks.Open("C:\Documents and
Settings\target.xls")
End If

With ActiveWorkbook
wksName = Application.InputBox(prompt:="Copy to what sheet:
", Type:=2)
Set destSheet = .Sheet(wksName)
End With

Lr = LastRow(destWB.Sheets("wksname")) + 1
Set sourceRange = ThisWorkbook.Sheets("DATA_TEST").Range("A1:C10")
Set destrange = destWB.Sheets("wksname").Range("A" & Lr)

sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub
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("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!