Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
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) | |||
How find if target is object in Worksheet_Change (ByVal Target As.. ?) | Excel Programming |