Copy and paste area not same in filtered mode .

[Aligahk06]

Dear All,

I am in filtered mode and a column cells contains formulae.
while remain in filtered mode and copying all those filtered cell contains
formulae and pasting
as value the following error encountered.
The information can not be pasted becoz copying area and paste area not the
same size and shape.
Try one of the following:

click on a single celland then paste.
Its to difficult to copy each cell and pasting as value becoz no of cell is
more than thousands.

Please can i have any option to overcome this problem ?????


Please assist ?

Rgds,

Aligahk06

 

[Dave Peterson]

Maybe you sort your data so that the visible rows are grouped together after
you've applied the filter.

Then copy|paste special|values and sort your data back to the way you like.

======
Alternatively, you could use a macro that looks at each area one at a time.

If you want to try:

Option Explicit
Sub testme()

Dim myVisRng As Range
Dim myVisFormulaRng As Range
Dim myArea As Range

Set myVisRng = Nothing
On Error Resume Next
Set myVisRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeVisible))
On Error GoTo 0

If myVisRng Is Nothing Then
MsgBox "No visible cells in selection!"
Exit Sub
End If

Set myVisFormulaRng = Nothing
On Error Resume Next
Set myVisFormulaRng = Intersect(myVisRng, _
myVisRng.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myVisFormulaRng Is Nothing Then
MsgBox "no formulas in visible range"
Exit Sub
End If

For Each myArea In myVisFormulaRng.Areas
myArea.Value = myArea.Value
Next myArea

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

==========
You'd apply the filter, select the range to convert (you can include the visible
and hidden rows since the macro looks for them).

Then run the macro.

 

[rhett waters]

Memo
To: Human Resources Dep. Members
From: Mindi Wallace
CC: Alice Butra
Date: October 5, 2010
Re: important job summaries
Please be sure that all Welcome Kits indicate that employers have certain responsibilities under the Occupational Safety and Health Act of 1970. Employees must be aware of these. The information below summarizes the most important employers responsibility.

The Welcome Kits should inform employees that they can find more information about workers rights and responsibilities by visiting OSHA?s Web site at www.osha.gov.

? Provide a workplace free from serious recognized hazards and comply with standards, rules and regulations issued under the OSHA Act.
? Examine workplace conditions to make sure they conform to applicable OSHA standards.
? Make sure employees have and use safe tools and equipment and properly maintain this equipment.
? Use color codes, posters, labels or signs to warn employees of potential hazards.
? Establish or update operating procedures and communicate them so that employees follow safety and health requirements.
? Provide medical examinations and training when required by OSHA standards.
? Post, at a prominent location within the workplace, the OSHA poster (or the state-plan equivalent) informing employees of their rights and responsibilities.
? Report to the nearest OSHA office within 8 hours any fatal accident or one that results in the hospitalization of three or more employees.
? Keep records of work-related injuries and illnesses. (Note: Employers with 10 or fewer employees and employers in certain low-hazard industries are exempt from this requirement.)
? Provide employees, former employees and their representatives access to the Log of Work-Related Injuries and Illnesses (OSHA Form 300).
? Provide access to employee medical records and exposure records to employees or their authorized representatives.
? Provide to the OSHA compliance officer the names of authorized employee representatives who may be asked to accompany the compliance officer during an inspection.
? Not discriminate against employees who exercise their rights under the Act.
? Post OSHA citations at or near the work area involved. Each citation must remain posted until the violation has been corrected, or for three working days, whichever is longer. Post abatement verification documents or tags.
? Correct cited violations by the deadline set in the OSHA citation and submit required abatement verification documentation.

 

[rhett waters]

Memo
To: Human Resources Dep. Members
From: Mindi Wallace
CC: Alice Butra
Date: October 5, 2010
Re: important job summaries
Please be sure that all Welcome Kits indicate that employers have certain responsibilities under the Occupational Safety and Health Act of 1970. Employees must be aware of these. The information below summarizes the most important employers responsibility.

The Welcome Kits should inform employees that they can find more information about workers rights and responsibilities by visiting OSHA?s Web site at www.osha.gov.

