Home

Macros and VBA

50 Conversations

Latest Activity

Custom List Message Item

Hello Friends,

 

I need to create a Macro in MS excel 2010 so that I can divide a file into several files based on the filtering of a particular column.

ie, One column of the excel file will contain several entries , some of which are common - so all the com

... Read More
101 Views
2 Replies

Here is sample code to filter values on Sheet1 by a person's name.  In this example the names are Matt, John and Jared....  If you paste this code into the attached workb

... Read More

Hi @Bala Subramanian,

 

We can do this by creating macro.

 

Can you give sample file so that i can explain how to do.

 

Or explain in detail.

Read More

Hello all,

I often use =Mod(row),2)-1 to apply alternating row colors, where the first row does not receive the formatting.  I also have a simple macro that I use to run that type of conditional formatting.  The user selects the area that needs the formatt

... Read More
56 Views
3 Replies

Something like this will work:

 

Sub Test()

    Dim myVar As Long

    myVar = ActiveCell.Row
    
    If myVar Mod 2 = 1 Then
        MsgBox "I'm an odd number!"
    Else
... Read More
Best Response

I have four sheets I want excluded from my macro (Renaming based on cell). This is my first round with macro, so I'm not sure what to input. My sheets are: Directions, Tips & Tricks, Home, Bubble Kids. And here is my macro:

 

Sub RenameTabs()
'Updateby201406

... Read More
46 Views
2 Replies

Try out this code

 

Also I'd encourage you not to reference cells directly in your VB code (e.g. B2)  and instead used Named Ranges  (e.g.  Go to Formula > Define Name > ca

... Read More

HI

 

How can I hide or block a macro so that nobody else can edit or change it? I need to make a macro enabled workbook available to many users.

27 Views
1 Reply
You can lock your macro code via the following steps: Alt + F11 to open the VBA window Tools > VbaProjectProperties > Protection Tab

Occasionally my macro hangs.  I believe it is in this code but since the only way to spot the hang is to task manager kill the job, I can't be sure.  Is there a way to return to my code after a time out period attempting to access a web-page?  The query r

... Read More
25 Views
1 Reply
You may want to try changing .BackgroundQuery = True to .BackgroundQuery = False

I am a restaurant owner/operator and trying to create a good tool for our chefs to put out a neat and organized daily prep list for the crew at the start of each day.  Right now a lot of this is still done w/ paper and pen but with mulitple languages, thi

... Read More
71 Views
1 Reply

I believe you want a listbox not a combobox.  See the attached file for an example.  You'll need to insert a code module and link the shape to the macro called AllTogethe

... Read More

I have created a spreadsheet to QC Maintenance Plans. I would like to save each one as another sheet in the same workbook. I have created a button and need to assign a macro to that button. Can anyone help me with the code to save that sheet as a sheet wi

... Read More
54 Views
1 Reply
Sub Test()
    For Each Sheet In ThisWorkbook.Sheets
        'Copy Entire Sheet and Paste ss new sheet in same workbook
        Sheet.Copy After:=Sheets(Sheets.Count)
   
... Read More

Hello to everybody,

i have a problem. I've created a new excel component and i have registered it in my excel to use. The component works well.

The problem is when i try to copy a seleceted area with key combination CTRL+C, excel run my component and doesn'

... Read More
21 Views
0 Reply

Hi guys!
I have a big problem, I try to make macro that is gonna to make a pivot table(example in file) and then divides the sheet with table and send it by outlook(every table to specified person).
Pivot has to be just like in the example. I tried to do it

... Read More
26 Views
0 Reply

I have created a spreadsheet where the sheet 1 is a form to be filled in for Quality Control. I have created a "Save" button that will use the 'copy and move' function to create a copy of the first sheet at the end of the same workbook. I need all the com

... Read More
69 Views
5 Replies

Hi @horsteadjim,

 

Do this for Copying Current Sheet and Paste it to End of Sheet.

 

Sub Macro1()
a = Sheets.Count
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets
... Read More

Somewhere between V1701 and V1705 a change was made to excel which affects when CutCopyMode is returned back to false.  You can see this using Excel normally if you copy something and see those crawling ants but then press the Delete key.  Previously this

... Read More
64 Views
2 Replies

Hi @Ross Edwards,

 

Send me that excel file and that macro. I will try to solve it.

Read More

I have a sheet with multiple push buttons and when I copy the information to a new sheet additional buttons over and above the ones on the sheet are created.  Can any one help?

46 Views
2 Replies

Hi,

 

Try to paste that information as values only, or eliminate that buttons from the copy area before copy.

Hi @M Neil Cameron,

 

Try to right click the sheet and make copy of that.

 

Read More

I have a large data query file with a few sheets containing pivot tables. I need to add a button to the first sheet that will refresh all the pivot tables in the file.

307 Views
6 Replies

Also, when you say Data Query file are you using Power Query and do you want the queries to refresh also,  in which case my PivotCache suggestion won't do it for you

 

This

... Read More

HI Pierre

 

If you do want to go down the Macro route rather than using the Refresh All button then I'd suggest adding this code to a module.

 

Best to avoid any hard coded r

... Read More

Dear Pierre,

 

