Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
Thanks to Claus for these two codes.
Normally you would have the "Copy WkBook To" open and both codes work well. I added the open "Copy WkBook To" code line to both so that you would not have to open it manually, the code would do it. Sub CopyBookToBookClaus_Rangex() works just fine. Sub CopyBookToBook2Claus_ColLetterx() opens the other workbook but does not complete the column copy after entering a destination column letter in the second InPut prompt. What am I missing here? Thanks. Howard Option Explicit Sub CopyBookToBookClaus_Rangex() Dim ColRngFrm As Range Dim ColRngTo As Range Set ColRngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _ Title:="Enter Copy from Column", Type:=8) If ColRngFrm Is Nothing Then Exit Sub Workbooks.Open Filename:= _ "C:\Users\Howard Kittle\Documents\Copy WkBook TO.xlsm" Application.Goto Workbooks("Copy WkBook To").Sheets("Sheet1").Range("A1") Set ColRngTo = Application.InputBox(Prompt:="Enter a Copy to Range.", _ Title:="Enter Copy to Column", Type:=8) If ColRngTo Is Nothing Then Exit Sub MsgBox ColRngTo.Address ColRngFrm.Copy ColRngTo End Sub Sub CopyBookToBook2Claus_ColLetterx() Dim ColRngFrm As String Dim ColRngTo As String Dim LRow As Long LRow = Cells(Rows.count, 1).End(xlUp).Row ColRngFrm = Application.InputBox(Prompt:="Enter a column letter.", _ Title:="Enter a column letter", Type:=2) If ColRngFrm = "" Or ColRngFrm = "False" Then Exit Sub Workbooks.Open Filename:= _ "C:\Users\Howard Kittle\Documents\Copy WkBook TO.xlsm" Application.Goto Workbooks("Copy WkBook To").Sheets("Sheet1").Range("A1") ColRngTo = Application.InputBox(Prompt:="Enter a column letter.", _ Title:="Enter a column letter", Type:=2) If ColRngTo = "" Or ColRngTo = "False" Then Exit Sub Range(Cells(1, ColRngFrm), Cells(LRow, ColRngFrm)).Copy _ Workbooks("Copy WkBook TO").Sheets("Sheet1").Cells(1, ColRngTo) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
Hi Howard,
Am Sun, 8 Sep 2013 09:57:41 -0700 (PDT) schrieb Howard: LRow = Cells(Rows.count, 1).End(xlUp).Row Are values in Column A?Is LRow 1? Range(Cells(1, ColRngFrm), Cells(LRow, ColRngFrm)).Copy _ You have activated Copy WkBook TO. So you have to refer to the correct workbook and wqorksheet for the copy range. Range without workbook and worksheet always works with the active sheet. The first code works with ranges referred to the correct workbook. But here you work with column numbers. So you have to refer correctly. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
On Sunday, September 8, 2013 10:20:54 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Sun, 8 Sep 2013 09:57:41 -0700 (PDT) schrieb Howard: LRow = Cells(Rows.count, 1).End(xlUp).Row Are values in Column A?Is LRow 1? Range(Cells(1, ColRngFrm), Cells(LRow, ColRngFrm)).Copy _ You have activated Copy WkBook TO. So you have to refer to the correct workbook and wqorksheet for the copy range. Range without workbook and worksheet always works with the active sheet. The first code works with ranges referred to the correct workbook. But here you work with column numbers. So you have to refer correctly. Regards Claus B. Okay, that is something I will have to study on. If I can't figure it out, I'll post back. Thanks Claus Regards, Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
Adding to Claus' sage advice, I *always* set fully qualified refs when
code acts on more than one workbook/worksheet. Typically it goes like this... Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim rngSource As Range, rngTarget As Range ...so code knows where to get/put data without having to activate anything. Using these refs is simple... Set wkbSource = Workbooks("GetData.xls") Set wkbTarget = Workbooks("PutData.xls") Set wksSource = wkbSource.Sheets("SourceData") Set wksTarget = wkbTarget.Sheets("TargetData") OR Dim wks For Each wks In wkbSource.Worksheets Set wksSource = wks Call ProcessSourceData(wksSource) Next 'wks OR For Each wks In wkbSource.Worksheets Call ProcessSourceData(wks, wksTarget) Next 'wks Sub ProcessSourceData(WksSource As Worksheet, WksTarget As Worksheet) Debug.Print WksSource.Name & ":" & WksTarget.Name End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
On Sunday, September 8, 2013 11:22:41 AM UTC-7, GS wrote:
Adding to Claus' sage advice, I *always* set fully qualified refs when code acts on more than one workbook/worksheet. Typically it goes like this... Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim rngSource As Range, rngTarget As Range ..so code knows where to get/put data without having to activate anything. Using these refs is simple... Set wkbSource = Workbooks("GetData.xls") Set wkbTarget = Workbooks("PutData.xls") Set wksSource = wkbSource.Sheets("SourceData") Set wksTarget = wkbTarget.Sheets("TargetData") OR Dim wks For Each wks In wkbSource.Worksheets Set wksSource = wks Call ProcessSourceData(wksSource) Next 'wks OR For Each wks In wkbSource.Worksheets Call ProcessSourceData(wks, wksTarget) Next 'wks Sub ProcessSourceData(WksSource As Worksheet, WksTarget As Worksheet) Debug.Print WksSource.Name & ":" & WksTarget.Name End Sub -- Garry Indeed some more study time on my part for this. I get the idea of what you offer, putting it on the canvas will take some pondering on my part for sure. That said, and given the two original codes I posted, one for a range and the other for a column, activating the Copy To workbook/sheet makes a fair amount of sense in that as you copy a range in the FROM you most likely will want to "see" where you want to paste it in the Copy To sheet. With the range copy code you do see the other sheet, with the column letter code you don't. Pasting a column to a new sheet sorta seems like you will for sure know where its going. A range, perhaps you would want to view the destination before pasting, but not always. I have to admit that when I use the column letter code where it does not show you the other sheet, I almost always go to the other sheet to verify it is correctly pasted. Okay, study time now. Thanks Garry. Regards, Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
Indeed some more study time on my part for this.
I get the idea of what you offer, putting it on the canvas will take some pondering on my part for sure. That said, and given the two original codes I posted, one for a range and the other for a column, activating the Copy To workbook/sheet makes a fair amount of sense in that as you copy a range in the FROM you most likely will want to "see" where you want to paste it in the Copy To sheet. I disagree! Mainly because this sort of process nearly always runs more efficiently when things like ScreenUpdating and Calculation are toggle off/on before/after. You can 'see' the results afterwards anyway, OR watch as you step through code. Regardless, there's no 'undo' for VBA actions unless you build that feature (extremely complex) into your project. With the range copy code you do see the other sheet, with the column letter code you don't. Pasting a column to a new sheet sorta seems like you will for sure know where its going. A range, perhaps you would want to view the destination before pasting, but not always. I have to admit that when I use the column letter code where it does not show you the other sheet, I almost always go to the other sheet to verify it is correctly pasted. Exactly my point! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
The main point IS to always, always, always use fully qualified object
refs in your code. Leaving this to VBA to assume default refs is always, always, always bad programming practice! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
On Sunday, September 8, 2013 12:30:50 PM UTC-7, GS wrote:
The main point IS to always, always, always use fully qualified object refs in your code. Leaving this to VBA to assume default refs is always, always, always bad programming practice! -- Garry Hi Garry, Here's my best shot at it. Checks to see if the wkbTarget is open, if not then it opens it. (Found that function on a google search.) Does the little song & dance For Each c In Range("C1:C" & lastrow) and copies to wkbTarget and then the Select Case save and leave open or save and close wkbTarget. Did not use these, perhaps there is a proper spot for them... Dim rngSource As Range, rngTarget As Range Code works okay, you may have some further refinements and are certainly free to add them. Regards, Howard Option Explicit Function IsFileOpen(FileName As String) Dim iFilenum As Long Dim iErr As Long On Error Resume Next iFilenum = FreeFile() Open FileName For Input Lock Read As #iFilenum Close iFilenum iErr = Err On Error GoTo 0 Select Case iErr Case 0: IsFileOpen = False Case 70: IsFileOpen = True Case Else: Error iErr End Select End Function Sub CopyBook10_BookTO() Dim c As Range Dim lastrow As Long Dim wksSource As Worksheet, wksTarget As Worksheet Dim wkbSource As Workbook, wkbTarget As Workbook Dim rngSource As Range, rngTarget As Range If Not IsFileOpen("C:\Users\Howard Kittle\Documents\Copy WkBook TO.xlsm") Then Workbooks.Open ("C:\Users\Howard Kittle\Documents\Copy WkBook TO.xlsm") End If Set wkbSource = Workbooks("Book10.xlsm") Set wkbTarget = Workbooks("Copy WkBook TO.xlsm") lastrow = Range("C" & Rows.Count).End(xlUp).Row For Each c In Range("C1:C" & lastrow) If c.Value = 1 Then c.Offset(0, -1).Copy wkbTarget.Sheets("Sheet1").Cells(Rows.Count, "A") _ .End(xlUp).Offset(1) End If Next Select Case MsgBox(Prompt:= _ " The copy is complete" & vbCr & _ " Do you want to save & close" & vbCr & _ " Copy WkBook TO" & vbCr & _ " workbook?", _ Buttons:=vbYesNoCancel) Case vbYes wkbTarget.Save wkbTarget.Close MsgBox "Okay, it is closed and has been saved." Case vbNo, vbCancel wkbTarget.Save MsgBox "Okay, it is still open and has been saved." End Select End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
Hi Howard,
Am Mon, 9 Sep 2013 01:58:14 -0700 (PDT) schrieb Howard: Set wkbSource = Workbooks("Book10.xlsm") Set wkbTarget = Workbooks("Copy WkBook TO.xlsm") lastrow = Range("C" & Rows.Count).End(xlUp).Row For Each c In Range("C1:C" & lastrow) If c.Value = 1 Then c.Offset(0, -1).Copy wkbTarget.Sheets("Sheet1").Cells(Rows.Count, "A") _ .End(xlUp).Offset(1) End If Next you haven't set the sheets. You can delete them in declaration or you try: Set wkbSource = Workbooks("Book10.xlsm") Set wkbTarget = Workbooks("Copy WkBook TO.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") With wksTarget lastrow = .Range("C" & Rows.Count).End(xlUp).Row For Each c In .Range("C1:C" & lastrow) If c.Value = 1 Then c.Offset(0, -1).Copy wksTarget.Cells(Rows.Count, "A") _ .End(xlUp).Offset(1) End If Next End With Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
On Monday, September 9, 2013 3:53:23 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Mon, 9 Sep 2013 01:58:14 -0700 (PDT) schrieb Howard: Set wkbSource = Workbooks("Book10.xlsm") Set wkbTarget = Workbooks("Copy WkBook TO.xlsm") lastrow = Range("C" & Rows.Count).End(xlUp).Row For Each c In Range("C1:C" & lastrow) If c.Value = 1 Then c.Offset(0, -1).Copy wkbTarget.Sheets("Sheet1").Cells(Rows.Count, "A") _ .End(xlUp).Offset(1) End If Next you haven't set the sheets. You can delete them in declaration or you try: Set wkbSource = Workbooks("Book10.xlsm") Set wkbTarget = Workbooks("Copy WkBook TO.xlsm") Set wksSource = wkbSource.Sheets("Sheet1") Set wksTarget = wkbTarget.Sheets("Sheet1") With wksTarget lastrow = .Range("C" & Rows.Count).End(xlUp).Row For Each c In .Range("C1:C" & lastrow) If c.Value = 1 Then c.Offset(0, -1).Copy wksTarget.Cells(Rows.Count, "A") _ .End(xlUp).Offset(1) End If Next End With Regards Claus B. Okay, plugged that in. I presume the With is a typo? With wksSource works and With wksTarget does not. With wksTarget lastrow = .Range("C" & Rows.Count).End(xlUp).Row With wksSource lastrow = .Range("C" & Rows.Count).End(xlUp).Row Howard |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
Hi Howard,
Am Mon, 9 Sep 2013 04:42:44 -0700 (PDT) schrieb Howard: Okay, plugged that in. I presume the With is a typo? With wksSource works and With wksTarget does not. With wksSource lastrow = .Range("C" & Rows.Count).End(xlUp).Row I forgot a dot in front of .Rows.Count: lastrow = .Range("C" & .Rows.Count).End(xlUp).Row Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Similar Book to Book Code, one works other does not
On Monday, September 9, 2013 5:16:17 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Mon, 9 Sep 2013 04:42:44 -0700 (PDT) schrieb Howard: Okay, plugged that in. I presume the With is a typo? With wksSource works and With wksTarget does not. With wksSource lastrow = .Range("C" & Rows.Count).End(xlUp).Row I forgot a dot in front of .Rows.Count: lastrow = .Range("C" & .Rows.Count).End(xlUp).Row Regards Claus B. Okay, that was lost on me, all works fine and dandy. Thanks Claus. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem: Closing one book causes the 2nd book freeze | Excel Programming | |||
copy worksheets to new book without linking to original book | Excel Discussion (Misc queries) | |||
to disconnect a destination book from a source book | Excel Discussion (Misc queries) | |||
'BeforeClose' code problems:book won't close if more than one book is open | Excel Programming | |||
Open book, check for macros, close book | Excel Programming |