VBA - Delete Sheets In Active Workbook According To Combobox Value

May 8, 2014

That particular question has been solved, but now i need it to work with multiple values from combobox.

So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7

The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"

WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.

Run-time error '-2147417848 (80010108)': Automation error

The object invoked has disconnected from its clients.

When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate

View 5 Replies


Delete Sheets In Active Workbook

Nov 27, 2008

I want to create a macro to check through all sheets in an active work book, if a sheet is empty, then delete it?

View 2 Replies View Related

Delete Active Workbook

Oct 10, 2004

if it is possible to delete the current workbook that is open?

I'm thinking of using an add-in to delete the workbook i'm viewing

View 9 Replies View Related

Same Active Cells Between Different Sheets In Same Workbook

Jun 8, 2006

is it possible to keep the same active cell when switching sheets in the same workd book. I am in sheet1 and cell C15 is my active cell. When i move to Sheet2 it either defaults to A1 or the last place i was in this sheet. I guess it would be the Worksheet_activate() and deactivate function but not sure

View 3 Replies View Related

VBA To Import All Sheets From Closed Workbook To Active Sheet?

Aug 20, 2014

code to pull up all the sheet from closed workbook to active opend workbook.

Closed Workbook name : Create Position
Active Workbook name : EIB builder

View 1 Replies View Related

Delete Sheets And Create New Workbook In Shared Workbook

Jun 2, 2014

I wrote a code in unshared workbook and it works fine. But when i make it shared i get Run-time error '1004' Delete method of worksheet class failed.

The Deleting of worksheet only occurs once (when the new wb is created) so is there i can unshare and share it back when the process is complete?

View 3 Replies View Related

Macro To Delete Sheets In Another Workbook

Mar 2, 2009

my code to loop through sheets in another workbook does not want to work. in This Workbook (SourceWb), I copy the current workbook using SaveCopyAs and then reopen the new workbook. from ThisWorkbook (SourceWb), I am trying to delete any sheet between "First" and "Last" where a specific criteria is not met. so starting from the For Each to get my loop values, where PMs is a list of people's initials:

For Each c In Range("PMs")

If c.Value = "" Then GoTo AfterArrays

View 9 Replies View Related

On Workbook Open Delete All Other Sheets Except For Data

Oct 25, 2011

VBA code that would delete ALL other sheets in the workbook except for Sheet "Data".

View 2 Replies View Related

Active X Combobox

Apr 1, 2008

I have an activex combobox in my worksheet. I want the selected text in the combobox to be written to a defined cell when a button is clicked.

View 13 Replies View Related

Unhiding Sheets With Combobox Selection And Duplicating Sheets Automatically

Sep 27, 2011

I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.

The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.

View 9 Replies View Related

Add Item To Combobox Depends On Multipage That Is Active

May 28, 2014

I have a Multipage with 3 pages in userform1, and I just want to add an item to the combobox in userform2 depends on the multipage that is active.

I have the below code but I'm getting an error 'Object doesn't support this propert or method'

Commandbutton in Userform1

[Code] ......

View 1 Replies View Related

Pull Data From One Closed Workbook Into Active Workbook With Command Button

Feb 27, 2014

I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.

Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With

View 6 Replies View Related

Making Backup Copy Of Active Workbook While Workbook Is Open

Jun 30, 2014

I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried

[Code] .....

but get a permission denied error message.

I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..

View 13 Replies View Related

Copy Data From Closed Workbook And Append In Active Workbook?

Feb 6, 2013

I have some vba that opens a closed workbook, copies data from a named range and then pastes it to the active workbook.

However, what is happening is that the closed workbook is opened and only part of the data is pasted. What I would prefer to happen is this:

Open the closed workbook-->copy the named range-->paste(append) to next empty cell in column B.

Heres the code that I have got.

Sub Workbook_test()Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("G:WAREHOUSEPlanningSmartNew Training Plan raining plan.xls", True, True)


View 5 Replies View Related

Open Workbook, Find Sheet That Contains Cell Value From Active Workbook

Jun 20, 2008

I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...

'Dim officen As Integer
'Dim thiswb As Workbook

officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"

RowCount = ActiveSheet.UsedRange.Rows.Count

View 8 Replies View Related

Copy Of Sheet From Specific Workbook To Active Workbook

Mar 6, 2014

My requirement is as follows......

I want to get a copy of worksheet from specific workbook to active work book in which i want a copy of sheet get moved.

View 2 Replies View Related

Transfer Value From Active Worksheet In One Workbook To Another Workbook?

May 9, 2014

I am trying to transfer a value from the active worksheet in one workbook to another workbook. I keep getting a subscript out of range error on the line that uses activeworksheet command when I run my code.

View 3 Replies View Related

Copy Of Sheet From Workbook 1 To X Workbook Which Is Currently Active?

Mar 6, 2014

I have a Picture in a workbook kept open. ( workbook 1 )And i have some X workbook open....i want a copy of sheet from workbook 1 To X workbook which is currently active.

View 1 Replies View Related

Making Workbook Opened From Other Workbook Active

Nov 20, 2006

