Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another wb and name the target sheet (XL2000)
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another wb and name the target sheet (XL2000)
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another wb and name the target sheet (XL2000)
Ron de Bruin, yes i want copy the source sheet range and paste it in a target sheet and give that target sheet a name. Come to think of it, I guess it's not really a target but a new sheet since a target would already have a name? Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another wb and name the target sheet (XL2000)
Use this then
Sub copy_to_another_workbook() Dim sourceRange As Range Dim destSheet As Worksheet Dim destrange As Range Dim destWB As Workbook Dim wksName As Variant 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 = False Then 'do nothing Else Set sourceRange = ThisWorkbook.Sheets("DATA_TEST").Range("A1:C10") On Error Resume Next Set destSheet = destWB.Worksheets.Add destSheet.Name = wksName On Error GoTo 0 Set destrange = destSheet.Range("A1") sourceRange.Copy destrange.PasteSpecial xlPasteValues, , False, False Application.CutCopyMode = False 'destWB.Close True End If Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "robert burger" wrote in message ... Ron de Bruin, yes i want copy the source sheet range and paste it in a target sheet and give that target sheet a name. Come to think of it, I guess it's not really a target but a new sheet since a target would already have a name? Robert *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to another wb and name the target sheet (XL2000)
Ron de Bruin,
Thank you very much. Your code works perfectly! have a great weekend! Robert. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula will copy the content & format of the target cell? | Excel Discussion (Misc queries) | |||
Changing target sheet name wihtin a macro | Excel Discussion (Misc queries) | |||
HOW TO COPY XL2000 (XP) MACROS TO XL2002 (XP) | Excel Discussion (Misc queries) | |||
copy to another wb and name the target sheet (XL2000) | Excel Programming | |||
How find if target is object in Worksheet_Change (ByVal Target As.. ?) | Excel Programming |