Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem: Closing one book causes the 2nd book freeze fred Excel Programming 0 June 15th 09 03:09 AM
copy worksheets to new book without linking to original book Lori Excel Discussion (Misc queries) 2 March 4th 09 05:46 PM
to disconnect a destination book from a source book officegirl Excel Discussion (Misc queries) 4 December 10th 07 10:28 PM
'BeforeClose' code problems:book won't close if more than one book is open Ed from AZ Excel Programming 0 September 18th 07 03:59 PM
Open book, check for macros, close book Robin Hammond[_2_] Excel Programming 5 March 31st 05 06:09 PM


All times are GMT +1. The time now is 11:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"