Excel VBA Macro Catch 22

[John Thow]

Hi Folks,

This has got me tearing my hair out. [What's left of it....;-) ]

I have an Excel macro that copies data from one workbook to another 'history'
one. The relevant bit of the code is:-

' Open Standard Weekly Book & Copy Weekly Sheet
Workbooks("whcasht.xls").Worksheets("Weekly Sheet").Activate
ActiveSheet.Cells.Select
Application.CutCopyMode = False
Selection.Copy

' Open Backup Workbook & Paste Weekly Sheet
Workbooks(FileDateX).Worksheets("Sheet1").Activate
ActiveSheet.Paste

This works every time.

Sometimes, it is necessary to bring back the 'history' workbook and add to the
data. I thought I'd use the same code (with the workbook names changed!) to
get the data back. This results in a 1004 runtime error. 'The information
cannot be pasted because the Copy area and the Paste area are not the same
size & shape.'

So I tried this:-

' Open Backup Weekly Sheet & Copy Weekly Sheet
Workbooks(WeeklySheetName).Worksheets("Sheet1").Activate
ActiveSheet.Cells.Range("A1:K81").Select
Application.CutCopyMode = False
Selection.Copy

' Open WhCasht Spreadsheet & Paste Backup Weekly Sheet
Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
ActiveSheet.Cells.Range("A1:K81").Select
Selection.Paste

This results in a 438 runtime error on the the Selection .Paste statement.
'Object doesn't support this property or method.'

Any ideas?

TIA



--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[Don Guillett]

try pasting to just a1
BTW you don't need all those selections

' Open Backup Weekly Sheet & Copy Weekly Sheet

Workbooks(WeeklySheetName).Worksheets("Sheet1").Activate
ActiveSheet.Cells.Range("A1:K81").Copy

' Open WhCasht Spreadsheet & Paste Backup Weekly Sheet
Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
ActiveSheet.Cells.Range("A1").Paste

or if both are open this should work. Should it be "WeeklySheetName"

Workbooks(WeeklySheetName).Worksheets("Sheet1").Range("A1:K81").Copy _
Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Range("A1")

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hi Folks,

This has got me tearing my hair out. [What's left of it....;-) ]

I have an Excel macro that copies data from one workbook to another 'history'
one. The relevant bit of the code is:-

' Open Standard Weekly Book & Copy Weekly Sheet
Workbooks("whcasht.xls").Worksheets("Weekly Sheet").Activate
ActiveSheet.Cells.Select
Application.CutCopyMode = False
Selection.Copy

' Open Backup Workbook & Paste Weekly Sheet
Workbooks(FileDateX).Worksheets("Sheet1").Activate
ActiveSheet.Paste

This works every time.

Sometimes, it is necessary to bring back the 'history' workbook and add to the
data. I thought I'd use the same code (with the workbook names changed!) to
get the data back. This results in a 1004 runtime error. 'The information
cannot be pasted because the Copy area and the Paste area are not the same
size & shape.'

So I tried this:-

' Open Backup Weekly Sheet & Copy Weekly Sheet
Workbooks(WeeklySheetName).Worksheets("Sheet1").Activate
ActiveSheet.Cells.Range("A1:K81").Select
Application.CutCopyMode = False
Selection.Copy

' Open WhCasht Spreadsheet & Paste Backup Weekly Sheet
Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
ActiveSheet.Cells.Range("A1:K81").Select
Selection.Paste

This results in a 438 runtime error on the the Selection .Paste statement.
'Object doesn't support this property or method.'

Any ideas?

TIA



--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[J.E. McGimpsey]

I'd avoid selections altogether:

For posting to history:

Workbooks("whcasht.xls").Worksheets( _
"Weekly Sheet").UsedRange.Copy Destination:= _
Workbooks(FileDateX).Worksheets("Sheet1").Range("A1")

for retrieving:

Workbooks(WeeklySheetname).Worksheets( _
"Sheet1").UsedRange.Copy Destination:= _
Workbooks("Whcasht.xls").Worksheets( _
"Weekly Sheet").Range("A1")

 

[John Thow]

I'd avoid selections altogether:

For posting to history:

Workbooks("whcasht.xls").Worksheets( _
"Weekly Sheet").UsedRange.Copy Destination:= _
Workbooks(FileDateX).Worksheets("Sheet1").Range("A1")

for retrieving:

Workbooks(WeeklySheetname).Worksheets( _
"Sheet1").UsedRange.Copy Destination:= _
Workbooks("Whcasht.xls").Worksheets( _
"Weekly Sheet").Range("A1")

Hi Folks,

This has got me tearing my hair out. [What's left of it....;-) ]

[Snip]

Thanks JE & Don. That's cracked that one.

Could I ask a further favour....?

Having copied the history sheet to the live one, it retains cell references to
the history workbook. I tried to replace them in the macro, but it does
nothing. Current code is:-

Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
Cells.Replace What:=WeeklySheetName, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I could understand if it left redundant quotes or [ ] in the cell, but do
nothing???

Thanks


--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[J.E. McGimpsey]

I suspect you have an On Error Resume Next statement in your code.
The lines below give me a Run Time error.

Try replacing with a valid workbook name.

 

[Don Guillett]

What:=WeeklySheetName, ???

--
Don Guillett
SalesAid Software
(e-mail address removed)

I'd avoid selections altogether:

For posting to history:

Workbooks("whcasht.xls").Worksheets( _
"Weekly Sheet").UsedRange.Copy Destination:= _
Workbooks(FileDateX).Worksheets("Sheet1").Range("A1")

for retrieving:

Workbooks(WeeklySheetname).Worksheets( _
"Sheet1").UsedRange.Copy Destination:= _
Workbooks("Whcasht.xls").Worksheets( _
"Weekly Sheet").Range("A1")

Hi Folks,

This has got me tearing my hair out. [What's left of it....;-) ]

[Snip]

Thanks JE & Don. That's cracked that one.

Could I ask a further favour....?

Having copied the history sheet to the live one, it retains cell references to
the history workbook. I tried to replace them in the macro, but it does
nothing. Current code is:-

Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
Cells.Replace What:=WeeklySheetName, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I could understand if it left redundant quotes or [ ] in the cell, but do
nothing???

Thanks


--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[John Thow]

I suspect you have an On Error Resume Next statement in your code.
The lines below give me a Run Time error.

Try replacing with a valid workbook name.

Having copied the history sheet to the live one, it retains cell references to
the history workbook. I tried to replace them in the macro, but it does
nothing. Current code is:-

Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
Cells.Replace What:=WeeklySheetName, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I could understand if it left redundant quotes or [ ] in the cell, but do
nothing???

JE,

There's no On Error Resume in the code

WeeklySheetName is a string variable in the macro that has ActiveWorkbook.Name
passed to it when the history workbook is opened.
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[John Thow]

What:=WeeklySheetName, ???

WeeklySheetName is a string variable in the macro that has ActiveWorkbook.Name
passed to it when the history workbook is opened.

[Snip]

Thanks JE & Don. That's cracked that one.

Could I ask a further favour....?

Having copied the history sheet to the live one, it retains cell references to
the history workbook. I tried to replace them in the macro, but it does
nothing. Current code is:-

Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
Cells.Replace What:=WeeklySheetName, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I could understand if it left redundant quotes or [ ] in the cell, but do
nothing???

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[J.E. McGimpsey]

Then the Find isn't finding WeeklySheetName.

Set a breakpoint at that line and make sure that WeeklySheetName is
what you expect, and that it exists in the sheet.

If it exists, you'll get a run-time error, since []Sheet1!A! is an
invalid reference.

 

[John Thow]

Then the Find isn't finding WeeklySheetName.

Absolutely right....

Set a breakpoint at that line and make sure that WeeklySheetName is
what you expect, and that it exists in the sheet.

Done that already. The references in the workbook sheet to which the data is
being copied are to (eg) ='[WK030621.XLS]Weekly Supplies'!E15.

WeeklySheetName at the break immediately before the call to:-

Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
Cells.Replace What:=WeeklySheetName, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

comes back as (in this case) " WK030621.XLS "

If it exists, you'll get a run-time error, since []Sheet1!A! is an
invalid reference.

That's what I'd have expected. Instead, nothing gets changed.
The only thing I can think is that it's something to do with the quote marks.
But, surely, on inspection at the breakpoint, those just indicate that it's a
string??

If I got (eg) ='[]Weekly Supplies'!E15 I'd know how to fix it!

Thanks for your patience.

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[John Thow]

why do you name a variable that is to hold the workbook name with a name
like

WeeklySheetName

I only ask, because it would seem more logical if it contained the sheetname
rather than the workbook name and perhaps you are using the wrong variable.

Just a thought.

That's kind of historic, Tom. The workbook started life as a paper-based
receipts and payments record done on a weekly basis: The 'Weekly Sheet'.

No, I'm not using the wrong variable. See my earlier post. The string passed
to the call to Replace contains what I'd expect it to - the name of the
workbook the data's being pasted from. The replace just doesn't find it in
the sheet the data's been copied to, even though I can see it there.

EUREKA! Whilst writing this post, I thought about the puzzle some more.
[There are more ways of getting past a brick wall than repeatedly banging your
head on it in the hope it'll fall down.... There has to be a way round. ;-) ]

I looked at ways of editing the link, rather than the cell content and came
up with:-

' Reset Links To Remove References To Backup Weekly Sheet
Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
ActiveWorkbook.ChangeLink Name:=WeeklySheetName, NewName:= _
"Whcasht.XLS", Type:=xlExcelLinks

i.e. reset the link in the book-pasted-to to itself.

IT WORKS! )

Then the Find isn't finding WeeklySheetName.

Absolutely right....

Set a breakpoint at that line and make sure that WeeklySheetName is
what you expect, and that it exists in the sheet.

Done that already. The references in the workbook sheet to which the data is
being copied are to (eg) ='[WK030621.XLS]Weekly Supplies'!E15.

WeeklySheetName at the break immediately before the call to:-

Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
Cells.Replace What:=WeeklySheetName, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

comes back as (in this case) " WK030621.XLS "

If it exists, you'll get a run-time error, since []Sheet1!A! is an
invalid reference.

That's what I'd have expected. Instead, nothing gets changed.
The only thing I can think is that it's something to do with the quote marks.
But, surely, on inspection at the breakpoint, those just indicate that it's a
string??

If I got (eg) ='[]Weekly Supplies'!E15 I'd know how to fix it!

Thanks for your patience.

There's no On Error Resume in the code

WeeklySheetName is a string variable in the macro that has ActiveWorkbook.Name
passed to it when the history workbook is opened.

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!

 

[John Thow]

On Thu, 13 Nov 2003 12:03:46 GMT, (e-mail address removed) (John Thow) wrote:

[Snip]

Could I ask a further favour....?

Having copied the history sheet to the live one, it retains cell references to
the history workbook. I tried to replace them in the macro, but it does
nothing. Current code is:-

Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
Cells.Replace What:=WeeklySheetName, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I could understand if it left redundant quotes or [ ] in the cell, but do
nothing???

Thanks


--

Whilst replying to Tom Ogilvy, I thought some more about the problem.
[There are more ways of getting past a brick wall than repeatedly banging your
head on it in the hope it'll fall down.... There has to be a way round. ;-) ]

I looked at ways of editing the link, rather than the cell content and came
up with:-

' Reset Links To Remove References To Backup Weekly Sheet
Workbooks("Whcasht.xls").Worksheets("Weekly Sheet").Activate
ActiveWorkbook.ChangeLink Name:=WeeklySheetName, NewName:= _
"Whcasht.XLS", Type:=xlExcelLinks

i.e. reset the link in the book-pasted-to to itself.

IT WORKS! )

Thanks for all the support folks.
--
John Thow
an optimist is a guy/ that has never had/ much experience -
certain maxims of archie; Don Marquis.

To e-mail me, replace the DOTs in the Reply-To: address with dots!