hide/unhide
 

I am using winxp and msoffice 2003

I need to copy a named range from wbMaster to wbUser.
In wbUser many of the columns are hidden.
Do I need to unhide these columns in order to copy/pastespecial the
range from wbMaster?
Also, wbUser has several ws that need the same range copied to from the
wbMaster.
I am thinking that there must be a way to cycle thru the ws in wbUser
and programmatically .pastespecial in each of the ws. Am I correct?
Could you help me out with the syntax please? I am thinking a loop would
do it instead of hardcoding the open/paste/close for each ws.
something like:
For Each ws in wb
ws.activate
ws.pastespecialrange
Next ws
Thanks for your time and efforts.
Joanne

hide/unhide
 

Sub ABC()
Dim rng As Range
Set rng = Worksheets("Sheet1") _
.Range("A1").CurrentRegion
rng.Copy
For Each sh In Worksheets
If sh.Name < rng.Parent.Name Then
sh.Range("B1") _
.PasteSpecial xlValues
End If
Next
End Sub

worked fine for me with hidden columns.
Windows XP, xl2000
--
Regards,
Tom Ogilvy


"Joanne" wrote:

I am using winxp and msoffice 2003

I need to copy a named range from wbMaster to wbUser.
In wbUser many of the columns are hidden.
Do I need to unhide these columns in order to copy/pastespecial the
range from wbMaster?
Also, wbUser has several ws that need the same range copied to from the
wbMaster.
I am thinking that there must be a way to cycle thru the ws in wbUser
and programmatically .pastespecial in each of the ws. Am I correct?
Could you help me out with the syntax please? I am thinking a loop would
do it instead of hardcoding the open/paste/close for each ws.
something like:
For Each ws in wb
ws.activate
ws.pastespecialrange
Next ws
Thanks for your time and efforts.
Joanne

hide/unhide
 

Thanks Tom

Tom Ogilvy wrote:

Sub ABC()
Dim rng As Range
Set rng = Worksheets("Sheet1") _
.Range("A1").CurrentRegion
rng.Copy
For Each sh In Worksheets
If sh.Name < rng.Parent.Name Then
sh.Range("B1") _
.PasteSpecial xlValues
End If
Next
End Sub

worked fine for me with hidden columns.
Windows XP, xl2000