Nick's picture

Nick's Day By Day Excel Experts Blog

need help with copy and paste code

the code works fine but I need to change it copy and paste the spreadsheets it collects from my other files to the current workbook where the code resides. It currently saves a to new workbook.

Sub CopySameSheetFrmWbs()
Dim wbOpen As Workbook
Dim wbNew As Workbook
'Change Path
Const strPath As String = "C:\Users\lap\Desktop\file2\master1\"
Dim strExtension As String

'Comment out the 3 lines below to debug
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next

ChDir strPath

NickMoran's picture

Excel VBA Reports and Auditing Expert


I'm interested in working as Excel - VBA Reporting freelance work.

Currently I'm working as Reporting Analyst in Hyderabad, India.

I have worked over 7 years in Excel Reporting, VBA programming and also Auditing.

If you are interested please get in touch with me via email: / Phone: USA-Phone# 929-800-3813, skype:


Excel help with a Formula

Looking for excel formula to calculate percent of time using standard deviation and mean.

Have existing macro available to open on each new Excel spreadsheet

I need to figure out if it's possible to have my EXISTING macro set up so that each time I open a NEW spreadsheet in Excel, I can run the macro in the same spreadsheet.

For example, if I export a set of data into Excel from another program, and the Excel spreadsheet opens with the data, I want to be able to click on "Macros" and be able to choose the Macro I need right there.

Can anyone please help me set this up?

VBA code to execute Java-script on webpage


