Hello Everyone
I am having some difficulties finding a solution to my problem. I am hoping someone can help or lead me to the right direction.
I have a workbook called Data.xlsm, below is a sample:
ID, Name, commission
1 john 1000
2 eric 2000
3 ryan 3000
Now I have another excel file called Temp.xls which looks pretty much the same as Data.xlsm.
ID, Name, Commission
1 john 200
2 eric 300
I need a macro that will read the Temp.xls and grab the commissions and bring them to Data.xlsm. The commissions need to be summed based on ID.
The final Result on Data.xlsm should be as follows:
1 john 1200
2 eric 2300
3 ryan 3000
Any help would be greatly appreciated.
Thank you
I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.
I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.
You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)
Just a quick and dirty solution
Make sure you place this in the same folder as your Temp.xlsm file and press the button
It will open Temp
update the data, if a new Id is in Temp it will add the data and set the commission values in Temp to 0 to avoid running it twice.
I hope the code explains itsefl.
Think of a macro of a step by step instrcution as you would say it out loud.
Is there a macro that can be created to move the currently opened workbook to a different folder?
Just issue the save as command to save the workbook to the new location with the same or new filename and then delete the old workbook
SAVE WITH NEW NAME
Code:
Sub MoveWorkbook()
vOriginal = ActiveWorkbook.FullName
ActiveWorkbook.SaveAs ("C:\NewName.xls")
Kill (vOriginal)
End Sub
SAVE TO NEW LOCATION WITH SAME NAME
Code:
Sub MoveWorkbook()
vOriginal = ActiveWorkbook.FullName
vWB = ActiveWorkbook.Name
ActiveWorkbook.SaveAs ("C:\NewFolder\" & vWB & ".xls")
Kill (vOriginal)
End Sub
Regards,
Rollin
Hi There
I have searched the internet to see if I could find a macro that opens a workbook and runs a macro. In other words, I have workbook 1 (master workbook) and workbooks 2,3,4 ...1000. So far I found a macro that opens workbooks 2, 3, ...1000 by running a macro from workbook 1. Now I have a macro in workbooks 2,3,....1000. What I want is to run the workbooks 2,3,...1000 macro from workbook 1. I can do it if I call the workbook name. But I don't want to do that, since I have over 1000 workbooks. Below is the code.
Sub WWNumbers()
Dim fso As Object 'FileSystemObject
Dim fldStart As Object 'Folder
Dim fld As Object 'Folder
Dim fl As Object 'File
Dim Mask As String
Dim thisFolder As String
Dim fullFilename As String
Set fso = CreateObject("scripting.FileSystemObject") ' late binding
Set fldStart = fso.GetFolder("C:\Users\Mario\Documents\Folder1 Name\Folder2 Name")
Mask = ActiveCell.Value
thisFolder = fldStart & IIf(Right(fldStart, 1) = "\", "", "\")
Debug.Print thisFolder, Mask
If Not IsError(Dir(thisFolder, vbReadOnly)) Then
If Dir(thisFolder & Mask, vbReadOnly) <> "" Then
Workbooks.Open Filename:=thisFolder & Mask
Else
fullFilename = ListFolders(fldStart, Mask)
If Len(Trim(fullFilename)) > 0 Then
Workbooks.Open Filename:=fullFilename ' workbook2 opens here just fine
Application.Run "UnhideSheets" ' I get an error here
End If
End If
End If
... Read more
Hello,
I work with Excel 2010 and have a series of workbooks (preop_management_AAA.xlsm, preop_management_BBB.xlsm, preop_management_CCC.xlsm) that each have a worksheet named 'Medications.' From the 'Medications' sheet, I have a macro in each which opens a common workbook named bridging.xlsm. After doing calculations in bridging.xlsm, the user returns to the workbook from which they started and proceeds to the next worksheet in the original preop_management_xxx.xlsm workbook via a macro named 'NextFromMedications.' Only one of the preop_management workbooks will be open at any one time.
I need help writing code that will call the 'NextFromMedications' macro in the open preop_management_xxx.xlsm workbook from the bridging.xlsm workbook. While the name of the macro is the same in each of those workbooks ('NextFromMedications'), the file name is variable so if I write (in bridging.xlsm) something like
Application.Run "'preop_management_AAA.xlsm'!NextFromMedications"
the macro would only work for one of my three "originating" workbooks. I am looking for a way to "generically" refer to the file name in the 'NextFromMedications' macro.
I cannot put the 'NextFromMedications' macro in my PersonalMacro workbook since these workbooks will be used by multiple users on multiple computers.
As always, I thank you in advance for your time and consideration.
I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.
Sub Email_Out()
If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
End If
End Sub
All help is greatly appreciated!
Mikey
Read other 16 answers
I'm retrieving some data from some CSV files using a macro.
Everything works fine, but it appears that just opening a file causes the entire workbook which the macro is being run from to get re-calculated, even though I'm not even changing any values (yet)!?!?
I assume it's every table value in every worksheet which uses a formula whenever I open a file (and this is in a new window)!!! As I'm opening 7 files, repeatedly calculating values unnecessarily (7 times!!!) even though doesn't appear to take much time, it's still time better spent doing something else.
The code I'm using to open the file:
Workbooks.OpenText Filename:=bk, Origin:=437, StartRow:=1 _
, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1)), _
TrailingMinusNumbers:=True
bk is the file reference getting passed in. Bear in mind that everything does work and I'm using Excel 2007 under XP.
Any help would be greatly appreciated.
Thanks.
Read other 13 answers
Is there a way to save a workbook if a cell within a range is changed?
Hello,
I have a master workbook with 20 worksheets. 5 worksheets in the worksbook are distributed to field reps, but the remaining 15 worksheets are not. What is the best practice for receiving the 5-worksheet workbook from the field rep and importing the data into the 20-worksheet master workbook? If I merely use Copy Sheet I am able to copy the worksheets into the master workbook but then I have to remove the pathname references in each of the copied worksheets in order to map the data in the master workbook - which is quite cumbersome. Suggestions?
Hi jpirhalla, welcome to the forum.
Have you tried moving the sheets over. Dragging the sheet from the field reps back into the now-15-sheet book?
I have two workbooks that I am using and I am trying to add one cell's data to another cell in another Excel workbook. It used to be as simple as beginning to "add" another cell's data(from another workbook you just migrate to the other workbook) into a different workbook. It isn't working like it used to in Excel 2003. Cant' speak of it for Excel 2007 as I never had it.
Any thoughts on why it won't work? Any way to make it work?
Quote: Originally Posted by Lewiedude
I have two workbooks that I am using and I am trying to add one cell's data to another cell in another Excel workbook. It used to be as simple as beginning to "add" another cell's data(from another workbook you just migrate to the other workbook) into a different workbook. It isn't working like it used to in Excel 2003. Cant' speak of it for Excel 2007 as I never had it.
Any thoughts on why it won't work? Any way to make it work?
Merging Data from Multiple Workbooks into a Summary Workbook in Excel
Looks like it's possible.
Also I would look at the DATA Tab under DATA TOOLS - CONSOLIDATE.
I have a workbook that I want to share with many people so they can all edit at same time. When I share it, they cannot run macos, it errors out with
"Error 1004 Application defined or object defined error"
Any ideas on how to overcome this?
Thanks!
ASM
The macro is supposed to create hyperlinks... is this a no no? this is excel 2007...
Hi There
I have recorded a macro that opens up a workbook from my C drive as follows:
Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario Pincivero\My Documents\Homes\Salem's Point 1L 40M-2423.xls"
I also recorded a macro that opens a workbook from windows as follows:
Windows("Salem's Point 1L 40M-2423.xls").Activate
I want to combine these two macros to do the following:
If Salem's Point 1L 40M-2423.xls is aready open then do the code Windows("Salem's Point 1L 40M-2423.xls").Activate
If Salem's Point 1L 40M-2423.xls is not open then do the code Workbooks.Open Filename:= _
"C:\Documents and Settings\Mario Pincivero\My Documents\Homes\Salem's Point 1L 40M-2423.xls"
Please help
Mario
Hi Mario,
When you say you open a workbook from your C-drive.
Are you executing your macro from within an Excel workbook or how?
What do you trigeer it with or where do you trigger it from?
I am copying an excel worksheet from an existing workbook to a new workbook using a macro. The sheet contains a Private Sub. I want the new workbook to have the Private Sub password protected. Is there a way of writing this into the macro of the originating workbook?
I have an Excel macro that opens another workbook. If the macro errors out, how can I close the second workbook that was opened?
Good Day Moderator,
just got to the core... I've problem to open an excel workbook which contain macros from visual basic at run-time. Thats it, I don't know what properties or method that I should used.
For your illustration, perhaps I should write some of them in here ( hope you don't getting bored with this )
dim strFile as String
dim objExcel as Excel.Application
strFile = InputBox("Please input your excel file full path :","C:\")
Set objExcel = CreateObject("Excel.Application")
With objExcel
.DisplayAlerts = False
.Workbooks.Open strFile
MsgBox "Your name is : " & .ActiveSheet.Range("D1")
.ActiveWorkbook.Close False
.Quit
End With
While trying to process the Msgbox's function, it said that "Object variable or With block variable not set", just like if the object has not successfully created.
Do you have any idea with this ? I really don't know what to do.
Thanks a lot for your help and have a nice day
Best Regards.
I don't claim to do VBA, but I dabble.
In the first place, if you are creating this code in an Excel file, I don't think you need to declare Excel as the application at all.
I think you should be creating a workbook object and not an application object.
I just tried it out, and I get that darn Error 429, but I think that's better than what you're getting.
Using Excel 2010. I regularly download data from a database which have to be sorted by
column 1 - cell color blue on top, then
column 1 - cell color orange on top, then
column 4 - value, from newest to oldest
the data is always downloaded automatically to a workbook named "ABC", and the worksheet is always automatically named ABC#, with the # changing automatically.
the macro is saved in the personal folder. I tried to record using absolute and then as relative reference. the macro NEVER works when I tried to run in any new downloaded workbook or even I recreated the same data in a new untitled workbook.
the number of columns is always the same but the number of rows varies. there are about (7) cell colors, but only the blue and the orange need to be on top
the recorded macro always indicate the name of the worksheet and workbook on which it was created. is this why it does not work on any other workbook or worksheet?
any help will be appreciated
I have a audit template that takes two files and compairs them and opens a new workbook to display the differences. This new workbook is then saved to a specific location for audit reasons.
In the new workbook created, I'm wanting to "remove" all the toolbars and menus so a user can't safe the file to another location by mistake.
Is there a way for the creating template to "insert" macros into the newly created workbook to do this? While I could just create new worksheets in the "creating" template workbook and remove the ones I don't need, I'm trying to keep the size of the file down by not having all the macros that created the new template in the final product.
Just for some thoughts on this concept, I would suggest you read this:
http://www.vbaexpress.com/forum/showthread.php?t=26263&highlight=hide+toolbars
I was wondering if it was possible in excel to have a main main with buttons that direct you to different tabs.
Once you get to that tab, the person needs to insert the correct password for that correct tab.
I have created a macro. It is located in the view code right next to File in the upper left hand corner.
Is it possible also, that if the administrator enters in a password somewhere in the Sheet 1 where all the buttons are, that the administrator can have access to all of the sheets without inserting a password.
The password for Sheet 2 is Sheet2.
The password for Sheet 3 is Sheet3.
You might want to read:
http://www.officearticles.com/misc/beginners_guide_to_getting_free_help_on_the_web.htm
Particularly, the part about "Projects".
Anyway, sure you can do this, but it'll take some code and not everyone has the time (or is willing to spend the time) to write that code FOR you. You can, however, check this out:
http://vbaexpress.com/kb/getarticle.php?kb_id=531
Use a sample file. Then, if you can't get it to work perfectly, ask at that forum (OR HERE) for additional help, uploading the sample file.
I hope this kick-starts you.
Hello, advicer
I do not understand why sometime I cannot copy a range from a workbook to another workbook in EXCEL (but I can if i paste the range in the same workbook). My PC is in the network of the company using WinNT 4.1. Please help me
Many thanks
Qan
How will I write a macro in excel that will enable me to save a current workbook in a specified directory and use the wording in a cell on the spreadsheet as the file name.
I have tried copying a macro and have got as far as the new directory, but do not know how to name the file after wording in a cell in a spreadsheet.
Just one simple line. Just change the portions in red to reflect your true save path and cell to use. You may need to also change the xls file extension if you are using Office 2007 or newer.
Code:
ActiveWorkbook.SaveAs Filename:="[COLOR="Red"]C:\TEST\[/COLOR]" & Range("[COLOR="Red"]A1[/COLOR]").Text & ".xls", FileFormat:=xlNormal
Mods please delete this!!!!
Double post, sorry...
The original post can be found here.
Thanks.
I have XP and just installed Office 2007. In order to get a 2003-like environment, I set up a customized Quck Access Toolbar that is meant to come up every time I open Excel. I also have a Personal Macro Workbook I wish to automatically open every time I open Excel.
To do this, I set them up as Add-ins in the ...\Application Data\Microsoft\AddIns\ directory:
* PersonalMacroWorkBook.xlam
* MyQAT.xlam
The issue is that when I launch Excel directly (say, from the Start Menu), the two add-ins open and work. However, if I launch Excel by directly opening an Excel file, neither of the two add-ins are opened. [Note that this happens whether I'm opening file with extension .xls, .xlsm, or .xlsx.]
One other point - this used to work just fine. The issue arose recently - and I think I can trace it back to Excel crashing a few days ago. It crashed, incedentally, independent of any action by me - I was actually working in a different application at the time.
Can anyone tell me what's going on? Can you help me fix this?
Thanks,
angrybuddha
I am getting a message, when I close Excel 2016, asking if I want to save changes to my Personal Macro Workbook, but I have not made any changes. Is there any way to get rid of this message, other than to click Save (I would have no idea what I am saving).
T
Hello
I have a corrupted Microsoft Excel Macro-Enabled Workbook xlsm file. Does anyone know how i can restore or recover it please?
You can see here http://www.mathworks.com/matlabcent...rosoft-excel-macro-enabled-workbook-xlsm-file
Hi all,
I really need help with this, as I have tried means to get it right, but I have no experience in VBA and it seem difficult. Any help will be greatly appreciated.
I need to copy specific cells in a form (in Excel format) that is input by users and paste them into the summary workbook.
The form is fixed, but every time someone sends in a new form, I'll need to update the data in a summary workbook. So it'll be constant updating and I need to ensure that a new row in the Summary folder is used for each form that is sent in.
I'm trying to write a macro that is able to automate the data transfer.
Eg. I need to:
copy the data from D6 in the file Form to the celll A2 Summary file,
D7 in Form to B2 etc.
Attached is the form and my summary sheet.
Thanks in advance!
Hi all,
Below is the vba code that I have written.
But the problem is I am not sure how to define the destination workbook as a file, which is also where this macro will be stored.
It does not seem to work when I tried to put ThisWorkbook or Activeworkbook. I did not want to add a new workbook either.
Sub copyWorkbooks()
Dim MyPath As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, DestWks As Workbook
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long
Dim SaveDriveDir As String
Dim FName As Variant
' Set application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
SaveDriveDir = CurDir
' Change this to the path\folder location of the files.
ChDirNet "C:\Documents and Settings\chinba\Desktop\ASL Exception\"
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
MultiSelect:=True)
On Error Resume Next
'find the last row
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
If IsArray(FName) Then
'Loop through all files in the myFiles array.
For FNum = LBound(FName) To UBound(FName)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(FName(FNum))
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resu... Read more
Hi... I'm somewhat new at Excel and really new at Macros.
This is what I'm trying to do:
I have two workbooks -
on the first workbook I have about 1000 rows of informtion with the following six colum headings...
ID# Last First Mid Dorm Decal#
The first five colums are all filled in already. I have to put the Decal#'s in as I get them.
What I need is that when I put a Decal# in (lets say F23) for a particular person, a Macro (or Formula) would then trigger that row (A23:F23) to be copied and then pasted to the next available blank row on a sheet that is located in a different workbook.
The first workbook is called -
Student List_Decals Input (SheetName is ResidentCommuterList)
The second (target) workbook is called -
Veh Plate_Decal Info Master (SheetName is Master Plate Info List)
I don't know if a Formular can handle this type of thing or if a Macro would be better... but if anyone has a good suggestion, I would be greatful.
thanks
I have added many tiles on the Start Screen for applications that I use regularly. I?ve also added quite a few shortcuts to Access databases that I use. I?ve also performed this process for an Excel workbook that I use regularly. They have
been working for years. About three months ago, the tile to the Excel workbook would launch Excel but not open the Excel workbook.
Several times I have unpinned the shortcut, deleted the shortcut from the Programs folder. I then recreated the shortcut to the Excel workbook. I tested the shortcut to ensure that the workbook file was loaded. It worked. I copied that shortcut to the Programs
folder where the tiles that came with Windows 10 and the ones I added are located. I retested that shortcut. Again, it worked by launching Excel and opening my workbook. Lastly, I pinned it to the Start Screen. I moved the tile to the desired location.
I click on it to test it. Excel is launched but the workbook is not opened.
I have 12 tiles that point to shortcuts to Access databases that I have created using the same process. They all work and continue to work.
Does anyone have a solution to this problem or even ideas that may lead to a solution?
Thank you.
Hi,
Apologies if this has been asked before.I have two different workbooks which I need to link I need to set up an arrangement whereby the data from a particular cell in one workbook is displayed in preferably pop up box in another workbook.
I have attached dummy versions of both the workbooks below.
1.The spreadsheet titled "Issues Log" has a list of issues,each with a unique identifier(column A) which is linked to a unique cab no (Column B).A brief description of the issue is given in Column C.
2.The spreadsheet titled "Link to Display Spreadhseet" has a list of CAB numbers (Column A) which corresponds to the "Issues Log" but the order is jumbled up.
In column B ,I need to place a link which will display the "Unique Identifier" code from the "Issues Log" corresponding to the cab number.When a person clicks on this Unique ID code ,one of the two things should happen: i.either the breif description of the issue (Column C in the "Issues Log") should pop up in a box.or
ii.Clicknig on the link will open up the issues log.
I hope I am clear in what I want.i am ok with using long winded formulas,tried some combinations of Match,IF etc but always hit a brick wall.I am not good with Macros,VBA.Any help/pointers would be greatly appreciated.
I am using Excel 2007
regards
Hi,
Just a question, since you need to know what is the issue with its description. why do need the unique identifer for that and linked it do description. It might be a silly question, but please forigve me for that.
In case you don't need the unique identifier column, you can use vlookup for the populating the details.
Regards,
Tushar
Does anyone know if it is possible to have a VBA code run after a workbook's name has been changed?
I was able to get what I wanted done by using the BeforeClose event.
I have the follow code behind the ThisWorkbook_Open Event:
Code:
If Weekday(Date, 1) = 6 Then
'Do nothing
Else
ThisWorkbook.Close
End If
Is this the best way to perform this operation?
Also, if the day was not yet Friday, how would I keep the workbook from closing if I needed to open it for modifications?
I have a row of dates against a surname and first name in one workbook that I need to organise. For example:
Graham Mitchell 1/12/07 31/2/08 6/6/08
Sam Knowles 1/11/07 6/1/08 4/4/08
I then need to organise them in a different workbook as follows:
November December January February
Graham Mitchell 1/12/07 31/2/08
Sam Knowles 1/11/07 6/1/08
What formula would I use to look at a line of dates in a workbook and put information into the cell if the month & year are the same but leave it blank if it different??
Hope that I explained this okay. I am using Excel 2007 by the way.
Lisa92
Hi all.
As I have solved a few of our IT problems at work, I am being held as our expert...thus expected to know how to do everything the managers want done!
About 80% of our computer files are done in excel, but there are a few workbooks which contain sensitve data on our customers, so the manager wants them password protected.
I have looked at the options in excel, and tried the protection option, but that just seems to stop people from making changes...they can still access and read the file.
I don't have excel at home so I can't play around with it to find out the answer myself.
I looked at the sharing/security properties, and that seems to choose which pc the file is available to....we need it available to all 3 pcs, as we just use whichever is free, but that it can only by opened by myself and the 3 managers.
We all use one standard log-in, so I can't make it available just to certain users.
Thanks in advance!
I have created a userform within a workbook that contains a command button. The comman button on the user form is coded to open another workbook.
This all works fine. The problem is that the code behind the Workbook_Open event in the second workbook does not run.
Hoe can I get the code in the Workbook_Opem event to run when the second workbook is opened?
I have an Excel workbook. What I would like to happen is find some way to open and close the workbook first thing in the morning. There is code that runs when the workbook is opened.
Does anyone know a good way to perform this operation?
I got the below code from http://www.rondebruin.nl/copy6.htm site.
Is there anyway of modifying this code so it saves each work with a specified password?
This process is carried out every month and there are 2 lots of 242 (1 set of new account and 1 set of renewal account for loans) new workbooks created so takes an age to open them all and resave with a password.
Thanks in advance for any help.
Sub Copy_Every_Sheet_To_New_Workbook()
'Working in 97-2007
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim DateString As String
Dim FolderName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
'Copy every sheet from the workbook with this macro
Set Sourcewb = ThisWorkbook
'Create new folder to save the new files in
DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")
FolderName = Sourcewb.Path & "\" & Sourcewb.Name & " " & DateString
MkDir FolderName
'Copy every visible sheet to a new workbook
For Each sh In Sourcewb.Worksheets
'If the sheet is visible then copy it to a new workbook
If sh.Visible = -1 Then
sh.Copy
'Set Destwb to the new workbook
Set Destwb = ActiveWorkbook
'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143... Read more
'Save the new workbook and close it
With Destwb
.SaveAs FolderName _
& "\" & Destwb.Sheets(1).Name & FileExtStr, _
FileFormat:=FileFormatNum, Password:="xyz"
.Close False
End With
Spent some time and worked it out - lol
Added in the bold section into the save section of the code.
Workbook Size
Using Windows 7 Ultimate 64 bit
C: Drive...873 GB free of 931 GB
Ram: 8 GB (7.9 GB Usable)
MS Excel 2007:
I have one workbook that has become very slow to Open and then to Save and then to Close.
When right clicking on the desktop icon for this workbook, I see it has 16.7 MB of data.
Takes as long as 15 seconds to open usually.
I have used the "Formula....Check Errors" and corrected any errors in the WB.
Is this too much for the system to use have?
Any suggestions?
How can i print multiple worksheets in an Excell 2007 workbook. Thanks in advance.
Hi there!
Yes, you can print multiple worksheets. Just select the ones you want by clicking on the tabs at the bottom of each worksheet that you'd like (holding down Ctrl will let you select more than one, or Shift will select all sheets in between the first and last one that you click on). If you go to your Print dialog box, it should have the 'Active Sheets' radio button selected in the 'Print what' section. This will print every worksheet you've selected. Alternatively, you could select 'Entire workbook' to have every worksheet in the file print out.
Hope that helps!
Excel problem – bottom of the workbook is off the screen.
Some workbooks, when I open them, the bottom of the sheet is off the screen and I can’t move the top of the workbook high enough to expose the bottom.
Might try setting a higher display resolution long
enough to adjust the size of the window,then
switch it back after if everything is too small.
I wonder if somebody has come accross a solution for the following:
I have a workbook with quite a lot of macro's and functions (vba).
To simplify matters I use named ranges for easy reference etc.
One of these functions adds a number of cell controls with certain sheets, but when I open another workbook, I need these options deleted since they will not function there.
This works perfectly if I change to a worksheet that does not need this within the same workbook, but of course, when I open another workbook the function will not recognize the named range and throws an error (I avoid these by usin the on error ...)
My question is, is there an option or way to hardcode the workkbook's name togehether with the named range when defineing it, so that when that particular range is referred to the code 'understands' where to look for it?
Something like "My other workbook"!Range("NAMEDRANGE")
If you use Refer.Name it only refers to the sheet but not the workbook.
Thanks in advance.
i have 2 workbooks entitled [price] and [fruit] respectively
[price], sheet1!, cell A1 is a variable which can contains a cell address, eg $D$1
[fruit], sheet1!, contains information in cells as follows:
D1 = apples
D2 = bananas
D3 = cherries
in the [price] workbook, i want to have a formula in say, cell B1, which would use the cell reference in A1 to find the value in the [fruit] workbook.
so, if [price]sheet1!A1 = $D$1
then [price]sheet1!B1 = apples
note that the result "apples" was pulled from another workbook, [fruit]sheet1!$D$1.
if [price]sheet1!A1 = $D$2,
then B2 = bananas
and so on...
in the [price] workbook,
As A1 is a variable, i do not want to physically retype the value within A1, but rather draw reference to it inside of a formula. i hope this is clear as mud.
Is this possible?
Thanks,
markus
In Sheet1!B1 (of Price), use
=INDIRECT("[Fruit.xls]Sheet1!"&A1)
, where A1 contains the cell reference -- $D$1, $D$2, $D$whatever.
As explained yesterday, this (the INDIRECT function) will only work when both files are open ; to get it to work when the source file if closed, you need to download Laurent's add-in. Please do not start threads over.
Rgds,
Andy
I am having trouble running a macro from another workbook from a macro inside the workbook I am using. For the record, all macros are enabled and there are no macro security concerns. Also, using the Personal.xlsx or whatever the extension is not an option.
I've tried just opening the workbook in my code and calling the macro and also creating a new excel object and opening the workbook in that object. Both produced Run-time error '1004': Cannot run the macro "blah" The macro may not be available in this workbook or all macros may be disabled.
I know this is possible, as I've done it before, I just can't figure out why its not working now. This is the first time I've tried that at my place of employment... Any help would be very much appreciated!
Both code sets are below, for your reference:
Create New Excel App
Code:
Dim appXL = Excel.Application
Dim EphesusWB as Workbook
Set appXL = CreateObject("Excel.Application")
With appXL
.Workbooks.Open ("Z:\Business Solutions\Scott\Tax\Tax Workbook\Development\EphesusDownloadTakingLongTime\Ephesus_Report_Downloader-TaxWB.xls")
Set EphesusWB = appXL.Workbooks("Ephesus_Report_Downloader-TaxWB.xls")
With EphesusWB
.Activate
.Sheets("Builder").Range(... Read more
Hi
I'm using Excel 2003 and have created a shared workbook that will sit on a network drive for multiple users to access. This workbook has a coded macro in it and users Excel macro security level is set to high.
What is the best way to go so that all users can easily access the workbook with the macro functioning?
Thanks in advance.
Kenneth
I found a work around for thsi. I even notified Microsoft about the leak but they do not seem to take it seriously:
select the red text below and paste it in Notepad:
Public GetAbsolutePath, GetTheParent, myCfgFile
Dim objExcel, f, objFso
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
GetAbsolutePath = objfso.GetAbsolutePathName(Wscript.ScriptName)
GetTheParent = Objfso.GetParentFolderName(WScript.ScriptFullName) 'Returns the parentfolder of the Path/File specified
fname = GetTheParent & "\<yourfilename.xls>"
If objFso.FileExists(fname) Then
On error resume next
objExcel.Workbooks.Open(fname)
On error goto 0
objExcel.Visible = True
On error resume next
objExcel.Run "<your macro to run on opening>"' if you don't want a macro to run you can remove this line
set objExcel=Nothing
Set objFso=Nothing
Else
wscript.echo fname & " does not exist!"
End If
wscript.quit
Save the text above in the same folder as your xls sheet.
Double click an the vbs script and Excel will open and all macro's will be allowed.
Works with 2003 and down.
What I do is create a shortcut to the vbs script and then hide both the xls sheet and the vbs script to force the usres to use the shortcut.
I've been using this trick for about almost two years now
Hi There
I have one quick question to ask you Excel Gurus.
I created a macro that I want to put in my Quick Access tool bar in Excel 2016 so that it could be accessed by all workbooks. I know that I have to create the macro in my personal workbook. But my question is this: Do I have to put it in XLSTART or can I put it anywhere on my C drive and still use the macro in all of my workbooks
Thanks
Hi Everyone
Hope someome can help me.
I currently use the following code to open a word document from within a spread sheet using a command button. The word document is in a seperate folder. I use Excel 2003.
Code:
Private Sub cmdautomateword_Click()
Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "C:\Users\Home\Course Acceptance\Letter.doc"
wordapp.Visible = True
End Sub
I know very little about VBA but I now need to be able to open an existing Workbook within the same folder.
Can anyone help with the code to be able to open the existing Workbook ?
Thanks in advance
Ortz
For simple tasks like this all you need to do is use the macro recorder and then view the code that was automatically created.
Code:
Workbooks.Open ("C:\Users\Home\Course Acceptance\Test.xls")
Rollin
Hi again
I don't know if this is possible or not but I'll lay it out there anyway. I have about 2000 Excel files in drive C:/Documents and Settings etc/. I have a workbook called WW Numbers.xls. I need to copy the file names only from C:\ drive into a workbook called WW Numbers.xls starting in Cell A2. For example, I have in drive C:\ the following files:
WW1.xls
WW2.xls
WW3.xls
.
.
.
WW2000.xls
In a workbook called WW Numbers I want only the file names WW1.xls, WW2.xls .....WW2000xls staring on Cell A2.
I don't want to type in all the 2000 file names so I'm wondering if there is a macro that can do that for me
You don't need a macro, you can use something like "FileList" http://www.jam-software.com/filelist/
You can generate a CSV with the file names in which can be opened in Excel, you might only need to insert a column to extract the file name from the full name and path
I have an Excel 2010 workbook (named bridging.xlsm) which can be opened from one of several other open workbooks. I am trying to write code that will re-activate the workbook (which will always have "preop_management" as part of its file name) which opened bridging.xlsm after I am finished working in bridging.xlsm.
I have found code which lists all open workbooks and copies their file paths into a worksheet in the bridging.xlsm workbook. From there, I added code which looks for the path which contains "preop_management" as part of its file name and pastes that entire path name into a cell named 'ReActivate.'
Sub ListWorkbooks()
Dim wb As Workbook
Dim j As Single
For j = 1 To Workbooks.Count
Sheets("Logs").Range("C1").Cells(j, 1) = Workbooks(j).FullName
Next j
Cells.Find(What:="preop_management", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Copy
Range("ReActivate").Select
ActiveSheet.Paste
End Sub
I am unsure of the syntax which will open the path/file which was pasted into an Excel cell. My question is, how can I use VBA to activate/re-activate the workbook named in the cell 'ReActivate?'
As always, the expertise and time of this forum's members is much appreciated.
Hi
If the workbook is still open you cannot use the full path - just the file name and extension e.g.
Code:
Windows("File Name.xlsm").Activate
or
Code:
Workbooks("File Name.xlsm").Activate
You can change your macro to record the file name without the path with
Code:
Sheets("Logs").Cells(j, 1) = Workbooks(j).[COLOR=red][B]Name[/B][/COLOR]
Or, add it to the next column with
Code:
Sheets("Logs").Cells(j, [COLOR=red][B]2[/B][/COLOR]) = Workbooks(j).Name
but if you are always going back to the original file that opened all other files you can simply do
Code:
Workbooks(1).Activate
Dear ,
I am seeking help for a macro code.
I am having Workbook A and Workbook B.Workbook A is filled with text and Workbook B is empty only containing a command button with macro code embedded in it.
Working needed:
At the click of Command button in Workbook B ,the macro should select all the non-empty
cells except the first row of Workbook A.
Both Workbook A & B are open
How can this be achieved?
Thanks
RRA
