ClearContents on 1st sheet from 2nd sheet

[Guest]

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.[O3232].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.

--
Thank you

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

 

[Yngve]

Hi Robert

Try this

Range("TransStatement").ClearContents

Regards Yngve

 

[Guest]

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?

--
Thank you

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

 

[Dave Peterson]

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

 

[Guest]

Dave

Thak you very much, greatly appreciated indeed

--
Thank you

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