Code with hidden flaw ERROR 400

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

Howard

Some time ago I submitted code to copy FROM book_X TOO book_y with a problem I needed help with.

Claus fleshed it out to beyond my expectations, therefore I hung his 'shingle' under the sub name. It worked just fine.

I made changes to the variable names and the workbook names to be more intitive as what this relatively simple code does.

Somewhere I have crossed an i and dotted a t. Errors out with "400"

I am suspicious of the very last line of code, but seems to read okay to me.
Any combination of select a range FROM, single cell, A1:A5 A1;A10 errors out 400even if I select a range TOO that is identical to FROM.

I would think I could select any range FROM and a single cell TOO and it should work.

The code is in "Copy WkBook FROM" sheet module.
Both workbooks are saved, Macro-enabled.xlsm.
Both workbooks are open.

Thanks.

Howard

Sub Copy_Book_To_Book_Select_Ranges()
'Claus
Dim CpyRngFrm As Range
Dim CpyRngTo As Range

Set CpyRngFrm = Application.InputBox(Prompt:="Enter a Copy FROM Range.", _
Title:="Enter Copy FROM Range", Type:=8)
If CpyRngFrm Is Nothing Then Exit Sub
MsgBox CpyRngFrm.Address

Set CpyRngTo = Application.InputBox(Prompt:="Enter a Copy TOO Range.", _
Title:="Enter Copy TOO Range", Type:=8)
If CpyRngTo Is Nothing Then Exit Sub
MsgBox CpyRngTo.Address

Workbooks("Copy WkBook TOO").Sheets("Sheet1").Range("CpyRngTo").Value = _
ThisWorkbook.Sheets("Sheet1").Range("CpyRngFrm").Value

End Sub
 
Howard,
The InputBox returns a Range object, and so your code has a fully
qualified ref to each range. Sooo...

CpyRngTo.Value = CpyRngFrom.Value

...is all you need.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Howard,

The InputBox returns a Range object, and so your code has a fully

qualified ref to each range. Sooo...



CpyRngTo.Value = CpyRngFrom.Value



..is all you need.
Click to expand...

Hi Garry,

CpyRngTo.Value = CpyRngFrm.Value

Your suggestion work well but copies to the same sheet it is copied from. Needs to go to the other workbook named Copy WkBook TOO.

The only place the other workbook is mentioned is in the last line of code. So the problem has to be there but danged if I can see why it doesn't work.

Howard
 
Hi Garry,

CpyRngTo.Value = CpyRngFrm.Value

Your suggestion work well but copies to the same sheet it is copied
from. Needs to go to the other workbook named Copy WkBook TOO.

The only place the other workbook is mentioned is in the last line of
code. So the problem has to be there but danged if I can see why it
doesn't work.

Howard
Click to expand...

This code...

Set CpyRngFrm = Application.InputBox(Prompt:="Enter a Copy FROM
Range.", _
Title:="Enter Copy FROM Range", Type:=8)
If CpyRngFrm Is Nothing Then Exit Sub

...sets a fully qualified ref to the source range. (I'd name it
rngSource) It should also hold a ref to the workbook it belongs to, and
the sheet where the selection was made.

This code...

Set CpyRngTo = Application.InputBox(Prompt:="Enter a Copy TOO Range.",
_
Title:="Enter Copy TOO Range", Type:=8)
If CpyRngTo Is Nothing Then Exit Sub

...sets a fully qualified ref to the target range. (I'd name it
rngTarget) It should also hold a ref to the workbook it belongs to, and
the sheet where the selection was made.

That precludes, then, that you only need to transfer the values between
the variables. If your target range is not in the correct workbook then
that's a user selection issue.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Howard,
The InputBox returns a Range object, and so your code has a fully
qualified ref to each range. Sooo...

CpyRngTo.Value = CpyRngFrom.Value

..is all you need.
Click to expand...

I just noticed a typo in the variable 'CpyRngFrm'! My bad as I tend to
use fully descriptive naming conventions...

Dim rngCopyFrom As Range, rngCopyTo As Range

OR as I mentioned already...

Dim rngSource As Range, rngTarget As Range

...where the variable 'prefix' refs the data type or, in this case,
object type. So just as...

Dim sText As String, lLastRow As Long, dteStart As Date
Dim vData As Variant, sngTimer1 As Single, dblTimer1 As Double
Dim curForeignAmount As Currency, iCount As Integer

...refs their respective data type, so should objects...

