Macro Code Only Works If Specific Worksheet Active

Apr 30, 2008

I have searched the FAQ's but have not found a suitable answer to my problem. I have some code that works perfectly when it is run from the VB Editor but when I put it behind a command button it gives me an error almost straight away. I have read that when a command button is used the command button defaults the active sheet to the one that it is one therefore you always have to specify the active sheet but I have done this so am still confused as to why it is falling over. Below is my code, I have commented where it is tripping:

Sub FormattingAcutalReport()
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesMF Consolidated Risk DAILY LIVE DATA FROM BO.xls"
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesDaily Non Banks LIVE.xls"
Dim myBorders() As Variant, item As Variant
Set SEGNSEG = Workbooks("Todays Reports.xls").Worksheets("Seg and Non Seg Bank Summary")............................

View 2 Replies


Macro Code To Change Active Worksheet

May 12, 2006

I have a workbook with about 12 worksheets within it. I am trying to input a button on worksheet 3 that when pushed would activate worksheet 12. Basically the same as if you clicked on the tab at the bottom of the screen called worksheet 12.

I realise that you have to create a button which Ive done. However I cannot seem to figure out the proper macro code to get the button to change the current worksheet when its pushed.

View 4 Replies View Related

VBA Code For Adding A Specific Worksheet Condition Within Subfolder Consolidation Macro

Aug 22, 2013

The issue is the I want to put the condition here in my the code that only copies the desired cells if the sheet is named specifically such as "Jan", if not named as this the worksheet should not be used. The current code I am using was posted on this site in 2009: VBA to copy specified cells from all the files in a folder

My modified code is:

Option Explicit
Public strSourceFldr As String
Public EachFile As Object
Public objFSO As Object
Public objFolder As Object
Public objFile As Object
Public strSheetName As String

[Code] .........

View 1 Replies View Related

2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others

Jan 27, 2009

I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..

View 2 Replies View Related

Macro To Change Name Of Active Worksheet?

Mar 27, 2014

Say i have a Worksheet named "gateway" or sometimes it will be "gateway (2)" (3) and so on. Is there a macro that i can call that in some ways calls the active worksheet and renames it to just "gateway" everytime?

View 2 Replies View Related

Making Worksheet Active With Macro

May 12, 2006

Basically trying to make change the active worksheet using a macro code. I want the user to be able to click a button and it will automatically switch the worksheet to another within the workbook.

View 5 Replies View Related

Advanced Filter Active Sheet Macro Code

May 9, 2008

the macro mentioned below has been assigned to a combo-box form. I have 2 different datasets in the same sheet, they have the same headers (but named differently, myhead and myhead1) and the same adv filter criteria range (named as "dcrit") is applicable to both datasets. So, if an item is selected from the combo-box, both the datasets should get filtered according to same criteria. how i can combine the 2 IF LOOPS mentioned in the code?

