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! |
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 |