This article helps you to make an exact copy of your worksheet including all formatting into another file.

Let us take two files, ‘File1’ and ‘File2’ for example. Now I will explain how to import a sheet from ‘File1’ to ‘File2.

Select the sheet tab you want to import from ‘File1’ to ‘File2’ and right click on it as shown in the picture below:

Choose “Move or copy” from the window.

At the top, choose the workbook you want to make a copy to, (example: File2.xls).

At the bottom of the window check the “Create a copy” box
then select ‘OK’.

Now you can see the imported sheet in File2.

If you want to import a sheet from a file to a new workbook, then follow below steps:

At the top, choose new book.

At the bottom of the window check the “Create a copy” box
then select ‘OK’.

    ]]> http://www.p2w2.com/blog/index.php/how-to-import-a-sheet-from-one-file-to-another-file/feed/ 0 http://www.p2w2.com/blog/index.php/how-to-import-a-sheet-from-one-file-to-another-file/ http://feedproxy.google.com/~r/p2w2/~3/VAFcWsbVDX8/ http://www.p2w2.com/blog/index.php/how-to-avoid-screen-updating-in-excel-vba/#comments Fri, 20 Apr 2012 04:00:29 +0000 Vani http://www.p2w2.com/blog/?p=1260 This article helps to handle the screen updates using macro. We can speed up our macro by turning off the screen updating while the macro runs, by adding the below line of code to the macro:
Sub Macro ()
Application.ScreenUpdating = False

Write the necessary code Here

——————-

——————-
Application.ScreenUpdating = True
End Sub.

The prior versions of excel 2000, it was not required to turn ScreenUpdating back to true. When the macro is finished, Excel would revert back to turning the screen updating to true.

But now whether you are writing macros in Excel 97, Excel 2000, Excel 2002, 2003, 2007 or 2010 always we have to turn the screen updating back to true with the below code:
Application.ScreenUpdating = True

Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoicesand Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.

    ]]> http://www.p2w2.com/blog/index.php/how-to-avoid-screen-updating-in-excel-vba/feed/ 0 http://www.p2w2.com/blog/index.php/how-to-avoid-screen-updating-in-excel-vba/ http://feedproxy.google.com/~r/p2w2/~3/t3VW8b2RmrY/ http://www.p2w2.com/blog/index.php/how-to-do-a-two-way-lookup-in-excel/#comments Tue, 27 Mar 2012 04:00:25 +0000 Vani http://www.p2w2.com/blog/?p=1217 Excel spreadsheets support lookup functions that return a value from a table by looking up another value in the table. The vlookup and hlookup functions in Excel work as one-way lookups. This article helps you to perform a two way lookup.

The Picture below shows a simple example:

The first table shows products, Price of the product and discount on number of products purchased. The second table shows name of the person, product purchased, number of products purchased and discount applicable.

A formula is used in column E of second table to get the discount percentage from the first table as shown in the picture below:

The formula in cell E15 looks up the values of cells C15(Product) and D15(# of Products) in the first table and returns the corresponding value from the table.

The formula in E15 is:

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-”)

Description of the formula:

The above formula uses the INDEX function, which consists of three arguments.

The first argument gives the entire range of ‘Discount% on # of products’ from first table($D$4:$F$12).

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-”)

The second argument is MATCH function. MATCH ($C15,$B$4:$B$12,0)

It matches the ‘product’ in cell C15 of second table with the ‘products’ in cells B4 to B12 of first table and returns the ‘Product‘.

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-”)

The third argument is also a MATCH function. MATCH ($D15,$D$3:$F$3,0).

It matches the ‘# of products’ in cell D15 of second table with the ‘Discount% on # of products’

in cells D3 to F3 of first table and returns the ‘# of products’.

=IFERROR(INDEX($D$4:$F$12,MATCH($C15,$B$4:$B$12,0),MATCH($D15,$D$3:$F$3,0)),”-”).

In this way the above formula is used as a two way lookup formula to retrieve the value required.

    ]]> http://www.p2w2.com/blog/index.php/how-to-do-a-two-way-lookup-in-excel/feed/ 2 http://www.p2w2.com/blog/index.php/how-to-do-a-two-way-lookup-in-excel/ http://feedproxy.google.com/~r/p2w2/~3/DVVO5PF_yJ0/ http://www.p2w2.com/blog/index.php/how-to-get-a-print-pdf-button-excel/#comments Wed, 16 Nov 2011 04:00:33 +0000 Vani http://www.p2w2.com/blog/?p=1186 One of the features in Excel is the ability to convert Excel files to PDF format. In this article we explain how to create a button in the excel worksheet, which when clicked generates a PDF file of that sheet.

Let us take the picture below as example to take a PDF and save it in current folder.

Below are the steps to get a button, which converts the excel file to a PDF file:

Select Developer, select Insert from Control group and select Button from Form Controls.

Drag the control on the spreadsheet, a window appears.

Enter the name of the macro in the tab provided for Macro name and click OK.

To edit the name of the button, Right click on the button and select Edit Text.

Right click on the button and select Assign Macro.

Select the Macro name and select New.

A Microsoft Visual Basic window appears.

Enter the below code in the application where the arrow is pointed, under ‘Sub CreatePDF()’.

Sub CreatePDF()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

“Generate Report.pdf”, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False

End Sub

Close the Microsoft Visual Basic application, now just click on the CreatePDF button. That’s all a PDF will be generated and saved in the same folder.

If we want to change the name of the PDF, we can change it in the code. For example in the second line we have “Generate Report.pdf”, change it to “Create Report.pdf”.

————————————————————————————————————–

    ]]> http://www.p2w2.com/blog/index.php/how-to-get-a-print-pdf-button-excel/feed/ 0 http://www.p2w2.com/blog/index.php/how-to-get-a-print-pdf-button-excel/ http://feedproxy.google.com/~r/p2w2/~3/1pd3YawgCHY/ http://www.p2w2.com/blog/index.php/how-to-sort-in-excel/#comments Sat, 29 Oct 2011 04:00:41 +0000 Vani http://www.p2w2.com/blog/?p=1161 Sorting is used to reorder our data in excel. We can easily reorder the data based on the type of sorting that we choose. We have two methods to sort the data.

Method 1:

Below are the steps to sort the given data in Excel:

Select a single cell anywhere in the range that you want to sort.

Note: If there are merged cells in your data, unmerge the cells before applying the Sort operation.

Select Data menu and select Sort from Sort & Filter group.

In the Sort by list, select the first column on which you want to sort. (You can click on Add Level to include additional number of columns to sort).

In the Sort On list, select the desired field from the drop down (Values, Cell Color, Font Color, or Cell Icon).

In the Order list, select the order that you want to apply to the sort operation (A to Z or Z to A for text, lower to higher or higher to lower for numbers).

Check the field My data has headers if your data has headers included.

Click OK.

Picture below shows the data after sorting.

Method 2:

Select a single cell in the column of the range that you want to sort.

Right click on the selected cell and select Sort in the options appeared.

Select the desired options (A to Z or Z to A for text, lower to higher or higher to lower for numbers).

Picture below shows the data after sorting.

————————————————————————————————————–

    ]]> http://www.p2w2.com/blog/index.php/how-to-sort-in-excel/feed/ 0 http://www.p2w2.com/blog/index.php/how-to-sort-in-excel/