I have 2 workbooks open in the same excel session. each with its own unique name. The macro looks for a specific name and then copies data from this workbook to the 2nd workbook. For some reason it no longer like the name of the workbook #1 which has not change (as far as I can tell) When I run the macro i get the run time error '9' subscript out of range, when i click on the debug this is what is highlighted and underlined.
View 5 RepliesSub display_all_false()
With Application
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.CommandBars("Control Toolbox").Visible = False
.CommandBars("Drawing").Visible = False
End With
End Sub
But, when I run the code, seems there're run-time errors detected at these codes:
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
1. Is there any way to correct these codes?
2. Does the bolded codes applied to ActiveWindow (Worksheet) only (eg. With ActiveWindow ...)? Can I change it to ActiveWorkbook (perhaps)?
3. Can the command menus (File Edit View Insert Format ...) be hidden also?
4. Does looping method for each worksheet
Read “My Documents” Path And Use Result
Problem:
Note:
Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003
1. Corporate network security settings will only allow directory/subdirectory creation in the “My Documents” section of customers individual computers.
2. Per customer request, VBA application needs to save extracted files for future use.
3. I can specify an initial “My Documents” subdirectory be made and the VBA application file be loaded/copied into that location – i.e. – “My DocumentsCat”.
4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:
Dim filepath As String
filepath = ThisWorkbook.Path
As an example – this code would produce a string definition of “filepath” – such as the following:............................
I have a macro that copies data from several workbooks saved in a particular folder to a master workbook.
The problem is that this macro only works when it is included as a module in the master workbook. Unfortunately, I cannot ask the users of this macro to insert a module, copy the code and run it each time they want to consolidate the master workbook. Therefore, I wanted to use this code by including it in a Add-In that I already prepared. The problem is that this consolidation macro does not run when it is in an add-in. Create a code that I can include in a macro add in that would insert a module in the master file, include the code that I already have, run it and then delete the module? The add-in would be password protected.
I currently have the following macro running to set a chart's data values:
Sub C3Quarter12013()
'
' C3Quarter32013 Macro
'
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = _
[Code] ......
When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.
I've tried changing the sheet name to ActiveSheet.name but that doesn't seem to work.
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
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..
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)
[Code]....
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"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
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.
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 RelatedI 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 RelatedI 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.
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
Sheets(I).Select
Sheets(I).Move
ActiveWorkbook.SaveAs Filename:= _
ThisFN, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Next ws
End Sub
I am working on an application where I have several workbooks open at the same time. I need to know which one is active. How do I do that?
View 3 Replies View RelatedI have an add-in (MyAddin.xla) that is called from my open_workbook event in my workbook (MyBook.xls):
Private Sub workbook_open()
AddIns("MyAddin").Installed = False
AddIns("MyAddin").Installed = True
ThisWorkbook.Activate
Run "MyAddIn.xla!WbookOpen"
End Sub
I put this in my WbookOpen procedure:
Sub WbookOpen(pWorkbook As Workbook)
MsgBox "Wbook Open Active workbook is " & ActiveWorkbook. Name
End Sub
The message displayed is:
Wbook Open Active workbook is MyAddin.xla
I thought this would be:
Wbook Open Active workbook is MyBook.xls.
I've manage to do this a while ago, unffortunaly my hard drive got damaged.
View 3 Replies View RelatedHow do i calculate only the active workbook in VBA?
simple question, but i'm really slow today.
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
I have a number of files which I am opening, manipulating and copying the result to a master workbook
I am attempting to use code like this
Selection.EntireRegion.Copy Destination:= Workbooks(Master). Range("A" & LastRow)
Is it posible to define the value of LastRow without making Master the active workbook?
Is there a way to set up a cell selection event trigger through an addin when it is installed so that I can respond to selections made on user's sheets?
View 3 Replies View RelatedCopy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls
The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as
Report1_11-03-08.xls. Then just continue incrementing.
Sub CopySave()
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls"
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
How can I save only the active sheet of a workbook in VBA?
View 9 Replies View RelatedI have 16 proposals that are either hidden or visible in a proposal generator. I need to be able to copy only the visible worksheets over to a new workbook that uses a name from a cell inside the proposal generator. Then a save as prompt to force the user to save the file wherever they choose.
View 14 Replies View RelatedI 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 RelatedI have this script below that removes all code from the active workbook. The reason I am removing all the code is because I am running code from my personal library and the code changes the worksheet I am working with.So while stepping through I noticed it moves over to the sheet change sub routine from the active workbook that I am working on.
So I tried to remove all the code from the Active workbook but after I remove all the code it returns a expected end of sub error, where the old worksheet change sub was? but no code is in the entire workbook? So I tried to enter something in the blank sheet mod and it returns a (Cant Edit Module) error.
An Excel file is e-mailed to my company for all the credit card transactions in a given day. Because we manage many stores and each store is a different legal entity and a different fiscal year ending, I need my macro to sort the information and separate the transaction by store and by fiscal year end. I just hit my first block.
I need to open a workbook off the server to grab information for a vlookup formula. I can get the workbook open, but I can't get back to the original workbook or find a way to reactivate it. Because the macro is going to be saved in the "personal" workbook, I can't use the "thisworkbook.activate" code. Also the workbook name and tab name that is emailed to me will always be different so I can't use other solutions that I've seen posted.
I work in Excel with a VBA script. The purpose of my script is that every 5 seconds a procedure will be executed. This works well. If my workbook is not active, it does not work. Or when I work with another workbook. Or when I work with another program. Then the script will stop to execute. It looks like a break. I have tried several scripts that have the same effect. I want the script continues normally with execution.
View 3 Replies View RelatedWith first code user selects a sheet from drop down and that sheet is activated. (This works fine)
Now hide all other sheets is what I'm trying to do with the second code. (This IS NOT working)
I assume I can either put the second code in the change event code or call it from the change event code once it works.
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$A$1" Or Target.Cells.Count > 1 Then Exit Sub
[Code].....
I have a function that is called in Access vba code that refreshes all sheets in a given workbook. We use it all the time for workbooks containing any number of tabs to be refreshed. The code has a key line: ActiveWroksheet.Refreshall. I need to now start to secure the pivot so the user can not refresh it. Another post pointed me towards: .PivotCache.EnableRefresh = False.
This apparently can only be used for a particular sheet; ie it can not be applied to ALL sheets in the workbook. The trouble is the function is generically used to refresh any workbook, and the number of sheets may vary.
Any thoughts on how I can disable the ability to refresh a pivot for ALL sheets. I'd prefer not to turn off the pivot wizard since the user should be allowed to manipulate it...just not refresh it.