Why does ThisWorkbook.Sheets.Range.Clearcontens give Application-defined or Object-defined error? [duplicate]

up vote
2
down vote

favorite

This question already has an answer here:

Excel VBA, getting range from an inactive sheet

3 answers

I wonder why the following line gave

"Application-defined or Object-defined error"

If Sheet6 is selected, this line runs fine - no error message. However, if another sheet is selected, Excel throws the above error message.

ThisWorkbook.Sheets("Sheet6").Range(Cells(1, 2), Cells(12, 1000).End(xlToLeft)).ClearContents

edited Nov 8 at 23:32

K.Dᴀᴠɪs

5,976102140

asked Nov 8 at 23:18

joehua

123111

marked as duplicate by chris neilsen excel
Users with the excel badge can single-handedly close excel questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Nov 9 at 0:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

up vote
2
down vote

favorite

This question already has an answer here:

Excel VBA, getting range from an inactive sheet

3 answers

I wonder why the following line gave

"Application-defined or Object-defined error"

If Sheet6 is selected, this line runs fine - no error message. However, if another sheet is selected, Excel throws the above error message.

ThisWorkbook.Sheets("Sheet6").Range(Cells(1, 2), Cells(12, 1000).End(xlToLeft)).ClearContents

edited Nov 8 at 23:32

K.Dᴀᴠɪs

5,976102140

asked Nov 8 at 23:18

joehua

123111

marked as duplicate by chris neilsen excel
Users with the excel badge can single-handedly close excel questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Nov 9 at 0:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

up vote
2
down vote

favorite

This question already has an answer here:

Excel VBA, getting range from an inactive sheet

3 answers

I wonder why the following line gave

"Application-defined or Object-defined error"

If Sheet6 is selected, this line runs fine - no error message. However, if another sheet is selected, Excel throws the above error message.

ThisWorkbook.Sheets("Sheet6").Range(Cells(1, 2), Cells(12, 1000).End(xlToLeft)).ClearContents

edited Nov 8 at 23:32

K.Dᴀᴠɪs

5,976102140

asked Nov 8 at 23:18

joehua

123111

This question already has an answer here:

Excel VBA, getting range from an inactive sheet

3 answers

I wonder why the following line gave

"Application-defined or Object-defined error"

If Sheet6 is selected, this line runs fine - no error message. However, if another sheet is selected, Excel throws the above error message.

ThisWorkbook.Sheets("Sheet6").Range(Cells(1, 2), Cells(12, 1000).End(xlToLeft)).ClearContents

This question already has an answer here:

Excel VBA, getting range from an inactive sheet

3 answers

excel vba excel-vba

edited Nov 8 at 23:32

K.Dᴀᴠɪs

5,976102140

asked Nov 8 at 23:18

joehua

123111

edited Nov 8 at 23:32

K.Dᴀᴠɪs

5,976102140

asked Nov 8 at 23:18

joehua

123111

edited Nov 8 at 23:32

K.Dᴀᴠɪs

5,976102140

edited Nov 8 at 23:32

K.Dᴀᴠɪs

5,976102140

edited Nov 8 at 23:32

K.Dᴀᴠɪs

5,976102140

asked Nov 8 at 23:18

joehua

123111

asked Nov 8 at 23:18

joehua

123111

asked Nov 8 at 23:18

joehua

123111

marked as duplicate by chris neilsen excel
Users with the excel badge can single-handedly close excel questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Nov 9 at 0:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

marked as duplicate by chris neilsen excel
Users with the excel badge can single-handedly close excel questions as duplicates and reopen them as needed.

StackExchange.ready(function()
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function()
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function()
$hover.showInfoMessage('',
messageElement: $msg.clone().show(),
transient: false,
position: my: 'bottom left', at: 'top center', offsetTop: -7 ,
dismissable: false,
relativeToBody: true
);
,
function()
StackExchange.helpers.removeMessages();

);
);
);
Nov 9 at 0:19

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

1 Answer
1

active

oldest

votes

up vote
4
down vote

accepted

You need to qualify all your Range objects with your Worksheet.

With ThisWorkbook.Worksheets("Sheet6")
 .Range(.Cells(1, 2), .Cells(12, 1000).End(xlToLeft)).ClearContents
