Excel 2010/2013
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  


Microsoft Excel Forum
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

This forum is for the discussion of Microsoft Excel.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft excel forum
Post New Topic

Read Dynamically change VB Macro to Project name by Perry S @ 7/9/2019
Good Morning Richard
I need some help if you would please

I have an Excel workbook that has a very detailed macro to make it work. And it does work well, I use the lessons from your Access classes to help me through it.
What the code is doing is it is coping and pasting the data from the one excel workbook into another.
The issue that I m having is the code has references to the workbook file name.
So, if I change the workbook file name the macro, for obvious reasons ceases to work.
How can I call the Project name or the file name into the code so that when I change the file name it dynamically changes the code?

Basically, any lines where the code says ("DDLTemplate.xlsm") the word Template be changing to the first initial and the last name of the driver.
Example ("DDLPShusta.xlsm")

Below is a short summary of the code

'Copy & Past Pay Period End
    Application.CutCopyMode = False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

Thank you
Perry Shusta

Show Just This Thread        Post Reply
Read Macro by ron b @ 4/28/2018
Do you have a lesson on creating Macro in Excel on this site? If you do can you post/email me the link - Tnx

Reply from Alex Hedley:

I don't think the Excel Courses got to Advanced to cover Macros.
Is there anything in particular you were wanting to automate?
Show Just This Thread        Post Reply
Read Macro by Richard L @ 11/28/2016
Thank you, There are a lot of cool templates and if Rick wants to do a seminar on how to make cool spreadsheets, I'm intereste.
Show Just This Thread        Post Reply
Read Macro by Richard L @ 11/26/2016
It is a template called the "To Do List" on 2016 Excel.  But I can't seem to find out what is being done.  all I know is if you double click on that cell a check mark appears and all the cells in that row are formatted with a strikethrough format.

Reply from Alex Hedley:

I'll take a look into that template.
Show Just This Thread        Post Reply
Read Macro by Richard L @ 11/23/2016
At work we have an Excel 2016 and one of the templates is a to Do List.  There is a field that if you double-click on it a check mark appears and the text in the row it's in has a strikethrough.  What is this feature called and how and where do I learn to do that?  
Also the background is cool where it looks like a sheet of notebook paper.  How do you do that?

Reply from Alex Hedley:

Is this an Office template, what's it called, is there a link on the marketplace?
If you have the developer tab on can you look into any code it uses?
Show Just This Thread        Post Reply
Read Macro Code by Stan @ 5/4/2015
So I have a spreadsheet that is five columns wide. It's a daily log. At the end of a day, I like to add a bottom border to the cells that represent the activity of the day, to help me distinguish separate days. I recorded a macro that works on a separate cell, or a selection. What I want it to do is apply the border to five cells, after I select just one. What am I missing? Here's the code.
Sub ApplyBorder()
' ApplyBorder Macro
' Applies bottom border to selected cells

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

Reply from Alex Hedley:

I'll need to test this but would the tint and shade not need a value other than 0?

You can use some code to get the last row but this with the range changed should work.
Range("A1:A2").Borders(xlEdgeBottom).LineStyle = xlContinuous
Show Just This Thread        Post Reply
Read Macros by Abhinav Grandhi @ 3/25/2015

I need to generate reports on monthly basis from various reports. Please explain how to use Macros step by step process.
Thank you in advance.

Reply from Alex Hedley:

This would depend what your needs are.
You could create a report that links to a datasouce and have all the graphs etc set up to be populated by this data, a template of sorts.
Can you give more detail of what steps you need to do?
Show Just This Thread        Post Reply
Read Recorded Macro by Bruce @ 5/31/2014
The default when you go into macro record mode is absolute. You can click on the middle section of the recorder dialog box to change to relative mode. This may fix your problem.
Show Just This Thread        Post Reply
Read Recorded Macro by Michelle C @ 4/26/2014
I figured it out.  I changed the name of the worksheet in the macro to ActiveSheet.  That took care of the problem.
Show Just This Thread        Post Reply
Read Recorded Macro by Michelle C @ 4/22/2014
I'm creating worksheets by double-clicking on a total on a pivot table.  I've recorded a macro for repetitive tasks that I perform on every new worksheet.  The problem is that the macro is specifying the specific worksheet name of the w/s I recorded the macro on so the macro won't work on new worksheets.  Is there a way of changing the macro so that it will work on any new worksheet I create?
Show Just This Thread        Post Reply
Read Macros by Anny Hahn @ 3/29/2012
I have a long list over 1 millions rows.  I have one column that have either a number that beginns with a 9 or 14 and I want to pull all the numbers that begin with 9 and copy them from cell B1 to C1 and if is a 14 leave that number on column B.

Thank you Ron
Show Just This Thread        Post Reply
Read macros by Pete @ 5/17/2010
I am writing a series of technical reports within one Excel document. Two sheets are seperate areas of testing relating to the same sample, the third a summary. How would I go about entering values common to all three sheets on the summary and have them appear on the other sheets in the appropriate cell? Specifically as a starter, date of test would appear in sheet 1 cell F7. Sheet two it appears in C5, D5, E5, F5, G5. Sheet two it appears in C1, C2, C3, C4, C5. I'm sure it can be done via VBA but my self taught skills lack the expertise. Thank you for your help!

Answer from Richard Rost:

You can just enter this information into the first sheet and then LINK to those cells in the next 2 sheets.

For example, type in some data into Sheet1 cell A1. Then go to Sheet2 and type in:


Now that cell will REFER to (is LINKED to) that cell on Sheet1.

Alternatively, you can COPY and then PASTE SPECIAL… PASTE LINK the data between two sheets in a similar fashion.
Show Just This Thread        Post Reply
Read filtering and sorting macro by Richard Rost @ 7/22/2009
Kathy, you could use the ISBLANK function to determine if the value in that row is blank, and then use that as one of your sort criteria. For example, if your value is in column A1, then B1 would be: =ISBLANK(A1)

Now autofill that value down the whole column, and now you have either TRUE or FALSE in column B. You could use that to sort by in a multi-column sort. Sort by the isblank column first, then column A. It should eliminate the need for the filter.
Show Just This Thread        Post Reply
Read filtering and sorting macro by Kathy Boyette @ 7/22/2009
Richard, I have a spreadsheet that needs to be filtered, then sorted. I am filtering out the non-blank cells, then trying to sort, ascending. Most of the list I'm sorting disappears. I read that the autofilter I've used corrupts the macro. Is there another way to sort non-blanks other than autofilter?

Thanks, Kathy Boyette
Show Just This Thread        Post Reply

Collapse All Topics


Post Your Comments or Subscribe
    If you would like to be notified of new posts on this forum,
    just enter your email address below. It will be kept private.
If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 6+9:
  Notify me when the this forum is updated
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

5/4/2014Microsoft Access Expert 20
3/5/2013Excel Tip: Price Markups
2/3/2013Signed Copies of my Excel Book
9/27/2012Excel Expert 10 Handbook Ready
9/18/2012Two New Excel Tips: Date/Time Differences
6/20/2012Excel Tip: Sum Cells Between Two Values
6/16/2012New Excel Tip: Paste Link
3/26/2012Excel Tip: 2D Matrix Lookups
11/18/2011Dashboards in Excel
10/6/2011Excel Expert 11 On Hold

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP