| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Problem
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 "Hazel" wrote in message ... 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Problem
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 "Hazel" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Problem
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 "Hazel" wrote in message ... 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 "Hazel" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Problem
Glad to help
-- Don Guillett SalesAid Software "Hazel" wrote in message ... 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 "Hazel" wrote in message ... 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 "Hazel" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
| I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
| Color Row Macro Problem, adapted from Patrick Malloy macro | Excel Programming | |||
| Problem in updating all worksheets of a workbook using a macro that calls another macro | Excel Programming | |||
| macro problem | Excel Discussion (Misc queries) | |||
Linear Mode