? Provide a workplace free from serious recognized hazards and comply with standards, rules and regulations issued under the OSHA Act.
? Examine workplace conditions to make sure they conform to applicable OSHA standards.
? Make sure employees have and use safe tools and equipment and properly maintain this equipment.
? Use color codes, posters, labels or signs to warn employees of potential hazards.
? Establish or update operating procedures and communicate them so that employees follow safety and health requirements.
? Provide medical examinations and training when required by OSHA standards.
? Post, at a prominent location within the workplace, the OSHA poster (or the state-plan equivalent) informing employees of their rights and responsibilities.
? Report to the nearest OSHA office within 8 hours any fatal accident or one that results in the hospitalization of three or more employees.
? Keep records of work-related injuries and illnesses. (Note: Employers with 10 or fewer employees and employers in certain low-hazard industries are exempt from this requirement.)
? Provide employees, former employees and their representatives access to the Log of Work-Related Injuries and Illnesses (OSHA Form 300).
? Provide access to employee medical records and exposure records to employees or their authorized representatives.
? Provide to the OSHA compliance officer the names of authorized employee representatives who may be asked to accompany the compliance officer during an inspection.
? Not discriminate against employees who exercise their rights under the Act.
? Post OSHA citations at or near the work area involved. Each citation must remain posted until the violation has been corrected, or for three working days, whichever is longer. Post abatement verification documents or tags.
? Correct cited violations by the deadline set in the OSHA citation and submit required abatement verification documentation.

 

[rhett waters]

Memo
To: Human Resources Dep. Members
From: Mindi Wallace
CC: Alice Butra
Date: October 5, 2010
Re: important job summaries
Please be sure that all Welcome Kits indicate that employers have certain responsibilities under the Occupational Safety and Health Act of 1970. Employees must be aware of these. The information below summarizes the most important employers responsibility.

The Welcome Kits should inform employees that they can find more information about workers rights and responsibilities by visiting OSHA?s Web site at www.osha.gov.

? Provide a workplace free from serious recognized hazards and comply with standards, rules and regulations issued under the OSHA Act.
? Examine workplace conditions to make sure they conform to applicable OSHA standards.
? Make sure employees have and use safe tools and equipment and properly maintain this equipment.
? Use color codes, posters, labels or signs to warn employees of potential hazards.
? Establish or update operating procedures and communicate them so that employees follow safety and health requirements.
? Provide medical examinations and training when required by OSHA standards.
? Post, at a prominent location within the workplace, the OSHA poster (or the state-plan equivalent) informing employees of their rights and responsibilities.
? Report to the nearest OSHA office within 8 hours any fatal accident or one that results in the hospitalization of three or more employees.
? Keep records of work-related injuries and illnesses. (Note: Employers with 10 or fewer employees and employers in certain low-hazard industries are exempt from this requirement.)
? Provide employees, former employees and their representatives access to the Log of Work-Related Injuries and Illnesses (OSHA Form 300).
? Provide access to employee medical records and exposure records to employees or their authorized representatives.
? Provide to the OSHA compliance officer the names of authorized employee representatives who may be asked to accompany the compliance officer during an inspection.
? Not discriminate against employees who exercise their rights under the Act.
? Post OSHA citations at or near the work area involved. Each citation must remain posted until the violation has been corrected, or for three working days, whichever is longer. Post abatement verification documents or tags.
? Correct cited violations by the deadline set in the OSHA citation and submit required abatement verification documentation.

 

[rhett waters]

Marie Rose Weston
230 West End Avenue
New York, NY 10025
Ms. Amelia Sutton, Vice President
Wise Financial Services, Inc.
60 Wall Street
New York, NY 10056



?Marie Rose Weston?
230 West End Avenue
New York, NY 10025
E-mail: (e-mail address removed)

Home: 212 555-5555
Cell: 917 555-5555
Fax: 212 666-6666

October 7, 2010



Ms. Amelia Sutton, Vice President
Wise Financial Services, Inc.
60 Wall Street
New York, NY 10056

Dear Ms. Sutton:

Ms. Pamela Johnson, a dear friend of mine, has recommended that I contact you regarding your investment counseling services. She has been very satisfied with your financial planning advice.

