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
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.)
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
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.
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.
Hi,
I am working on an Excel 2002 report. I need to be able to create a copy of the workbook, strip it down a bit (for uploading purposes), and let the user save it as he/she pleases.
Is it possible to make an active copy of a workbook without saving it?
Excel VBA help is not being very "helpful" on this matter.
Thank you,
ABentsionov
Hello, welcome to the board!
It is always best if you work from a saved file. This is just generally a good idea. Is there a reason you do not want to save the workbook? You need to save to do what you want. There is a way, but it is more convuluted than just saving your file.
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.
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
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,
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
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
Hi,
I'm having trouble with the following code. If I run it as "Sub test()", it works. If I try to run it as is, it hangs up at Sheets("2011 Yearly Summary").Select
HTML:
Private Sub Workbook_open()
Range("A1").Select
Workbooks.Open Filename:="C:\Excel\PT\pt money.xlsm"
Sheets("2011 Yearly Summary").Select
Range("A1").Select
ActiveCell.Range("A1:U36").Select
Selection.Copy
Windows("Treasury Summary.xlsm").Activate
ActiveCell.Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("pt money.xlsm").Activate
ActiveCell.Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
What I trying to do is import from workbook "pt money"- sheet (2011 Yearly Summary) to workbook "Treasury Summary" each time I open it.
Also what do I add if the workbook that I'm coping from (Workbooks.Open Filename:="C:\Excel\PT\pt money.xlsm")
is already open?
Thankis
Mike
I am trying to write a macro that needs to copy a range of cells from one workbook to another. I am using Excel 2010. The problem I have is that the copied cells link back to the original workbook. How can I preven this? Here is the problem lines:
Windows("Source.xlsx").Activate
Sheets("Analysis").Range("E2:AE3").Copy
Windows("Destination.xlsx").Activate
With Sheets("Analysis")
.Range("E2:AE3").PasteSpecial
End With
Any ideas much appreciated.
Tom
Hi Tom
It depends on what you want the result to look like. If all you want is to copy the data use this in your "Copy" line
Range("E2:AE3").PasteSpecial Paste:=xlPasteValues
But if you want to retain the formulas but with any references to other sheets repointed to the Destination sheets you can add this line
Cells.Replace What:="[source.xlsx]", Replacement:=""
This will not work if a sheet referred to in Source does not exist in Destination.
hi
hope someone can help,
in workbook1, Sheet1, data is entered in the following cells, E4,E13,D1221 and H12:H17
what i would like is, when a command(save) button is pressed the data from these cells is copied and pasted into the next available row in a new workbook or a different sheet in workbook1
if the data is copied to a different worksheet will the data be saved even if the origional data is cleared in sheet1
am not sure if i actually need to use 2 workbooks,
Hi,
Is there a way to move/copy a Userform from one workbook to another. I'm making a new workbook and want to use a userform that I already formatted that's in a different workbook. I don't want to have to redo the formatting if I don't have to.
Mike
I comprehend that you can use Worksheets.Copy or Worksheets.Move to move a worksheet to a new workbook. But my requirements are more in depth than that, and my VBA is minimal. I have reporting on about 25 different programs, and the program can either be a "A" program or can be a "B" program. I am using this to open the workbook, import a query using ADO, then save the file. However, before saving the file, I want to copy the 2nd worksheet into a new workbook. The requirement set is if it is an "A" program move to a workbook called "A_Programs" if it is a "B" program, then move to a workbook called "B_Programs". Here is the code I currently have:
Code:
Dim NewWb As Workbook
Dim WB As Workbook
Dim CurrentWb As Workbook
Dim WsTData As Worksheet
Dim NewWs As Worksheet
Dim Ocell As Range
Dim GreaterThanExists As Boolean
Dim Type
Type = "B"
Set WB = Workbooks.Open(Filename:="D:\Excel\Mainframe\Reports\FileName.xls")
Call GetAccessDataDAO
Set WsTData = Worksheets("Sheet2")
Set CurrentWb = ThisWorkbook
For Each Ocell In WsTData.Range(WsTData.Cells(2, 45), WsTData.Cells(Rows.Count, 45).End(xlUp))
If Ocell.Value >= Date Then
Set NewWs = Sheets("Sheet1")
Sheets("Sheet 2").Select
NewWs.Rows(1).EntireRow.Cells.Value = WsTData.Rows(1).EntireRow.Cells.Value
... Read more
Hi,
Does anyone know how do you copy just the formula from one workbook to different workbook without it having the link to the 1st work book?
i.e.
When I copy the formula ='Balloons N1 Sun'!$A$46 from one workbook to another it comes out like ='[TEMPLATE Balloon Lot details Nights 1 Week XX.xls]Balloons N1 Sun'!$A$46 when all I want is ='Balloons N1 Sun'!$A$46 .
If anyone can help thanks in advance.
Thanks,
David.
You need to copy just the text of the formula, not the cell containing the formula.
Select the cell containing the formula. Press F2 for "Edit" mode. With Shift held down, press Home to highlight all the text. Press CTRL+C (shortcut for copy). Press ESC. Switch to the other workbook (CTRL+Tab). Select the cell where you want the formula. Press F2 again. Press CTRL+V (shortcut for paste). Press ENTER.
HTH
Excel 2000
Windows 98
I have a workbook for my business from 2009 that I want to copy and make work for 2010. Workbook has 33 pages so I'd like an entirely new workbook for 2010 set up just like my 2009 workbook. I have attempted to copy and paste the desktop icon of this workbook to a new area of the desktop and that worked very well except, when opening the new workbook and erasing all 2009 entries, none of the formulas are working and all will need to be reprogrammed. Is there a better way to accomplish what I need to do. The copied w/b appears to have entered the "Twilight Zone".
Hi
I have a Workbook(User) that contains data for the issues that have been resolved. Every week I need to pull out a copy (certain columns) of the data that meet a certain criteria and paste it into another workbook(Master) for manipulation.
For Instance I need to copy alll the rows that have "yes" as Feedback and paste it into another workbook.
I want Macro to be stored in one file only(Master). I need to report data on weekly basis and want a new file to be created each week.
I have attached the prototype of files that I am using.
Regards
Manjot
Welcome to the board.
"Every week I need to pull out a copy (certain columns) of the data that meet a certain criteria and paste it into another workbook(Master) for manipulation. For Instance I need to copy alll the rows that have "yes" as Feedback and paste it into another workbook."
While it's possible that "manipulation" includes other actions you're not telling us about, including the Master book in the process at all appears to be complicating things unnecessarily.
With your User.xls active, you could just run this:
Sub test()
Sheets("Sheet1").Copy
x = Range("A" & Rows.Count).End(xlUp).Row
Range("N1") = "Check"
Range("N2:N" & x).FormulaR1C1 = "=IF(RC[-5]=""Yes"",""X"",0)"
Range("N:N").SpecialCells(xlCellTypeFormulas, 1).EntireRow.Delete
Range("N:N").Delete
End Sub
to get a new book with only the rows from User.xls where Feedback = "Yes". You could then save this new book with the date in the name.
"I want Macro to be stored in one file only(Master)."
Storing it in your personal macro workbook would be better. See "Record a macro" in Help.
Hi
I have a starnge problem with Excel 2000.
I am trying to copy a merged cell (8 cols x 1 row) to another merged cell the same size on another sheet using a macro. Normally this works fine.
Others started to want access to the workbook so I turned on the shared workbook facility.
As soon as this is done, when I copy the merged cell to the other merged cell it splits the receiving merged cell into it's original eight columns. So all the other relative references in the macro don't work anymore.
Turn off sharing and it works again fine.
What am I missing?
I've experienced the same issue - with no fix found. I had a couple macros that allowed users to enter new data on one worksheet, then did a cut/paste of the new data to a merged cell on the report form. Sharing the workbook caused the macro to fail, unsharing fixed it. Don't know the cause, but it appears to be universal. I just instructed my users to copy the master form onto their desktops, and I would notify them of updates/changes to the master file on the shared drive (the non-shared file!) when they were made, so they could update their desktop version.
Good luck!
In Excel, I wonder how can I copy the formula and the format from worksheet 1 to worksheet 2 - 12? All these woorksheet are in the same workbook. Thank you for the reply.
Should just be able to copy the entire sheet, select all the sheets you want to copy it to, and then just click paste. If there is no extra formula's to avoid, nothing to it.
v
I have two workbooks I'm trying to combine in a VBA macro. The first excel spreadsheet is just simply a sheet of 30 columns of data and 5k rows. It adds a new row every day.
The second sheet will be the model with the macro. I would like to write a script that will use a sort of vlookup to the data sheet to pull in specific parts of the datatable instead of searching through the whole big sheet.
For example, see attached. The Model tab lets you input a date or dates in the C column and then it would have to somehow search another WORKBOOK, not within this one as the example shows (for simplicity). Any ideas would be much appreciated.
Hi,
I am using Office 2013. I have a workbook which has to be shared. I want to protect it in a way that none of the options can be changes (like I have enabled 'track changes'. 'highlisght changes from users' etc). I do not want that someone turn off the 'track changes' option or as a matter of fact any other option.
They, however, should be able to edit the workbook. Is it possible?
I tried searching but could only see an option to protect workbook structure but noes not protect options.
-regards
I have an Excel (2010) template from which I need to create separate workbooks for our range of ocean-going vessels.
Requirement 1
Upon loading the template, I need to prompt the user to enter the name of the vessel.
Requirement 2
The name must be transferred to a particular cell, probably A1, in proper case.
Requirement 3
I then wish to save the workbook using the vessel’s name, in capitals, followed by a space, a hyphen, another space and then the rest of the filename – based on the name of the template.
Example 1: name of template Record of voyage.
Example 2: name of workbook OCEAN TRAVELLER - Record of voyage.
Any thoughts?
Hello guys.
Please guide me through building my own tool in Excel.
I need to have workbook WK 1 with macro to modify another workbook WK2 which already have macros in it.
That is my plan : WK1 should have several command buttons first is to find person by ID from WK 2 , copy entire row with persons details to WK1 list 1, another button same but copy entire row to WK1 list 2, than when both lists created, I must have third command button which will : for each person in List1 WK1 will change value in cell 1 in WK2 from none to 1 and cell 10 enters =today date, for each person in List2 WK1 will delete value in first cell of the row in WK2.
I am keep loosing the track of what would be most simple way to do those steps, WK 2 file name is changing every day with date in beginning of the name of file,
My O/S is Windows Vista and I am running M/S Office 2000 Professional. In the past when using Windows XP I have been able to change which workbook opens when I open Excel. Now with Vista, I have designated 2 different workbooks to open when Excel begins however I cannot delete them form the Excel XLS folder. When I delete them, the screen shows that they are gone however when I strat Excel afterwards, they are back! How can I dlete them and install a new workbook. The standard process shown in the Excel help files is not working. Thank You.
Closing duplicate...........replies here: http://forums.techguy.org/business-applications/672809-revise-excel-startup.html
I created an Excel workbook to monitor my investments. One of the work sheets contains a long column of currency values from an annuity investment with each row showing daily values.
Presently, I manually copy the daily value of that investment and paste it into worksheet # 2 that contains all of my investments. Worksheet # 2 daily refreshes, updates and totals all investments.
My question is this: is there a formula that would automatically copy and paste the daily annuity values from worksheet # 1 into worksheet # 2 without having to manually copy and paste? If so, could you please guide me through the steps to accomplish this?
Many thanks for your prompt attention and cooperation.
Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Quad CPU Q8200 @ 2.33GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 4
RAM: 4028 Mb
Graphics Card: NVIDIA GeForce 9800 GT, 1024 Mb
Hard Drives: C: Total - 476837 MB, Free - 414208 MB; D: Total - 114439 MB, Free - 21161 MB;
Motherboard: Intel Corporation, DG43GT
Antivirus: avast! Antivirus, Updated and Enabled
<edited by moderator (etaf) to reduce font size - not required default forum font size is adequate >
Hi there
Sometimes I click on the X to close the workbook and I accidentally press NO when the computer prompts me to save the workbook since it usually comes up in a yellow box that does not really capture my attention. This usually happens when I'm in a hurry or late at night when I'm half asleep. Is there some code that makes a message box pop up with a message that says in big bold letters "YOU FORGOT TO SAVE YOUR FILE" when I click on the X to close the workbook? If so, this will save me a lot of headackes.
Mario
I am no guru, however I did find this in the hep wizard of MS Excel. Hope it is of some help to you.
Save workbooks automatically as you work
On the Tools menu, click AutoSave.
If the AutoSave command is not on the Tools menu, you must install the AutoSave add-in.
How?
Select the Automatic save every check box.
In the Minutes box, enter how often you want Microsoft Excel to save workbooks.
Select any other options you want.
Tech Support Guy,
I have a question regarding linking two Excel (I am using Office 2003) workbooks and varying worksheets.
Here's what I want to do, using a macro, create a link to a cell in workbook A/worksheet X to workbook B/worksheet X (where X is variable).
Now creating a link is easy, except I want the "link" itself to appear in workbook A.
I tried the following:
Workbooks("Workbook A.xls").Worksheets(Worksheet A).Cells(6, 4).Value _
= Workbooks("Workbook B.xls").Worksheets(Worksheet A).Cells(57, 3).Value
However, Excel places the value instead of the link in the cell. I want both, meaning, you see the value in the cell, but when you highlight the cell, you see the link to the 2nd workbook.
I have also tried the the following:
Workbooks("Workbook A.xls").Worksheets(Worksheet A).Cells(6, 4).Value _
= "='[Workbook B.xls] Worksheet A '!R57C3"
This gets me what I need except as I mentioned, the worksheets vary and I have not been able to figure out how to do use a variable worksheet name in this example.
I appreciate your help in solving this problem
Take care,
RJ
I write a few macros for other users, and right now I just request that they rename the workbooks each month (or however often they run it) to the same names and then offer a reminder at kick-off. Something like:
Code:
If MsgBox("Are the workbooks saved as 'Workbook_1.xlsx' and 'Workbook_2.xlsx', and are they both open?", vbYesNo) = vbNo Then
End
End If
Windows("Workbook_1.xlsx").Activate
Range("a3").Select
Etc, Etc
Otherwise, I don't know how to make changes or run actions based on workbooks whose names might change. But is there a better way? I've had some people - those least familiar with automation - forget to rename, ignore the message, and then call with problems. Perhaps there's a way, instead, that asks them to name as variables whatever the sheets are called, though this might require some hand-holding, such as a list of what workbooks are open? I don't know. I'm open to suggestions - just putting it out there.
Thanks!
Windows XP
Excel 2007
I have a shared workbook that when some people open it the formatting does not show the same. Example I have hidden columns, but they don't see them hidden
Howdy, kinda new here. Anyway, I've got a doozy I can't seem to figure out. We just installed dual-monitors and now Excel is coming up saying "Not enough resources to display completely." It does this even when Excel is just started, with the blank, new workbook. There doesn't seem to be any screen real-estate issues since there's now double the screenspace. Any thoughts or ideas to try would be greatly appreciated.
-Ted
When I attempted to save an Excel workbook, I couldn't because the file name had changed to a number. What should I do?
Hi, I need an excel Macro that will copy the rows (including the header) and paste into a new Workbook and then attach the workbook to Outlook email to send. It will need to add a message in the body of the email and add the email addresses. I have included a sample workbook to review.
Thank you for you help.
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 need to update one workbook (Master Metrics) with data entered into another workbook (Daily Metrics).
Data is entered in the daily metrics workbook on a daily basis. This data populates the master metrics workbook automatically. The master keeps a running total of the data so that a monthly total can be generated for monthly reporting.
What is the best way to do this?
Thanks in advance for any suggestions
Hello,
I've been working on a certain workbook and everytime a try to save it an error occurs:
Microsoft Excel for Windows has encountered a problem and needs to close. We are sorry for the inconvenience.
AppName: excel.exe AppVer: 9.0.0.2719 ModName: excel.exe
ModVer: 9.0.0.2719 Offset: 002aa2b3
HELP !!!
Thanks
Mark R
Hi, try running a repair from control panel>click on MS office and run change or repair if I remmber correctly.
Read other 1 answersGood 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.
Please forgive me if I am using incorrect terms here. I use Adobe Acrobat/Distiller (Version 5) to convert documents to pdf format. The problem comes when I have a large Excel workbook with 20 or so worksheets (tabs at the bottom of the page). When I try to convert them into pdf (File-Print-Distiller) I get 20 or so files.
First question-Is there a way to convert a multiple worksheet excel file into 1 pdf file? (Without the tedious "insert pages" function I am using right now)
Second question- Is there a way to convert multiple Word documents into 1 pdf file? (Once again without tedious "insert pages" function) Before you ask, no I cannot make all the Word documents into one file.
Third question-If there is no way to do the other two...is there a way to convert multiple adobe pdf into one without "insert pages".
Now since last time I got a bunch of "if it's not broke don't fix it comments" I am posting this statement. "Insert pages" is great and works great to get a couple of pdf files together. Try doing it for over 250 files and see how fun it is.
If the worksheets are all < 1 page "long", then File -- Print -- Entire Workbook worked for me using CutePDF (can be found on Google).
OpenOffice has "Export to PDF" ; it did handle > page-long sheets for me, but baulked at some print settings, so it could depend on how fancy your sheets are (http://www.openoffice.org/).
>> I cannot make all the Word documents into one file.
Are you saying Insert Subdocument doesn't work in your version of Word?
HTH,
Andy
I have a non-shared Excel spreadsheet that users have saved multiple copies of. Some are Excel 2010 and some are Excel 2003. I need to compare and merge them back into one spreadsheet which I will then share. What is the best (and easiest!!) way to do this?
sounds like a manual job.... you can't automatically select the best data over a range of different spreadsheets.
As long as the macros were created with the 2010 version you should be able to view them within the same instance of Excel.
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?
When I try to delete either in Excel 2016 I get a message that tells me to unhide the sheet. The sheet is there in front of me, the "hide/unhide" says nothing is hidden and I am confused. Can this be resolved?
Also, how do sheets get hidden without my doing anything knowingly?
Thanks
Hey -
How do I set different (scaling) print percentages for each worksheet within an excel workbook?
For example: My workbook has 5 worksheets and I would like for 4 of the 5 pages to print at 75% scaling and the 5th page to print at 68% scaling.
Thanks in advance
GDJ
From each worksheet, go to File-Page setup, and set the percentage. You can group worksheets, tho I'm not sure you can set the page setup when they are grouped, but I would try it. Click on worksheet 1 tab, hold shift, then click on worksheet 4 tab. Do the page setup, click on worksheet 5, do page setup. Then they should all be set as you desire them.
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
Can anyone please assit me on how to make an Excel workbook give me a reminder via Outlook.
I want a reminder that will tell me when a specific task has not yet been completed.
we send requests to a custodian when we a looking for a document. on the excel sheet we enter the date we sent the request, but I want a reminder when the received date column has not been updated if I have not received the documents within 3 days time in order to follow up with the person.
can you pease assist me
regards
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?
It seems to me that I used to be able to set up my own default Excel books and worksheets. I would set formats, toolbars, footers, etc. the way I liked them then save the sheet as “sheet1.xlt” and the workbook as “book1.xlt”, and put these in the XLstart folder. That way, when I opened a new workbook, or inserted a new sheet, the sheets are formatted they way I want them. But that doesn’t seem to work now. I’m using Excel 97 and Win XP. Before I was using Excel 97 and Win98. Did I miss a cog somewhere? It’s been a long time since I did this and perhaps I missed a step.
Don