Dim wksSource As Worksheet, wksTarget As Worksheet '(or 'ws')
Dim wkbSource As Workbook, wkbTarget As Workbook '(or 'wb')
Dim appWD As Word.Application, appOL As Outlook.Application
Dim colCustomers As Collection, dicInventory As Dictionery

...use related prefixes (IMO)!

IMO what's important is to use what works for you, so long as it's
consistent and not cryptic to whomever follows after you with the job
of maintaing your projects. In the case of this code, there is no
copying happening and so making ref to that in the variable name
seems inappropriate to my way of thinking. Even if copying was involved
I'd handle it like this...

rngSource.Copy Destination:=rngTarget

...because it stills explains clearly where data is coming from and
where it's going to. Similarly...

rngTarget.Value = rngSource.Value

...this clearly depicts where data is coming from and where it's going.

I'm thinking anyone (even a beginner) should be able to take over my
projects and not have any problems understanding the code. (Or, at
least, that's my hope!<g>)

I'm a big fan of self-documenting code writing, but I also use comments
liberally throughout my code where needed for additional clarity (or
more detailed explanations) as to what the code is doing AND why it's
doing it a particular way.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
This code...
Set CpyRngFrm = Application.InputBox(Prompt:="Enter a Copy FROM
Range.", _
Title:="Enter Copy FROM Range", Type:=8)
If CpyRngFrm Is Nothing Then Exit Sub
Click to expand...
..sets a fully qualified ref to the source range. (I'd name it
rngSource) It should also hold a ref to the workbook it belongs to, and
the sheet where the selection was made.
Click to expand...


So it should look like this for the inputbox's...

Dim rngSource as Range
Dim rngTarget as Range

Set rngSource = Application.InputBox(Prompt:="Enter a Copy FROM Range.", _
Title:="Enter Copy FROM Range", Type:=8)
If CpyRngFrm Is Nothing Then Exit Sub


Set rngTarget = Application.InputBox(Prompt:="Enter a Copy TOO Range.", _
Title:="Enter Copy TOO Range", Type:=8)
If CpyRngTo Is Nothing Then Exit Sub

And this is for clarity of whats happening and future maintenance?
I still need to adjust something in the code to get to work, right?

I tried Ron's suggestions and trying to digest and try yours also... falling flat on my face.

Howard
 
Ok, let's take it from the top...

Firstly, move the procedure from the sheet module to a standard module.

Secondly, revise the procedure something like this...

Sub CopyWkbToWkb_SelectRanges()
'Garry (GS)
Dim rngSource As Range, rngTarget As Range, lRows&, lCols&, sPrompt$
Const sTitle$ = "Copy Book to Book"

'Select range of values to copy in source wkb
Workbooks("wkbSource").Sheets("Sheet1").Activate '//edit to suit
' ThisWorkbook.Sheets("Sheet1").Activate '//optional alternative
sPrompt = "Select the Range to Copy"
Set rngSource = Application.InputBox(Prompt:=sPrompt, _
Title:=sTitle, Type:=8)
If rngSource Is Nothing Then Exit Sub

'Select first cell of range to copy values to in target wkb
Workbooks("wkbTarget").Sheets("Sheet1").Activate '//edit to suit
sPrompt = "Select the first cell of the target Range " _
& "to copy values to"
Set rngTarget = Application.InputBox(Prompt:=sPrompt, _
Title:=sTitle, Type:=8)
If rngTarget Is Nothing Then Exit Sub

'Transfer the values to the target range
With rngSource
lRows = .Rows.Count: lCols = .Columns.Count
End With 'rngSource
rngTarget.Resize(lRows, lCols).Value = rngSource.Value
End Sub

...where each sheet is specifically active during selection. Also, only
select the 1st cell on target sheet so there's no mistake on size of
the ranges matching correctly.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ok, let's take it from the top...



Firstly, move the procedure from the sheet module to a standard module.



Secondly, revise the procedure something like this...



Sub CopyWkbToWkb_SelectRanges()

'Garry (GS)

Dim rngSource As Range, rngTarget As Range, lRows&, lCols&, sPrompt$

Const sTitle$ = "Copy Book to Book"



'Select range of values to copy in source wkb

Workbooks("wkbSource").Sheets("Sheet1").Activate '//edit to suit

' ThisWorkbook.Sheets("Sheet1").Activate '//optional alternative

sPrompt = "Select the Range to Copy"

Set rngSource = Application.InputBox(Prompt:=sPrompt, _

Title:=sTitle, Type:=8)

If rngSource Is Nothing Then Exit Sub



'Select first cell of range to copy values to in target wkb

Workbooks("wkbTarget").Sheets("Sheet1").Activate '//edit to suit

sPrompt = "Select the first cell of the target Range " _

& "to copy values to"

Set rngTarget = Application.InputBox(Prompt:=sPrompt, _

Title:=sTitle, Type:=8)

If rngTarget Is Nothing Then Exit Sub



'Transfer the values to the target range

With rngSource

lRows = .Rows.Count: lCols = .Columns.Count

End With 'rngSource

rngTarget.Resize(lRows, lCols).Value = rngSource.Value

End Sub



..where each sheet is specifically active during selection. Also, only

select the 1st cell on target sheet so there's no mistake on size of

the ranges matching correctly.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Well, that's exactly where I wanted to get to.
I love the specifically active sheet during selection. I assigned a key stroke r to the macro which makes it perfect when you are the target.sheet after a copy, hit ctrl + r and you are back to the source sheet for more copies.

The single cell selection on target sheet is great!

Sorry for my 'deer in the headlights' lack of comprehension, has to be frustrating to you.

Thanks for the total bail out on this. Your name goes on the code where ever I share it from here.

Regards,
Howard
 
Hi Howard,

Am Thu, 28 Mar 2013 17:02:41 -0700 (PDT) schrieb Howard:
Claus fleshed it out to beyond my expectations, therefore I hung his 'shingle' under the sub name. It worked just fine.
Click to expand...

you only had to look again to my posted link. Your workbook is still in
SkyDrive.


Regards
Claus Busch
 
Hi Howard,



Am Thu, 28 Mar 2013 17:02:41 -0700 (PDT) schrieb Howard:






you only had to look again to my posted link. Your workbook is still in

SkyDrive.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Hi Claus,

I did revisit that site. There was a 'Box' with my name on it and like last time when I clicked on it, it opened with what looked like some test datain the first four or five columns of a sheet. I was unable to do anythingelse with it or I just plain don't how to do anything with it. I expectedto be able to see some code and stuff.

Is there some special procedure to follow. Clicking about on things it took got a site where I would have to subscribe to site. Opening one of the other boxes I was able activate the sheet which had tabs named "mit xxx". "Mit" is German for "with" I believe.

Howard
 
Hi Howard,

Am Fri, 29 Mar 2013 02:48:40 -0700 (PDT) schrieb Howard:
I did revisit that site. There was a 'Box' with my name on it and like last time when I clicked on it, it opened with what looked like some test data in the first four or five columns of a sheet. I was unable to do anything else with it or I just plain don't how to do anything with it. I expected to be able to see some code and stuff.

Is there some special procedure to follow. Clicking about on things it took got a site where I would have to subscribe to site. Opening one of the other boxes I was able activate the sheet which had tabs named "mit xxx". "Mit" is German for "with" I believe.
Click to expand...

in SkyDrive macros are not enabled. To have all the features of the
workbook click right and download.


Regards
Claus Busch
 
in SkyDrive macros are not enabled. To have all the features of the

workbook click right and download.

Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Right click worked, I sure thought I did try that before.

Posted the code in my workbook, changed the book name to match, works perfect.

I sure am sorry for all crap I caused here, wish I had got into that SkyDrive sooner.

Thanks Claus.

Again, sorry for all the trouble I caused. And I appreciate your time and effort.

Regards,

Howard
 
Hi Howard,

Am Fri, 29 Mar 2013 03:18:02 -0700 (PDT) schrieb Howard:
Right click worked, I sure thought I did try that before.

Posted the code in my workbook, changed the book name to match, works perfect.
Click to expand...

glad to help.
In Module2 also is the code for the column letter.


Regards
Claus Busch
 
Well, that's exactly where I wanted to get to.
I love the specifically active sheet during selection. I assigned a
key stroke r to the macro which makes it perfect when you are the
target.sheet after a copy, hit ctrl + r and you are back to the
source sheet for more copies.

The single cell selection on target sheet is great!

Sorry for my 'deer in the headlights' lack of comprehension, has to
be frustrating to you.
Click to expand...

Na! What frustrates me more, sometimes, is my inability to convey my
point so as it's as easily understand as I'd like it to be. said:
Thanks for the total bail out on this.
Click to expand...

You are most welcome! Thanks for the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top