I am stuck in automation of a website by VBA. It has Javascript under a dropdown menu(Download). I wrote down VBA code but neither it gives any error nor it executes the Java script. Here is HTML code

  • Download
  • and VBA code

    For Each MyHTML_Element In IE.document.getElementsByTagName("a")

    Product Review: Stellar Phoenix Excel Repair Software

    Verdict: Stellar Phoenix Excel Repair is a professional tool to fix corrupt or damaged Excel files and restore the Excel objects to a new blank Excel file. The tool supports XLS and XLSX file repair from any reason of corruption. With this tool, you can recover objects like charts, chart sheets, cell comments, worksheet properties, and other Excel data.

    How to delete undesired rows

    I have reports in which no. of rows has been changed. I have create a macro to sorting the report. Its work fine with 144 rows. But whenever there are more rows in report macro still shows first 144 rows. How can I program my macro to work accurately?

    k33jakha's picture

    Need formula assistant for calculation of Intrest:

    as attached images, I need to calculate interest rate according to thecondition.

    Help Me!

    Excel Interest calculation

    Can i use a date picker to go to worksheets that are named as dates

    I have a workbook that has 365 worksheets, one for everyday of the year. They are named by date. Can I use a date picker to go to the corresponding worksheet. I am using Excel 2013

    How enter data in multiple rows of excel, by calling them from VBA Macro?


    I have a excel database, which contains different classes e.g. Class-1, Class-2 and Class-3 and every class has 10 different names. Now I want to create three macros by the name Class-1, Class-2 and Class-3, so that if anybody in that excel sheet writes =class-1, then the names which are linked with class-1, shall displayed in excel rows. Simillary on the same sheet other classes may also be called, when required.

    If anybody can solve this, it would be a great help.


    How to Open a Corrupt Excel File

    When you are not open an Excel workbook, it means, your MS Excel file are damaged. The Microsoft Excel Application already provide AutoRecover feature that enables the damage file to be re-opened and simultaneously Fix it. But, however, the application of Excel are not always start the file recovery process each time, you attempt to open a damaged workbook. In such condition, you must attempt manual Repair process. All manually Repair process mentioned below:


    How to Recover Data from a Damaged Microsoft Access Database Table

    Microsoft Access database tables hold all the data within it. At times, databases tend to get damaged due to various reasons and exhibit error messages. You can face problems when you perform various functions. Some of them occur when any one of the following conditions comes in front.


    Database Programming using VBA/Excel


    Can anyone please instruct me how to use my current worksheet containing 5 columns as a table in a relational database? This way, I can use codes like SELECT....FROM....WHERE...ORDER BY... as in database programming.

    I am new to VBA and I apologize in case my question is trivial and ill-framed.

    Thank you very much.

    Top 5 Methods to Repair your Corrupt Excel 2007 File

    MS Excel is widely used for storing and managing data in both text and graphical forms. Being a part of MS Office suite, Excel is used in various companies, colleges and institutions, and at many other places for keeping data in organized form with the help of its inbuilt formulas, formatting techniques, filters, and other internal functions. One of the major features of MS Excel application is data portability, which makes your data accessible on any Windows based computer.

    Almir's picture

    Solution to "Macro code that will repeat x number of time based upon a cell in my excel spreadsheet"

    This is solution to post at: Example file is attached.

    Macro code that will repeat x number of time based upon a cell in my excel spreadsheet
    Almir's picture

    Solution to Budgeting

    Almir's picture

    Example of calculated pivot field to compare two data sets

    Solution to:

    compare two data sets with pivot table
    Almir's picture

    Calculate Easter Date

    Thanks "Mr Spreadsheet" John Walkenbach for this formula.

    Calculate Easter Date
    Almir's picture

    Add a custom command to your right mouse-click menu

    When using particular macro often, it is handy to have it on right mouse-click. In this example there is a simple macro "Show Date and Time", and it was added to right mouse-click menu.

    Supposing you have a macro called "DateAndTime" in a "Module1", copy code from attached .TXT file to the "Workbook" part in VBE.

    With slight modification you can add more commands to the right mouse-click menu. It was explained in the comments.

    If you want custom commands available on right mouse-click, regardless of the file you work on, copy the code to your "Personal" macro file.

    Add a custom command to your right mouse-click
    Nick's picture

    Comparing Data sets

    An excellent way to compare data sets with the same column headings is to list them under each other, and add a new column called "source", and populate that source.

    Then create a pivot table, and add source as the column field.

    Then you can easily spot differences. Here's an example for comparing Trade PVs


    I want formuls for retail pick qty form store ,,,
    find out greatere qty avalable store name what we want

    Bhagwat Tambe

    Vikas Verma's picture



    Please try this to transpose the vertical values to horizontal Values.





    I'm trying to transpose (A) to (B), but every time I do this it just strings the text across one row. I need each address to to be read horizontally in a row with a blank cell between each address in the column. I have 1155 address in the column I don't want to transpose one at a time. I want to copy all 1155 address and have them transposed in the format I need. See (B) Format Needed

    (A) Existing


    (Blank Cell)

    600 STEWART ST STE 1200

    Almir's picture

    An Example of Bracketed Column Name

    This is an example based on post at:

    Bracketed column name can be seen when you choose "Show Total Row" with Data Table. When you right-click on Data Table and choose "Show Total Row" and choose "SUM", this is what you get within a Total cell. "Meal" within brackets refers to column "Meal" within Data Table, not a named range. So, "SUBTOTAL(109,[meals])" means "SUM of 'Meals' column", where "Meals" is a column within Data Table.

    Maybe "Sum" cell was copied away from a Data Table, and that is why it looked strange.

    Bracketed Column Name in Data Table
    Almir's picture

    Create Index page with hyperlinks to sheets

    This is solution to: "Hyperlink Macro that converts a list of sheet names to hyperlinks to the sheets", at:

    To create an index page with hyperlinks to all sheets in a workbook:

    1. First, insert a new sheet and name it "Index"
    2. In VBE (Alt+F11), select "Index" sheet and paste the code from the attached .txt file into the right pane:
    3. Run Macro "CreateIndex"

    Create Index page with hyperlinks to all sheets in a workbook
    Vikas Verma's picture

    How to reduce excel file size?


    1. Tips & Tricks on How to reduce Excel file size?
    2. Are you thinking why my Excel file size is so bigand how can I reduce it?
    3. Don’t Worry…..Here you will find great Tips on Reducing Excel File size
    4. Tip 1. Zip the Excel file
    5. Tip 2: Save the Excel file in .xlsbformat
    6. Tip 3. Remove unused cellsFollow below steps to remove unused cells1. Select any cell and press F5.2. Click Special-> Blank radio button.3. Go to Edit-> Clear All.
    7. Tip 4. Remove formattingFollow below steps to remove formatting1. Open your Excel sheet.2. Press CTRL+A3. Click Remove Formats.

    Vikas Verma's picture

    CELLS Property in VBA

    CELLS Property....

    This property can be used as an alternative to the absolute range property and is generally more flexible to work with, as variables are easier to pass into it.
    There are two optional arguments:
    Cells([row] [,column])
    Leaving the arguments empty (no brackets), it will detect the current selection as the active range.
    Adding an argument to either row or column with a number will refer to the co-ordination of the number passed.
    Adding both arguments will explicitly locate the single cell’s co-ordinate.
    'Examples of the Cells property
    Sub CellsExample()

    Vikas Verma's picture

    Soul of Excel formulas

    Hi if you want to be an expert in excel formulas then learn Cell Referencing.....


    Cell Referencing
    Vikas Verma's picture

    Move ALL files (or of a specific file type) from one folder into another folder

    Sub MoveFilesFolder2Folder()
    Dim fso
    Dim sfol As String, dfol As String

    sfol = "c:\MyFolder" ' change to match the source folder path
    dfol = "e:\MyFolder" ' change to match the destination folder path

    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next

    If Not fso.FolderExists(sfol) Then
    MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
    ElseIf Not fso.FolderExists(dfol) Then
    MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"

    Syndicate content