Similar Book to Book Code, one works other does not

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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
 
Hi Howard,

Am Sun, 8 Sep 2013 09:57:41 -0700 (PDT) schrieb Howard:
LRow = Cells(Rows.count, 1).End(xlUp).Row
Click to expand...

Are values in Column A?Is LRow > 1?
Range(Cells(1, ColRngFrm), Cells(LRow, ColRngFrm)).Copy _
Click to expand...

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.
 
Hi Howard,



Am Sun, 8 Sep 2013 09:57:41 -0700 (PDT) schrieb Howard:






Are values in Column A?Is LRow > 1?






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.
Click to expand...

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
 
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
 
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
Click to expand...

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 lettercode you don't. Pasting a column to a new sheet sorta seems like you willfor 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
 
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.
Click to expand...

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.
Click to expand...

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
 
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
 
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!
Click to expand...

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
 
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
Click to expand...

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.
 
Hi Howard,



Am Mon, 9 Sep 2013 01:58:14 -0700 (PDT) schrieb Howard:








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.
Click to expand...

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
 
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
Click to expand...

I forgot a dot in front of .Rows.Count:
lastrow = .Range("C" & .Rows.Count).End(xlUp).Row


Regards
Claus B.
 
Hi Howard,



Am Mon, 9 Sep 2013 04:42:44 -0700 (PDT) schrieb Howard:








I forgot a dot in front of .Rows.Count:

lastrow = .Range("C" & .Rows.Count).End(xlUp).Row





Regards

Claus B.
Click to expand...

Okay, that was lost on me, all works fine and dandy.

Thanks Claus.

Regards,
Howard
 
Back
Top