End With

You didn't qualify Cells() with your worksheet, so it pulls from the ActiveSheet instead of Worksheets("Sheet6").

So, remember that when your thinking "It only works when it's on that sheet" then this is going to be the issue.

edited Nov 8 at 23:25

answered Nov 8 at 23:20

K.Dᴀᴠɪs

5,976102140

Thanks. Now that you pointed it out, I remember I had a similar problem few years back. I had solved the problem by qualifying Cells but the solution escaped me this time.
– joehua
Nov 9 at 0:25

add a comment |

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
4
down vote

accepted

You need to qualify all your Range objects with your Worksheet.

With ThisWorkbook.Worksheets("Sheet6")
 .Range(.Cells(1, 2), .Cells(12, 1000).End(xlToLeft)).ClearContents
End With

You didn't qualify Cells() with your worksheet, so it pulls from the ActiveSheet instead of Worksheets("Sheet6").

So, remember that when your thinking "It only works when it's on that sheet" then this is going to be the issue.

edited Nov 8 at 23:25

answered Nov 8 at 23:20

K.Dᴀᴠɪs

5,976102140

Thanks. Now that you pointed it out, I remember I had a similar problem few years back. I had solved the problem by qualifying Cells but the solution escaped me this time.
– joehua
Nov 9 at 0:25

add a comment |

up vote
4
down vote

accepted

You need to qualify all your Range objects with your Worksheet.

With ThisWorkbook.Worksheets("Sheet6")
 .Range(.Cells(1, 2), .Cells(12, 1000).End(xlToLeft)).ClearContents
End With

You didn't qualify Cells() with your worksheet, so it pulls from the ActiveSheet instead of Worksheets("Sheet6").

So, remember that when your thinking "It only works when it's on that sheet" then this is going to be the issue.

edited Nov 8 at 23:25

answered Nov 8 at 23:20

K.Dᴀᴠɪs

5,976102140

Thanks. Now that you pointed it out, I remember I had a similar problem few years back. I had solved the problem by qualifying Cells but the solution escaped me this time.
– joehua
Nov 9 at 0:25

add a comment |

up vote
4
down vote

accepted

You need to qualify all your Range objects with your Worksheet.

With ThisWorkbook.Worksheets("Sheet6")
 .Range(.Cells(1, 2), .Cells(12, 1000).End(xlToLeft)).ClearContents
End With

You didn't qualify Cells() with your worksheet, so it pulls from the ActiveSheet instead of Worksheets("Sheet6").

So, remember that when your thinking "It only works when it's on that sheet" then this is going to be the issue.

edited Nov 8 at 23:25

answered Nov 8 at 23:20

K.Dᴀᴠɪs

5,976102140

You need to qualify all your Range objects with your Worksheet.

With ThisWorkbook.Worksheets("Sheet6")
 .Range(.Cells(1, 2), .Cells(12, 1000).End(xlToLeft)).ClearContents
End With

You didn't qualify Cells() with your worksheet, so it pulls from the ActiveSheet instead of Worksheets("Sheet6").

So, remember that when your thinking "It only works when it's on that sheet" then this is going to be the issue.

edited Nov 8 at 23:25

answered Nov 8 at 23:20

K.Dᴀᴠɪs

5,976102140

edited Nov 8 at 23:25

answered Nov 8 at 23:20

K.Dᴀᴠɪs

5,976102140

answered Nov 8 at 23:20

K.Dᴀᴠɪs

5,976102140

answered Nov 8 at 23:20

K.Dᴀᴠɪs

5,976102140

Thanks. Now that you pointed it out, I remember I had a similar problem few years back. I had solved the problem by qualifying Cells but the solution escaped me this time.
– joehua
Nov 9 at 0:25

add a comment |

Thanks. Now that you pointed it out, I remember I had a similar problem few years back. I had solved the problem by qualifying Cells but the solution escaped me this time.
– joehua
Nov 9 at 0:25

Thanks. Now that you pointed it out, I remember I had a similar problem few years back. I had solved the problem by qualifying Cells but the solution escaped me this time.
– joehua
Nov 9 at 0:25

add a comment |

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Dfyjkt