First and foremost, add the (Developer Tab) to your Excel Ribbon, and follow these steps:

 

  - Go to Insert at the Developer Tab, and click on it.

  - You will

... Read More

Hello Pierre

 

Excel already has such a button on the ribbon:

Data -> Connections -> Refresh All

 

You could also put it in the Quick Access Toolbar if you don't want to switc

... Read More
I have created and save a macro under the 'personal' workbook.. to my understanding, each time i open an excel file, i could execute the macro instantly.. BUT instead, i have to keep unhiding the macro each time i open a new excel file before i can use th... Read More
198 Views
14 Replies

Hi Helmi,

 

could you please clarify what you do to unhide the macro? If you open a file, then go to  Developer->Code->Macros, select either 'All Open Wookbooks' or 'PERSON

... Read More
Hi Everyone,
I am comparing the two Dates in the two columns (D and E).
The Dates in column D are source Dates and the Dates in column E are Start date of the Project.
I am calculating the difference in two Dates as weeks and pasting the result in the column
... Read More
135 Views
9 Replies
I am wondering however, why are you using VBA and not a formula combined with some conditional formatting?

Something like:

 

Sub dateCompare()

zLastRow = Range("D" & Rows.Count).End(xlUp).Row    'last data row

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For r = 2 To zLastRow

If Cells(r, "
... Read More

I created a command button with a VBA code to print invoices for clients.  Yesterday, when I clicked the button it worked, but now when I click the button nothing happens.  I have tried all the fixes.  I have deleted the button and reset it.  I have gone

... Read More
106 Views
1 Reply
I'm not sure but it might be worth checking the Trust Centre for Active X and Macros

PLS HELP!

I DOWNLOAD EVERY WEEK A NEW REPORT OF MY DATA BASE, BUT I APPLY THE SAME 3 MACRO VBA THAT I NEED TO COPY ALL THE TIME FROM MY PERSONAL.XLS.

1. HOW TO I MAKE THIS PROCESS AUTOMATIC??

2. ALSO, I WANT THE MACROS TO RUN AUTOMATICALLY WHEN I OPEN THE NE

... Read More
136 Views
3 Replies

Hi,

 

One possible option would be to connect your Excel file to your data using Power Query ('Get and Transform' in Excel 2016+), have all three macro in the same file, th

... Read More

I recently ran updates for MS Office and version of Excel is 2016  MSO (16.0.8067.2115) 32-bit.

 

VBA code that worked perfectly prior to the upgrade now causes Excel to crash at the same place every time.  A portion of the code is below.  It does not give

... Read More
90 Views
1 Reply

Does it also crash if you try copying another worksheet?

Hi,

I'm new in VBA.  I've been reading and seeing courses on internet this last three days and triying to understand every single line in this code, but I cant read completely all.

Could someone try to explain the reason of the parts with bolt and underline

... Read More
121 Views
3 Replies

Hi Kamu,

 

Here are some explanations

 

InStr(varCheckNumber, C_SEPARATOR) > 0

This function finds the position of the first occurance of "." in the string. If you function re

... Read More

I want to connect Excel to Micro focus Rumba using VBA. can anyone provide VBA code for establishing connection?

61 Views
0 Reply

Hi

 

Is there anyone out there who can help with this?

 

I'm creating a workbook for others to use, so I am aiming to include as much automation as possible.

 

I need to update 50 sheets, all in the same workbook so that they have a common footer based upon eit

... Read More
116 Views
4 Replies

Hi Keith 

 

This is the basic code

Sub RenameFooters()
Dim wks As Worksheet

For Each wks In Worksheets

wks.PageSetup.LeftFooter = Range("FooterText").Value
' note you ca
... Read More

Hi All,

 

I have a worksheet that serves as a data entry form & another worksheet that stores historic data.

I would like to be able to enter the new data in the data entry form & then by macro, paste the new data into the history storeage worksheet. Obvious

... Read More
140 Views
1 Reply

See if this gets you started:

 

Sub foo()
    Dim lr As Long
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    
    Set wsSource = Sheets("Input")
    Set wsD
... Read More

Hi I recently used the following Macro, provided by Gary's Student, to create a PDF from an active sheet, generate a unique Filename based on a cell ref and save it to a specific location.

 

This macro works well for me, however, I would like to add to it s

... Read More
279 Views
5 Replies

This is code I use.

End Sub

Private Sub Email()

    Dim objOutlook As Object
    Dim objMail As Object
    Dim signature As String
    Dim oWB As Workbook
    Set oWB = Ac
... Read More
Best Response
Further to my previous post I have managed to adapt the code to do what I required: 1. Create a pdf of ActiveSheet 2. Save pdf into a specific location 3. Name the pdf wi... Read More

Hi Team,

 

Request you to provide me a professional training on excel.

59 Views
1 Reply
Excel Campus seems to have a good training program. Visit them here: https://www.excelcampus.com/ Read More

I've written an Excel add-in in c++ that uses a .CHM file as a help resource, this worked fine in Excel 2010 but after installing Excel 2016 it appears that online help is the preferred option and my help file is ignored. As my colleagues use the add-in i

... Read More
83 Views
2 Replies

Are you sure it isn't simply Windows blocking the chm file?