I have a workbook that is a formatted report that I need to insert data from a name variable "CSV" file. I have created a macro in this workbook that calls an "Open" dialog for CSV files that I can use to browse to and open the source file. The macro then is supposed to "select all" copy and close the workbook then make the original workbook active and paste the data starting at row 2. The macro "seems" to be working perfectly except when I do the open the macro then makes the original workbook active, selects all copies then wants to close the original workbook. How can I tell the macro that the csv file that I just opened should be the active workbook, baring in mind that the name won't be known before the time it's opened so I can't hardcode the "active. workbook (NAME) "route.

View 9 Replies View Related

Copy Worksheets From Active Workbook To A New Workbook

Apr 22, 2009

I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error.

MS VB Script error:
Runtime error '9':
Subscript out of range

Any advise would be greatly appreciated.

Thank you

Code is below..

Sub saveall()
For Each ws In ActiveWorkbook.Worksheets

ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls"
I = I + 1
ActiveWorkbook.SaveAs Filename:= _
ThisFN, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

Next ws

End Sub

View 9 Replies View Related

Delete From Non Active Sheet

Nov 22, 2011

I am not sure why this code won't delete, I can't believe excel requires the sheet to be active to delete from. It will only work if the .activate line is included.

How can you delete from another worksheet without first switching to it?


Sheets("sheet1").Range(Cells(row, col), Cells(row + 7, col)).Delete shift:=xlUp

View 3 Replies View Related

Delete Row Of Active Cell

Mar 26, 2008

I have a macro for deleting a row. I want iit to delete the row that I have selected, that is i if I mark cell B22 I want it to delete row 22. But it deletes the row under it, that is 23.

Sub Tabortrad()
Dim intRadnr As Integer
Dim intStartrad As Integer
Dim intSlutrad As Integer
intRadnr = ActiveCell.Row
intStartrad = ActiveSheet.Range("Första").Row + 1
intSlutrad = ActiveSheet.Range("Sista").Row - 2
If intRadnr < intStartrad Or intRadnr >= intSlutrad Then
MsgBox "Kan inte radera denna rad. Placera markören på en av bokföringsraderna mellan rad " & intStartrad & " och rad " & intSlutrad - 1 & "." & Chr(13) & "En ny rad kommer att infogas under den rad där markören står.", vbOKOnly, "Felaktig rad markerad".......................

View 2 Replies View Related

Do Not Update Non-active Sheets

Sep 25, 2007

I have a big spreadsheet with a number of sheets, some with dynamic lists which take up a lot of processing power, but which I only use rarely. Consequently the whole spreadsheet is running very slowly. Is there a way to set certain sheets to not automatically update, but retain automatic updating on others? I know you can set Calculation to Manual in the Options, but I need to retain automatic calculation on some sheets.

View 9 Replies View Related

Counter For (active) Sheets

Nov 27, 2008

I have a rather big worksheet with lots of sheets in it. I need to see in the frontpage how many sheets there are in total (-2, the frontpage & end sheet don't count) and how many sheets are currently shown (ie. not hidden, as there are vba-filters which sheets to display).

I don't really mind how it is shown, it could be one cell with 14/53 meaning 14 sheets shown of a total 53 or in seperate cells.

View 4 Replies View Related

Print Active Sheets With VBA

Oct 22, 2011

I am trying to select a variable amount of worksheets in the workbook and print just those.

I was trying to do something like...

Sub print_sheets()
shtcount = Sheets("Master List").Range("A278")
For i = 2 To shtcount
Next i
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

...but this wasn't really working...

View 3 Replies View Related

Run Macro Only When One Of 2 Sheets Are Active

Dec 10, 2011

I have a macro that runs when the workbook is saved, it starts to enter in certain formulas onto the worksheet, how can I have this maro run only when 2 specific sheets are active

So basically I want the macro to run if either worksheet "Sales Jan-Jun" is active or worksheet "Sales Jul-Dec" is active, if they are not the active sheets I want to exit sub

This is what I tried without success

If ActiveSheet ("Sales Jan-Jun") Or ActiveSheet ("Sales Jul - Dec") Then

Exit Sub


View 8 Replies View Related

AutoCalc Only On Active Sheets

Feb 8, 2012

Is there a code I can use that will only have autocalc functioning on the active sheets? If not, how about for only the current Files that are active?

View 1 Replies View Related

Protect Non Active Sheets

Nov 1, 2008

I have a workbook that has a sheet for every day of the month (1 to 31).

When it opens it automatically activates the sheet for the day corresponding today.

What I'm missing is how to enable users to only update that sheet.

Example today is the 1st, sheet 1 should be unprotected but all others protected and off course tomorrow it should be sheet 2.

View 9 Replies View Related

Only Print Active Sheets

Aug 20, 2009

Is there any way to only print active sheets

i mean, i have 4 sheets in my workbook, and if i only use one sheet i dont want to print all 4 sheets

i would like to print sheet 1 if cell D19 has data in it
i would like to print sheet 2 if cell D61 has data in it
i would like to print sheet 3 if cell D103 has data in it
i would like to print sheet 4 if cell D145 has data in it

View 9 Replies View Related

Delete Chart Series Across Sheets But Skip Protected Sheets

Mar 30, 2008

I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets

Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................

View 4 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved