ClearContents on 1st sheet from 2nd sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert Christie - 20 Jan 2006 14:36 GMT

Hi

The code below works without selecting the worksheet "Monthly" & copies data
from "Data" across.

Sub Transfer_Data_to_Monthly_Sheet()

Dim wsWig As Worksheet
Dim wsMth As Worksheet
Set wsWig = Workbooks("WESTPAC.xls").Worksheets("Data")
Set wsMth = Workbooks("WESTPAC.xls").Worksheets("Monthly")

wsWig.[O32:P32].Copy
wsMth.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.Run "'WESTPAC.xls'!Both_1_Month_UP"

End Sub

This macro also works OK. but jumps across to the "Monthly" worksheet

Sub Macro7()
Application.Goto Reference:="TransStatement"
Selection.ClearContents
End Sub

How do I ClearContents of a named range "TransStatement" on the "Monthly"
sheet without selecting the "Monthly" sheet?
Can someone please help, I now just cannot get the code right.

Signature

Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro

Reply to this Message

Yngve - 20 Jan 2006 16:11 GMT

Hi Robert

Try this

Range("TransStatement").ClearContents

Regards Yngve

Reply to this Message

Robert Christie - 21 Jan 2006 04:43 GMT

Thank you Yngve

I "couldn't see the trees for the forest" :-(

My thinking was "Dim this as"- "Set this as" and placing code between a With
??? & End With.

If I may ask another two Questions:

How would you code if the "Monthly" sheet was in;

1. A seperate unopen Workbook?

2. A seperate open Workbook?

Signature

Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro SP2

> Hi Robert
>
[quoted text clipped - 3 lines]
>
> Regards Yngve

Reply to this Message

Dave Peterson - 21 Jan 2006 15:50 GMT

If you do this:

Set wsMth = Workbooks("otherworkbook.xls").Worksheets("Monthly")

You could change this:
Range("TransStatement").ClearContents
to
wsMth.range("transstatement").clearcontents
(as long as there was a range named transstatement on that worksheet.

And you'll have to open that other workbook, then use the same statements.

dim Wkbk2 as workbook
dim wsMth as worksheet

set wkbk2 = nothing
on error resume next
set wkbk2 = workbooks("otherworkbook.xls")
on error goto 0

if wkbk2 is nothing then
set wkbk2 = workbooks.open(filename:="C:\yourpath\otherworkbook.xls")
end if

Set wsMth = wkbk2.Worksheets("Monthly")
wsMth.range("transstatement").clearcontents

wkbk2.close savechanges:=true 'if you want

> Thank you Yngve
>
[quoted text clipped - 25 lines]
> >
> > Regards Yngve

Signature

Dave Peterson

Reply to this Message

Robert Christie - 22 Jan 2006 08:38 GMT

Dave

Thak you very much, greatly appreciated indeed

Signature

Thank you

Regards
Aussie Bob C.
Using Windows XP Home + Office 2003 Pro SP2

> If you do this:
>
[quoted text clipped - 54 lines]
> > >
> > > Regards Yngve

Reply to this Message