Sub myfilt()
Application. ScreenUpdating = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
If Not (Range("indsignal")) Or Not (Range("countsignal")) Then
Range(Range("myhead"), Range("myhead").Offset(1, 0).End(xlDown)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False
End If
If Not (Range("indsignal")) Or Not (Range("countsignal")) Then
Range(Range("myhead1"), Range("myhead1").Offset(1, 0).End(xlDown)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

View 3 Replies View Related

Rename Active Worksheet From Personal.xls Macro

Mar 12, 2009

I am having trouble renaming an active sheet from a Macro I stored in the personal.xls file. I want the active sheet to be renamed to "Data_Source" and then the rest of the code can kick in. Instead of renaming the current worksheet it creates a new one.

View 4 Replies View Related

Macro To Filter Table Using Active Worksheet Name?

Jun 7, 2013

I need the macro to filter a table using the name of active worksheet as criteria. The code that I am writing is as below, but it doesn't seem work:

ActiveSheet.Range("$A$1:$AE$421").AutoFilter Field:=19, Criteria1:="Activesheet.Name"

If I hard quote the name of the sheet then, the macro, unchecks all the criteria in the filter and does not show and row in the table.

View 1 Replies View Related

Macro To Create Pivot Table On Active Worksheet?

Jun 20, 2012

I have recorded a macro to create a pivot table. I thought I had it so that it would create the pivot from the active worksheet only. But looking at the code, it is picking up the sheet name from the one I recorded it from

Sub SalPiv()
' SalPiv Macro
' Macro recorded 20/06/2012 by imccormick


View 1 Replies View Related

Excel Macro (VBA) Sending Active Worksheet In PDF By Outlook

Mar 17, 2014

I find out a code to create a PDF (with opening the Save As dialog box) from an active worksheet, but I can't find out how to send this PDF by e-mail (Outlook). The code is working till the words 'Set OutApp'.

Just what I want is to send the active worksheet as PDF (as attachment) by email (Outlook). Here the present code.

Sub SendPDF()
' SendPDF Macro
Dim OutApp As Object
Dim OutMail As Object
Dim v As Variant
v = Application.GetSaveAsFilename(Range("E2").Value, "PDF Files (*.pdf), *.pdf")


View 9 Replies View Related

Macro: Process The Cells For Every Worksheet In A Book Rather Than Just The Active One

Apr 2, 2007

I need to get this macro to process the cells for every worksheet in a book rather than just the active one

Public Sub test()
Dim Lr As Long, i As Long, x As Range, _
v1 As String, v2 As String, v3 As String
Set x = ActiveSheet.Cells.Find("*", searchdirection:=xlPrevious)
If x Is Nothing Then Exit Sub
Lr = x.Row
Application.ScreenUpdating = False
For i = Lr To 1 Step -1
v1 = Cells(i, 2)
v2 = Mid(Cells(i, 3), 1, 1)
v3 = Cells(i, 4)
If v1 "OP00" Or v2 "L" Or v3 "CC" Then Cells(i, 1).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Excel VBA - Applying Code To Specific Worksheet?

Jul 23, 2013

I have a number of different modules. Each module does something similar, but different to a specific workbook. Instead of running the macro(s) individually is it possible to have a parent-class that calls on each module, and further to have each module *know* which worksheet it should apply to?

Example below:

Sub Cost_Center_Information_File()
'What it does: applied to a worksheet (there are about 15 worksheets in the workbook) it
'will do some formatting manipulations.


View 4 Replies View Related

VBA Code For Selecting Specific Cells Within Worksheet?

Aug 28, 2013

know the VBA script to select only specific cells within a worksheet. To be more precise, on sheet 1 - A2, A6 and A10 cells are filled with red color. I want only those cells which are highlighted in red to be selected, copied and pasted to sheet2.

View 3 Replies View Related

Macro - Paste Formula Into Another Worksheet Using Active Sheet Name As Reference

Jan 9, 2014

I'm using a macro to paste a formulae into a destination cell on another worksheet.

The problem is that I need the Macro to put the name of the active sheet into the formulae


The macro has to work whis way becuse I will be using it with multiple worksheets, all with different names.

View 9 Replies View Related

Macro To Copy Six Specific Columns From One Worksheet To Another Worksheet

May 12, 2008

What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)

The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.

In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas

View 9 Replies View Related

Making Existing Code Work For Specific Worksheet/s

May 30, 2013

I would like the following code to be run for all those sheet/s that has a name = "single" (Not case sensitive neither an exact match) of my active workbook.

Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("I" & i)
If .Offset(, -1).Value = 1 Then .Value = .Value & "-"
End With
Next i
End Sub

View 3 Replies View Related

Code Works In One Workbook But Not Another

Feb 19, 2009

This code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?

Sub CopyFormatMMT()
'Colour code Owners
Dim r As Range
Dim f As Range
Dim c As Range
Dim j As Range
Set r = Range("ColourRangeMMT")
Set f = Range("ColourIndex")
Selection.Interior.ColorIndex = xlNone
For Each c In r.Cells
For Each j In f.Cells
If c = j Then
c.Interior.ColorIndex = j.Interior.ColorIndex
End If
Next j
Next c
End Sub

View 2 Replies View Related

Code Only Works Every Other Time I Run It

Mar 19, 2007

I am trying to programmatically select an item from a single selection listbox (i.e., set to fmMultiSelectSingle). The following code works properly only every other time I run it; the problem is extremely repeatable.

Const storeStartCell = "B5000"
Private Sub ListBox_CounterTOs_Click()
selectedTOName = CStr(Mid(Worksheets("Sheet1"). Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 3, 3))
selectedTONumber = CInt(Mid(Worksheets("Sheet1").Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 6, 4))
ListBox_TO_Name.Value = selectedTOName
ListBox_TO_Number.Value = selectedTONumber
End Sub

The times that it works, ListBox_TO_Name.Value is set to something like "ABC" and ListBox_TO_Number.Value is set to something like "1234". When the values get set properly, the associated click subroutines (e.g., ListBox_TO_Name_Click()) get called automatically when the value is set. The times that it doesn't work, they are both set to "" and the click subroutines do not get called. I also tried

myListbox.Selected(myIndex) = True

View 4 Replies View Related

VBA COde That Works For My Accounting Journal

Apr 22, 2007

I am trying to create this macro for my accounting journal What I want to happen is that in my sheet1 if the 1st cell in column a is "CASH" then the whole row should be copied and pasted in sheet 2. i want this to happen from the first cell in a column until the very last data in column a which means i am not certain up to what row number it will have data since this is a journal with uncertain number of transactions.

View 9 Replies View Related

Small Code Works For One User But Not Another

Oct 5, 2009

This snippet of code works fine when I run it. When another user uses this same workbook and runs it it gives an error:

This is the line highlighted by excel:

I have tried changing it to:


View 9 Replies View Related

Activate Workbook Code Only Works On Some PCs

Oct 20, 2007

I wrote code to update some workbooks. The code opens the workbooks and then activates the workbook to add the update.

I was tired when I wrote the code to activate the workbook and it is written:


The updates have already been sent out and it is not working on some computers. (If I change the code to


it works fine.)

Is there some option in the VBA editor that I can have people change on their computer so the code will run? Why does it work on some computers and not others?

(Unfortunately, rewriting the code to add ".xls" and resending everything isn't an option.)

View 7 Replies View Related

Formula Works On One Worksheet But Not The Other?

Jan 25, 2014

The formula I'm using is


But it doesn't work at all on one worksheet, half works on another, and works sometimes on another.

In the worksheet attached called workbook 4, it works a15 sheet 2, but not a10 sheet 2. But that was not always the case. In the worksheet attached justification copy, it works in a14 sheet 4 but not in a10 but for a good while it didn't work in both. In my own private doc it doesn't work in both cases.


View 6 Replies View Related

Code Works In Debug Mode But Will Not Run Through When Played

Mar 13, 2013

I put together about 10 separate macros that will log you into a site using the values in a given cell. I was having trouble with the last one because of AutoComplete remembering the username. So I put in an IF statement, and when I used F8 to go line for line, it works perfectly. However, when I click F5 and just let it play through, it doesn't log in. I tried adding a 5 second delay, but that didn't seem to work. I still get a run-time error when I hit play.

It get hung on this line:

If doc.getElementById("user_name").Value = cUsername Then

But the complete code is this:

Sub StreetLinks_Login()
'On Error Resume Next

Dim cURL As String
cURL = Worksheets("Sheet1").Range("B9").Value

[Code] ..........

View 7 Replies View Related

Find Method Code Works Only In Immediate Window

Sep 1, 2006

I am trying to write a function that searches a column for an index then sums the value in a corresponding column. This function works when run in the immediate window of VBA but when I use it in the spreadsheet it can't find any rows.

The reason I need this formula is because there are more than one rows that can be found.

Function SumIndex2(ByVal sField As String, ByVal sIndex As String) As Double

Dim i As Integer
Dim dSum As Double
Dim rng As Range
Dim firstaddress As String

View 5 Replies View Related

Formula Works In VBA Screen, But Not On Worksheet

Dec 2, 2007

Private Sub Zero()
If Range("G8").Value = "" Then
Range("G8").Value = "0"
End If
End Sub

I have this listed in as code for VBA. If I hit the "Run Sub/User Form" button on that screen, with the field G8 having nothing in it (having hit "Delete" just prior), it resets the field to "0".

But when I hit delete on the worksheet, the field just sits zero, no nothing.

View 9 Replies View Related

Assistance With Creating A Specific Macro Using VBA Code

Dec 8, 2008

I was wondering if someone could help me generate a Macro to do the following:

I have a sheet with the following characteristics.

Column A, rows 8-15 contain headers
Column A, rows 17-24 contain headers
Column A, rows 26-40 contain headers
Column B to CV, row 6 may or may not contain an "X"
Column B to CV, rows 8-15 & 17-24 & 26-40 may or may not contain the various data

I need a macro which does the following on the press of a button located somewhere on the sheet:
Generate a .TXT file in the folder C:Test with the name "Test_YYYY_MM_DD_HH_MM_SS_Full.txt" which uses the system time and date to fill in the values In the TXT file the following data should be created: ....

View 7 Replies View Related

VBA Code Works Great Until Number Of Rows Changed

Jan 21, 2014

I wrote the following basic code earlier (which will end up being part of a larger code)

Dim DateInput As String
Dim LastCell As String

Sub LastRowInputBox()

LastCell = InputBox("What is the last row number in the range?", "Last Row Input")

[Code] ......

As you can see, all the ColumnCopyInsert Subroutine does is copy select/copy four columns (E:H), inserts the copied selection immediately to the right, inserts formulas and autofills down to the last row required. I added the LastRowInputBox routine as the range of rows varies from week to week (inserting new rows, deleting others).

The code works great...up to a point unfortunately. If ran as it is, everything that should be copied/pasted will be and cells autofilled to the row specified via the InputBox. Here comes the problem, if any rows are either inserted or deleted then the code throws out "The object invoked has disconnected from its clients" when it reaches the first instance of the following line:

Selection.Insert Shift:=xlToRight

At which point, Excel hangs (or maybe in some sort of loop) and I have to open Device Manager and close the Excel exe process.

View 5 Replies View Related

Run Worksheet Specific Macro Only On One Sheet?

Apr 15, 2014

I'm trying to add some code so that it only runs on the specific sheet or a range of specific sheets - but this list might change in the future) as a worksheet event... When I have been doing this in the past, I have been copying the code to each of the individual sheets but this now seems to be incorrect.

This time, though, I have created the code on one sheet (with a Private Sub Worksheet_Calculate() event) which launches a macro in a separate module - my logic being to have as little code as possible in each of the sheets for copying, pasting, editing purposes, and centralise the main code in the module. However, I've noticed that this code is actually being run on all the sheets - even those I do not want it to run on. I thought, while developing this code, that it would only run on the one sheet it had been added to (e.g. right click the worksheet name, view code).

View 7 Replies View Related

Opening Specific Worksheet Using Macro

Mar 6, 2014

I'm having a problem to use the macro for opening a specific worksheet in a workbook. My plan to setup the workbook to open the first worksheet no matter which worksheet that was last saved and close.

What i have in mind is like below macros but it is not working.

Private Sub Workbook_Open()
End Sub

Attached is the sample for the workbook. Test file.xlsm‎

View 3 Replies View Related

Copyrights 2005-15, All rights reserved