Hi Robert
Do you want to copy the cells on a new sheet and give it a name?.
In my example you must fill in a existing sheet.name
--
Regards Ron de Bruin
http://www.rondebruin.nl
"robert burger" wrote in message ...
Thanks for the reply Ron de Bruin and Bernie,
I want to apologize for not being clear on my first post.
Ron de Bruin,
I thought that my code would allow me to name the target but instead (i
think)it allows me to choose the target worksheet.
Currently, with the sub below, I run the macro I get the Inputbox, I
give it a name but when I check the target there is no data and no name
on the sheet.
Bernie,
I hope this answers your questions.
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 String
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\test_target.xls")
End If
wksName = Application.InputBox(prompt:="Copy to what sheet: ",
Type:=2)
If wksName = flase Then
'do nothing
Else
On Error GoTo eind
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 If
Exit Sub
eind:
Application.ScreenUpdating = True
End Sub
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!