[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Re: Macro Problem

Don Guillett

12/15/2006 4:28:00 PM

Didn't have time to test any of this as you need to clarify your request.
Sub Macro2()
'Application.ScreenUpdating = False
Range("F3:F52").Copy Range("H3")
Columns("H:H").Insert Shift:=xlToRight
Range("F3:F52").ClearContents

'what about this line for each sheet
Sheets("Sheet1").Range("A2:C26").ClearContents
application.goto Sheets("Sheet2").Range("F3")

' Application.ScreenUpdating = True
End Sub
for all ws

Sub Macro2()
for each ws in worksheets
with ws
.Range("F3:F52").Copy .Range("H3")
.Columns("H:H").Insert Shift:=xlToRight
.Range("F3:F52").ClearContents

'what about this line for each sheet
Sheets("Sheet1").Range("A2:C26").ClearContents
application.goto Sheets("Sheet2").Range("F3")

end with
next ws
End Sub


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Hazel" <Hazel@discussions.microsoft.com> wrote in message
news:71005897-1054-4923-A7D2-701D8413AB45@microsoft.com...
> Hi All you experts out there,
>
> I have a little macro code below which runs on Sheet1 of my workbook -- as
> I
> have 26 sheets in the workbook when I run the macro - Ineed it to run on
> every sheet not just on Sheet 1 -- how would I alter the macro. When you
> read
> the code you will probably come up with something much better and easier.
>
> Sub Macro2()
> Application.ScreenUpdating = False
> Range("F3:F52").Select
> Selection.Copy
> Range("H3").Select
> ActiveSheet.Paste
> Columns("H:H").Select
> Application.CutCopyMode = False
> Selection.Insert Shift:=xlToRight
> Range("F3:F52").Select
> Selection.ClearContents
> Sheets("Sheet1").Select
> Range("A2:C26").Select
> Selection.ClearContents
> Sheets("Sheet2").Select
> Range("F3").Select
> Application.ScreenUpdating = True
> End Sub
>
> --
> Many thanks
>
> hazel


4 Answers

Hazel

12/16/2006 8:59:00 AM

0

Hi Gentlemen

Sorry about the delay in replying we had a power failure yesterday for about
4 hours -- Everything works OK when we run the revised macro - however I
dropped a clanger we have one sheet in the workbook named "Account" and we
don't want the macro to run on that sheet as it collates the data from all
the other sheets in the workbook - is it possible for that problem to be
solved within the current macro.
--
Many thanks

hazel


"Don Guillett" wrote:

> Didn't have time to test any of this as you need to clarify your request.
> Sub Macro2()
> 'Application.ScreenUpdating = False
> Range("F3:F52").Copy Range("H3")
> Columns("H:H").Insert Shift:=xlToRight
> Range("F3:F52").ClearContents
>
> 'what about this line for each sheet
> Sheets("Sheet1").Range("A2:C26").ClearContents
> application.goto Sheets("Sheet2").Range("F3")
>
> ' Application.ScreenUpdating = True
> End Sub
> for all ws
>
> Sub Macro2()
> for each ws in worksheets
> with ws
> .Range("F3:F52").Copy .Range("H3")
> .Columns("H:H").Insert Shift:=xlToRight
> .Range("F3:F52").ClearContents
>
> 'what about this line for each sheet
> Sheets("Sheet1").Range("A2:C26").ClearContents
> application.goto Sheets("Sheet2").Range("F3")
>
> end with
> next ws
> End Sub
>
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Hazel" <Hazel@discussions.microsoft.com> wrote in message
> news:71005897-1054-4923-A7D2-701D8413AB45@microsoft.com...
> > Hi All you experts out there,
> >
> > I have a little macro code below which runs on Sheet1 of my workbook -- as
> > I
> > have 26 sheets in the workbook when I run the macro - Ineed it to run on
> > every sheet not just on Sheet 1 -- how would I alter the macro. When you
> > read
> > the code you will probably come up with something much better and easier.
> >
> > Sub Macro2()
> > Application.ScreenUpdating = False
> > Range("F3:F52").Select
> > Selection.Copy
> > Range("H3").Select
> > ActiveSheet.Paste
> > Columns("H:H").Select
> > Application.CutCopyMode = False
> > Selection.Insert Shift:=xlToRight
> > Range("F3:F52").Select
> > Selection.ClearContents
> > Sheets("Sheet1").Select
> > Range("A2:C26").Select
> > Selection.ClearContents
> > Sheets("Sheet2").Select
> > Range("F3").Select
> > Application.ScreenUpdating = True
> > End Sub
> >
> > --
> > Many thanks
> >
> > hazel
>
>
>

Don Guillett

12/16/2006 1:36:00 PM

0

if ws.name<>"Account" then

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Hazel" <Hazel@discussions.microsoft.com> wrote in message
news:3CE53313-63A2-4240-84A3-1343989FB676@microsoft.com...
> Hi Gentlemen
>
> Sorry about the delay in replying we had a power failure yesterday for
> about
> 4 hours -- Everything works OK when we run the revised macro - however I
> dropped a clanger we have one sheet in the workbook named "Account" and we
> don't want the macro to run on that sheet as it collates the data from all
> the other sheets in the workbook - is it possible for that problem to be
> solved within the current macro.
> --
> Many thanks
>
> hazel
>
>
> "Don Guillett" wrote:
>
>> Didn't have time to test any of this as you need to clarify your
>> request.
>> Sub Macro2()
>> 'Application.ScreenUpdating = False
>> Range("F3:F52").Copy Range("H3")
>> Columns("H:H").Insert Shift:=xlToRight
>> Range("F3:F52").ClearContents
>>
>> 'what about this line for each sheet
>> Sheets("Sheet1").Range("A2:C26").ClearContents
>> application.goto Sheets("Sheet2").Range("F3")
>>
>> ' Application.ScreenUpdating = True
>> End Sub
>> for all ws
>>
>> Sub Macro2()
>> for each ws in worksheets
>> with ws
>> .Range("F3:F52").Copy .Range("H3")
>> .Columns("H:H").Insert Shift:=xlToRight
>> .Range("F3:F52").ClearContents
>>
>> 'what about this line for each sheet
>> Sheets("Sheet1").Range("A2:C26").ClearContents
>> application.goto Sheets("Sheet2").Range("F3")
>>
>> end with
>> next ws
>> End Sub
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Hazel" <Hazel@discussions.microsoft.com> wrote in message
>> news:71005897-1054-4923-A7D2-701D8413AB45@microsoft.com...
>> > Hi All you experts out there,
>> >
>> > I have a little macro code below which runs on Sheet1 of my workbook --
>> > as
>> > I
>> > have 26 sheets in the workbook when I run the macro - Ineed it to run
>> > on
>> > every sheet not just on Sheet 1 -- how would I alter the macro. When
>> > you
>> > read
>> > the code you will probably come up with something much better and
>> > easier.
>> >
>> > Sub Macro2()
>> > Application.ScreenUpdating = False
>> > Range("F3:F52").Select
>> > Selection.Copy
>> > Range("H3").Select
>> > ActiveSheet.Paste
>> > Columns("H:H").Select
>> > Application.CutCopyMode = False
>> > Selection.Insert Shift:=xlToRight
>> > Range("F3:F52").Select
>> > Selection.ClearContents
>> > Sheets("Sheet1").Select
>> > Range("A2:C26").Select
>> > Selection.ClearContents
>> > Sheets("Sheet2").Select
>> > Range("F3").Select
>> > Application.ScreenUpdating = True
>> > End Sub
>> >
>> > --
>> > Many thanks
>> >
>> > hazel
>>
>>
>>


Hazel

12/16/2006 2:09:00 PM

0

Hi Don

Works perfectly thanks so much for all your help
--
Many thanks

hazel


"Don Guillett" wrote:

> if ws.name<>"Account" then
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Hazel" <Hazel@discussions.microsoft.com> wrote in message
> news:3CE53313-63A2-4240-84A3-1343989FB676@microsoft.com...
> > Hi Gentlemen
> >
> > Sorry about the delay in replying we had a power failure yesterday for
> > about
> > 4 hours -- Everything works OK when we run the revised macro - however I
> > dropped a clanger we have one sheet in the workbook named "Account" and we
> > don't want the macro to run on that sheet as it collates the data from all
> > the other sheets in the workbook - is it possible for that problem to be
> > solved within the current macro.
> > --
> > Many thanks
> >
> > hazel
> >
> >
> > "Don Guillett" wrote:
> >
> >> Didn't have time to test any of this as you need to clarify your
> >> request.
> >> Sub Macro2()
> >> 'Application.ScreenUpdating = False
> >> Range("F3:F52").Copy Range("H3")
> >> Columns("H:H").Insert Shift:=xlToRight
> >> Range("F3:F52").ClearContents
> >>
> >> 'what about this line for each sheet
> >> Sheets("Sheet1").Range("A2:C26").ClearContents
> >> application.goto Sheets("Sheet2").Range("F3")
> >>
> >> ' Application.ScreenUpdating = True
> >> End Sub
> >> for all ws
> >>
> >> Sub Macro2()
> >> for each ws in worksheets
> >> with ws
> >> .Range("F3:F52").Copy .Range("H3")
> >> .Columns("H:H").Insert Shift:=xlToRight
> >> .Range("F3:F52").ClearContents
> >>
> >> 'what about this line for each sheet
> >> Sheets("Sheet1").Range("A2:C26").ClearContents
> >> application.goto Sheets("Sheet2").Range("F3")
> >>
> >> end with
> >> next ws
> >> End Sub
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> dguillett1@austin.rr.com
> >> "Hazel" <Hazel@discussions.microsoft.com> wrote in message
> >> news:71005897-1054-4923-A7D2-701D8413AB45@microsoft.com...
> >> > Hi All you experts out there,
> >> >
> >> > I have a little macro code below which runs on Sheet1 of my workbook --
> >> > as
> >> > I
> >> > have 26 sheets in the workbook when I run the macro - Ineed it to run
> >> > on
> >> > every sheet not just on Sheet 1 -- how would I alter the macro. When
> >> > you
> >> > read
> >> > the code you will probably come up with something much better and
> >> > easier.
> >> >
> >> > Sub Macro2()
> >> > Application.ScreenUpdating = False
> >> > Range("F3:F52").Select
> >> > Selection.Copy
> >> > Range("H3").Select
> >> > ActiveSheet.Paste
> >> > Columns("H:H").Select
> >> > Application.CutCopyMode = False
> >> > Selection.Insert Shift:=xlToRight
> >> > Range("F3:F52").Select
> >> > Selection.ClearContents
> >> > Sheets("Sheet1").Select
> >> > Range("A2:C26").Select
> >> > Selection.ClearContents
> >> > Sheets("Sheet2").Select
> >> > Range("F3").Select
> >> > Application.ScreenUpdating = True
> >> > End Sub
> >> >
> >> > --
> >> > Many thanks
> >> >
> >> > hazel
> >>
> >>
> >>
>
>
>

Don Guillett

12/16/2006 4:30:00 PM

0

Glad to help

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Hazel" <Hazel@discussions.microsoft.com> wrote in message
news:94B3BEE6-B239-4586-B994-618731E69A22@microsoft.com...
> Hi Don
>
> Works perfectly thanks so much for all your help
> --
> Many thanks
>
> hazel
>
>
> "Don Guillett" wrote:
>
>> if ws.name<>"Account" then
>>
>> --
>> Don Guillett
>> SalesAid Software
>> dguillett1@austin.rr.com
>> "Hazel" <Hazel@discussions.microsoft.com> wrote in message
>> news:3CE53313-63A2-4240-84A3-1343989FB676@microsoft.com...
>> > Hi Gentlemen
>> >
>> > Sorry about the delay in replying we had a power failure yesterday for
>> > about
>> > 4 hours -- Everything works OK when we run the revised macro - however
>> > I
>> > dropped a clanger we have one sheet in the workbook named "Account" and
>> > we
>> > don't want the macro to run on that sheet as it collates the data from
>> > all
>> > the other sheets in the workbook - is it possible for that problem to
>> > be
>> > solved within the current macro.
>> > --
>> > Many thanks
>> >
>> > hazel
>> >
>> >
>> > "Don Guillett" wrote:
>> >
>> >> Didn't have time to test any of this as you need to clarify your
>> >> request.
>> >> Sub Macro2()
>> >> 'Application.ScreenUpdating = False
>> >> Range("F3:F52").Copy Range("H3")
>> >> Columns("H:H").Insert Shift:=xlToRight
>> >> Range("F3:F52").ClearContents
>> >>
>> >> 'what about this line for each sheet
>> >> Sheets("Sheet1").Range("A2:C26").ClearContents
>> >> application.goto Sheets("Sheet2").Range("F3")
>> >>
>> >> ' Application.ScreenUpdating = True
>> >> End Sub
>> >> for all ws
>> >>
>> >> Sub Macro2()
>> >> for each ws in worksheets
>> >> with ws
>> >> .Range("F3:F52").Copy .Range("H3")
>> >> .Columns("H:H").Insert Shift:=xlToRight
>> >> .Range("F3:F52").ClearContents
>> >>
>> >> 'what about this line for each sheet
>> >> Sheets("Sheet1").Range("A2:C26").ClearContents
>> >> application.goto Sheets("Sheet2").Range("F3")
>> >>
>> >> end with
>> >> next ws
>> >> End Sub
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> dguillett1@austin.rr.com
>> >> "Hazel" <Hazel@discussions.microsoft.com> wrote in message
>> >> news:71005897-1054-4923-A7D2-701D8413AB45@microsoft.com...
>> >> > Hi All you experts out there,
>> >> >
>> >> > I have a little macro code below which runs on Sheet1 of my
>> >> > workbook --
>> >> > as
>> >> > I
>> >> > have 26 sheets in the workbook when I run the macro - Ineed it to
>> >> > run
>> >> > on
>> >> > every sheet not just on Sheet 1 -- how would I alter the macro. When
>> >> > you
>> >> > read
>> >> > the code you will probably come up with something much better and
>> >> > easier.
>> >> >
>> >> > Sub Macro2()
>> >> > Application.ScreenUpdating = False
>> >> > Range("F3:F52").Select
>> >> > Selection.Copy
>> >> > Range("H3").Select
>> >> > ActiveSheet.Paste
>> >> > Columns("H:H").Select
>> >> > Application.CutCopyMode = False
>> >> > Selection.Insert Shift:=xlToRight
>> >> > Range("F3:F52").Select
>> >> > Selection.ClearContents
>> >> > Sheets("Sheet1").Select
>> >> > Range("A2:C26").Select
>> >> > Selection.ClearContents
>> >> > Sheets("Sheet2").Select
>> >> > Range("F3").Select
>> >> > Application.ScreenUpdating = True
>> >> > End Sub
>> >> >
>> >> > --
>> >> > Many thanks
>> >> >
>> >> > hazel
>> >>
>> >>
>> >>
>>
>>
>>