I am interested in a similar investment strategy that you provided to Ms. Johnson. You counseled her to diversify her stock portfolio to include more real estate investments. I think I should do the same. I also need advice on whether it is wise to purchase some low-priced stocks that have come to my attention.

Please let me know when it would be a good time for us to meet. In the meantime, I would appreciate it if you could provide me with a written cost estimate for your financial planning services. I am enclosing my last portfolio statement for your review.



Sincerely,



Marie Rose Weston

enclos

 

[rhett waters]

Marie Rose Weston
230 West End Avenue
New York, NY 10025
Ms. Amelia Sutton, Vice President
Wise Financial Services, Inc.
60 Wall Street
New York, NY 10056



?Marie Rose Weston?
230 West End Avenue
New York, NY 10025
E-mail: (e-mail address removed)

Home: 212 555-5555
Cell: 917 555-5555
Fax: 212 666-6666

October 7, 2010



Ms. Amelia Sutton, Vice President
Wise Financial Services, Inc.
60 Wall Street
New York, NY 10056

Dear Ms. Sutton:

Ms. Pamela Johnson, a dear friend of mine, has recommended that I contact you regarding your investment counseling services. She has been very satisfied with your financial planning advice.

I am interested in a similar investment strategy that you provided to Ms. Johnson. You counseled her to diversify her stock portfolio to include more real estate investments. I think I should do the same. I also need advice on whether it is wise to purchase some low-priced stocks that have come to my attention.

Please let me know when it would be a good time for us to meet. In the meantime, I would appreciate it if you could provide me with a written cost estimate for your financial planning services. I am enclosing my last portfolio statement for your review.



Sincerely,



Marie Rose Weston

enclos

 

[rhett waters]

Marie Rose Weston
230 West End Avenue
New York, NY 10025
Ms. Amelia Sutton, Vice President
Wise Financial Services, Inc.
60 Wall Street
New York, NY 10056



?Marie Rose Weston?
230 West End Avenue
New York, NY 10025
E-mail: (e-mail address removed)

Home: 212 555-5555
Cell: 917 555-5555
Fax: 212 666-6666

October 7, 2010



Ms. Amelia Sutton, Vice President
Wise Financial Services, Inc.
60 Wall Street
New York, NY 10056

Dear Ms. Sutton:

Ms. Pamela Johnson, a dear friend of mine, has recommended that I contact you regarding your investment counseling services. She has been very satisfied with your financial planning advice.

I am interested in a similar investment strategy that you provided to Ms. Johnson. You counseled her to diversify her stock portfolio to include more real estate investments. I think I should do the same. I also need advice on whether it is wise to purchase some low-priced stocks that have come to my attention.

Please let me know when it would be a good time for us to meet. In the meantime, I would appreciate it if you could provide me with a written cost estimate for your financial planning services. I am enclosing my last portfolio statement for your review.



Sincerely,



Marie Rose Weston

enclos

 

[rhett waters]

Marie Rose Weston
230 West End Avenue
New York, NY 10025
Ms. Amelia Sutton, Vice President
Wise Financial Services, Inc.
60 Wall Street
New York, NY 10056



?Marie Rose Weston?
230 West End Avenue
New York, NY 10025
E-mail: (e-mail address removed)

Home: 212 555-5555
Cell: 917 555-5555
Fax: 212 666-6666

October 7, 2010



Ms. Amelia Sutton, Vice President
Wise Financial Services, Inc.
60 Wall Street
New York, NY 10056

Dear Ms. Sutton:

Ms. Pamela Johnson, a dear friend of mine, has recommended that I contact you regarding your investment counseling services. She has been very satisfied with your financial planning advice.

I am interested in a similar investment strategy that you provided to Ms. Johnson. You counseled her to diversify her stock portfolio to include more real estate investments. I think I should do the same. I also need advice on whether it is wise to purchase some low-priced stocks that have come to my attention.

Please let me know when it would be a good time for us to meet. In the meantime, I would appreciate it if you could provide me with a written cost estimate for your financial planning services. I am enclosing my last portfolio statement for your review.



Sincerely,



Marie Rose Weston

enclos