Excel 2013 :: Macro That Can Reference Worksheet It Has Created
Jul 4, 2014
I am completely new to Macro's and VB and the macro below has been generated using the "Record Macro" function in Excel 2013 with a couple of very minor modifications based on some research I have done (hyperlink & input box). My ultimate goal is to make a copy of my "TEMPLATE", which is hidden and the copy could have a variety of names, then create an entry in my "SUMMARY" table that references cells on the newly created sheet. The new entry on the SUMMARY page should be entered in the next available row ... at the moment I need to make sure I have my cursor in the right place before I run the macro. I also want the first cell in the new "SUMMARY" row to create a hyperlink to the newly created worksheet.
The macro does what I need it to do, as long as I name the new sheet "Test", what I would like is for the Macro to recognise the name of the new worksheet and create links to that name. The rows and columns in each new sheet will remain the same, hence the R##C## part will always work.
The "SUMMARY" and "TEMPLATE" worksheet names will not (ever) change.
View 4 Replies
ADVERTISEMENT
Aug 2, 2014
I've written a little VBScript that generates several hundred Excel 2013 worksheets. In order to protect the users from themselves I protected certain cells. Everything works as expected, except when I discovered that the password I used to protect the worksheet doesn't work to unprotect it. If it matters, I'm not trying to unprotect programmatically, just opening Excel and going that route.
Here's a snippet of my code:
objWS.protect Password="abc123"
View 2 Replies
View Related
Jul 24, 2013
The spreadsheet works in Excel 2000 when you open it in any newer version it is corrupted. Which is fine so we are rewriting it in Excel 2010/2013.
We have data in columns A:E which comes from the refreshable query. Data in F:I are different formulas based on the data from the query. The number of rows we get will vary from query to query. The headers never change just the data. How can we make it so that when no matter the data length the formulas will always autofill up or down depending on the data length?
View 9 Replies
View Related
Aug 10, 2014
Here is a very simple workbook/sheet with some simple date arithmetic.
I keep getting the warning that there's a circular reference in C2.
Either I need to suppress the warning so vba loops can run or PREFERABLY I'd get rid of the "circular reference".
Run with Excel 2013.
View 14 Replies
View Related
Jun 28, 2013
I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.
My department processes payments, both for internal clients and external clients. My Payments table looks like:
Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity
Additionally, I have a table for Mail Opening, which looks like:
Date | Employee | Operation | Quantity
My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.
View 3 Replies
View Related
May 3, 2012
I am creating an invoice form in Excel 2003. I like to know if it is possible to increase the value of cel A1 by one everytime a new sheet is created.
For example i created my first worksheet and gave an invoice number 001. Now I create a new worksheet by right clicking on the tab and choose copy/move. I like the second work sheet change the invoice number to 002.
View 8 Replies
View Related
May 28, 2014
I encountered a strange anomaly where a worksheet "freezes" when Application.DisplayFormulaBar = False. I can only replicate this in Excel 2013. Excel 2010, for example, works perfectly well, and as expected.
[Code].....
To replicate the anomaly (Excel 2013 only):
1. In a new workbook, insert an ActiveX command button on "Sheet1", no code required.
2. Run the following code
[Code] .....
3. Click on the command button.
4. Now click on any cell and try to enter a value.
Is your screen "frozen"? If so, go to another sheet, return to Sheet1 and try again. Does it work?
Here's an alternative code for MyTest() that causes no problems. Can spot the difference? Is there a reasonable explanation?
[Code] ....
Even more curious, call the following MyTest3 on Workbook_Open() and the workbook behaves. Run MyTest3 again and the screen starts freezing(!)
[Code] .....
View 3 Replies
View Related
Jan 20, 2014
In Excel 2013 x64 (EN; CZ locale) I have this funny bug. I work on a large vba project and sometimes when I open it, every cell in every workbook that had default formatting now has this numberformat (shown as "Accounting")
"_-* #,##0.00 [$Kč-405]_-;-* #,##0.00 [$Kč-405]_-;_-* ""-""?? [$Kč-405]_-;_-@_-"
its seems that this formatting is assigned to styles --> Normal and it just messes up everything (pivots, slicers...) and cannot(!) be undone.
I have made some routines to check for this error on workbook.open and workbook.close and I also have file versioning. I check for the error regularly on every worksheet change, but it never comes up, nor does it whenever I close the workbook, so Im having hard time detecting when it occurs.
Sometimes when I try to open the workbook its just all messed up. When I go trough the versions, couple of them back still has the error which means it was already saved with it.
All I could figure out so far is that it sometimes happens when I try to copy some cell and paste it elsewhere (but later it works fine)
I'm 99.9% sure that my code is not causing it by accident or purpose. Now I just found the problem on different workbook that might have been opened at the same time. If you're interested, have a look here [URL] ......
View 2 Replies
View Related
Feb 19, 2014
I have made a Rota of sorts using Excel 2013 Desktop Edition for my charities volunteers (and stored it as a shared file via office 365 server that they can download and edit) and this rota is populated by our volunteers manually. Each day our controller needs to check the file to see who is on duty at that time.
How the Rota is populated.(the bit i managed to do myself)
The volunteer (Person A in this example) would open the excel file and go to the month they wish to choose a shift for (ref worksheet: FEB in this example). They would then pick a shift that suits them and click on the cell (ref: F32) that shows a vehicle available, then from the drop down list they select their name and then save and close the sheet.
Rather than our volunteer controller going through the sheet for the current month (ref worksheet: Sheets JAN to DEC) I would like them to use the first sheet in the workbook (ref worksheet: DC Info Page) to get an instant view of which volunteer is currently on shift.
My current problem
I don’t know how to make the excel file do the following
Search sheets JAN to DEC (ref cells: C4:I58 on each sheet) inclusive for the cell that contains today's (current actual) dateCopy the 8 (eight) cells below the cell that contains today's datePaste the copied cells in to the relative cells (ref: C8 to C15) in sheet one (ref worksheet: DC Info page)
I would also like this to be done automatically so the controller does not have to click on anything after they open the file. But if it needs a button to process the request, one could be added to the worksheet (ref: DC Info Page)
View 1 Replies
View Related
Aug 11, 2014
I have an Excel file that's updated monthly. when it does save its around 16mb and can take up to 12 hours to save, and sometimes just doesn't.
I have tried saving as binary, I have made sure exact size of area to be saved is required, I have tried save with no calculations.
Basically the only reason I need to save it is so that another analysis spreadsheet can pull data from it. The file is heavily formatted, charts, vlookup tables etc, none of which is needed when analysis spreadsheet links to it.
View 1 Replies
View Related
Aug 12, 2014
I merged about 15 adresslists from media contacts to one excel list. Each list had a name i.e. music, health, theater, etc. and the same logic in colums. I added a few columns and have 1 large list now.
As some journalists write about music & health & theater, architecture, etc. they are listed up to 10 times in the new list now. But the "genres" from the original list i.e. music, health, theater, etc. are in different columns. Some of the lines have empty fields (i.e. no address or mail)
All I want to do is have one line with all the information of all 10 lines in it, merged, dupes removed:
company - firstname - lastname - Adress - Mail, etc. : genre: music - health - theater:
example.xlsx
I atteched an example of the full list and the result i want
View 9 Replies
View Related
Jan 27, 2014
I'm using some workbook-scoped named formulas to define some dynamic ranges which will be referred to by numerous worksheets. The named ranges are defined like:
NAME: gTable_costDetailsEquipment
REFERS TO: =globalParameters!$B$5:INDEX(globalParameters!$B$5:$C$1048576,1+countAdjacentNonBlank
(globalParameters!$B$5,"down"),1+countAdjacentNonBlank(globalParameters!$B$5,"right"))
From either of the tabs "Reports" or "DOR_Template" the user can press the large "+" icon to add a report (which copies the template or the last report to a new sheet).
When this Sheet copy takes place, excel is repeating my named formulas - this time it's making LOCAL versions scoped to the newly made worksheet.
I've used this copy sheet trick before and have never had excel create new, locally scoped, named formula for each workbook level name.
I also just recently started using excel 2013, is this a problem with the new version? I've just never seen this problem, usually workbook-level names are NOT duplicated on sheet copy.
View 2 Replies
View Related
May 17, 2014
I'm using Excel 2013 and I'm getting an issue in vba I can't figure out. (This is something I've done several dozen time before) But everytime I try to copy a sheet in a workbook,
Sheets("Sheet1").Copy After:=WB.Sheets(WB.Sheets.Count)
I recently copied in this sheet from another workbook, and deleted all of it's formula names, but I can't copy any other sheets now either.
The Run time Error 1004: Copy method of Worksheet Class failed pops up. What is weird is that I put in a msgbox and
MsgBox (WB.Sheets.Count)
returns a "1" though the sheet has about a dozen sheets within it. I've saved the workbook and even saved it as another name.
The sheet I imported has a sheet number of 77 while the previous last sheet was 23, could this be a cause?
View 2 Replies
View Related
Jun 23, 2014
I am using the code below in Excel 2013.
Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "131125" Then
[Code]....
This works great except that it pastes formulas. I would like to paste values only. I've tried
" PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.
View 3 Replies
View Related
Jun 23, 2014
I've got a spreadsheet that pulls data from a SQL server.
I'm looking to have a button on the first sheet that extracts the data from SQL and inserts it into sheet 2 of spreadsheet. The data connection from SqL is working fine but when I click on the button sheet 2 is blank.
View 1 Replies
View Related
May 14, 2014
I am using excel 2013. I have the table look like the above, in the diagram 1, i want to have the macro such that it can be formatted to the 2nd diagram. Can this be done using macro ? [URL]
View 2 Replies
View Related
Mar 18, 2014
A coworker just ran something by me that i'm quite frankly baffled by. After running a macro, the undo function becomes disabled up to the point of when the macro was ran. I've done a quick search through the forums and can't seem to find this question so i thought i'd ask.
Is there an option to toggle somewhere or 3rd party addon that keeps Undo enabled after running a macro? On a similar topic for my own curiosity, what's the reason that it disables?
View 3 Replies
View Related
Apr 3, 2014
Excel 2013
I am trying to define a font colour within a macro When I record the macro, I choose a colour from the font colour-picker in the Home section of the ribbon. The colour I choose is one of the grey shades Everything seems to work ok, and the macro saves with the colour defined as a long number, e.g.
.Bold = False
.Italic = False
.ThemeColor = xlThemeColordark2
TintAndShade = -0.499984740745262
(Note this is the colour number returned by Excel when I chose a grey font) But when I run the macro, instead of a grey font I get a sort of pale apricot
Do I need to re-index something?
Is there a list anywhere of these long numbers and the colours that they represent?
View 2 Replies
View Related
May 30, 2013
I currently have this beast of a code running in Excel 2013:
HTML Code:
Sub OBTAIN_RAW_DATA_MACRO()
Dim NextCol As Long
'Copy data
Workbooks.Open Filename:= _
[Code] ........
However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.
Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?
View 4 Replies
View Related
Dec 3, 2013
Upgraded to Excel 2013. Do i no longer have the ability to customize the look of my macro buttons i add to the toolbar?
View 2 Replies
View Related
Dec 6, 2013
Had 2003 now 2013. Trying to get the macro to select different printers for different doccuments. 2013 macro recorder does not record any printer info or path, all printers show the same wording just application print...
How can I get the recorder to acurrately record and diferentiate printers?
View 2 Replies
View Related
Apr 8, 2014
So there's a macro "RoundedRectangle4_click" that's assigned to a box on a worksheet. It's a simple macro, I assume. it just takes the user to the 'Main Sheet' tab. I know the name b/c I rightclicked on the box to get the Macro Assigned.
I click on Macros (F8), it's not listed there, but"RoundedRectangle5_click" is, so I click on it, edit it to match the one I'm searching. Nope, nothing. CREATE button is highlighted.
I've gone to VBA (AltF11) to try to find it among the 25+ worksheets, forms & modules - haven't found it yet.
View 4 Replies
View Related
Feb 2, 2013
I need to place a date and time in a cell 14/9/2013 6:00 PM
Tried several formats under custom format without success.
Also need addition to following macro to enable it to start on opening of workbook
Code:
Sub Macro1()
Do
PauseTime = 1 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
[Code]...
View 8 Replies
View Related
Apr 27, 2014
The following code dynamically creates a number of textboxes within a frame with a label right next to it. There are two classes.
[Code] ......
The two classes look like this:
[Code] .....
This works just fine to create the dynamic series of textboxes and corrosponding labels. I am having trouble with the reference between them. for example (within the class module):
[Code] .....
As shown, the reference to the corresponding lable from within the class doesn't work. How to reference the correct corresponding label.
View 13 Replies
View Related
May 9, 2014
I'm using Excel 2013 on Windows 7.
I have an Excel file which has a macro that sucks in data from a number of separate files (two Excel, a varying number of csv). The macro grabs all the individual files and loads them into one of three worksheets with some formatting, sorting, and structuring on the way. All good so far.
I then need to take two of the worksheets and copy them to a new Workbook which I can send out to some users. So I use the Workbooks.Add method, and use the Selection.Copy on just the data (UsedRange.Rows/Columns.Count to ensure it is only the data) and Selection.PasteSpecial with the xlPasteColumnWidths, xlPasteValues, and xlPasteFormats options to copy the data over. None of the data is filtered. Just straight data.
I would expect the newly added workbook to be smaller than the one with the macros because the one with the macros has two extra sheets that I don't transfer, and the sheets I do transfer are identical.
And that's where it gets weird. The file with the macros and more data is 18.7Mb, but the new one with only two sheets is 24.8Mb. One is .xlsm and the other is .xlsx (because it has no macros).
I've tried opening the new workbook and saving as .xlsm (no material difference to the size) and as .xls (it got even bigger). I've opened the new workbook, gone to the end of the data and deleted all the blank rows and columns, but no effect (because the rows are already empty).
Why would the file with less in it be so much bigger (or bigger at all)?
View 2 Replies
View Related
Jun 16, 2014
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have excel 2013.
View 4 Replies
View Related
Dec 6, 2013
I've done this loads of times before but doing it this morning seems to not be working at all.
Previously I'd be in Sheet 1, I put the equals sign, I click into Sheet 2 in the cell I want, hit enter, and hey presto the external cell is referenced in Sheet 1.
However, what I'm trying to do here is not reference individual sheets within an excel document, but reference a totally separate Excel document. Can I do this or not?
If so how? Because when I select the second worksheet after I've entered the = sign, it just takes me to the second sheet - it doesn't seem to 'remember' that I'm in the middle of a formula.
View 4 Replies
View Related
Nov 3, 2009
I am writing a macro that will use information from the worksheet in use and the one prior to it. The problem is I do not know how to reference the worksheet that will always come before the worksheet in use. To fill you in, I have a monthly budget worksheet that tracks all my monthly financials. What I like to do is compare this months balances (cash, equity, bonds) and see the amount of loss/gain and percentage from the month before.
Here is what i currently have implemented.
This macro was written in the month of October. For this function the macro I wrote will take Octobers cash balance and subtract it from Septembers cash balance. This will give me the gain or loss. Now the problem is if I were to run this macro in November it will still reference September not October. I always want the macro to use the month before (the 1st tab to the left) not a specific tab. I wrote this macro using relative references so I do not understand why it will not use the tab before. Can someone please shed some light on this for me?
View 10 Replies
View Related
Aug 26, 2010
Here is my situation:
I created one worksheet called: Assumptions. In this worksheet I have 6 different columns representing 6 different cases.
I have completed the first case in a different worksheet called: WL. I would like to copy this case/worksheet WL and be able to change all the reference cells used in case#1: WL to case#2: FA. So all the cells used in my Assumptions worksheet were in the B columns for case 1: WL, for case 2: FA they will all be in the C column, same row, different column.
Any quick way to do the changes?
My case #1 worksheet as over 10 000 formulas, I really don't want to change each and every formula.
I was thinking there was a way to highlight ALL the reference cells used in my Assumption worksheet, so I would have had to drag from B to C cells, only 50 cells. But I can't find it.
View 12 Replies
View Related
Dec 18, 2013
I am trying to create a budgeting spreadsheet that contains 26 worksheets that represent 2 week expense cycles. The worksheet will tally all the expenses in its 2 week cycle, compare them to a target budget, and calculate the remaining budget. I would like to roll the remaining budget over to the next 2 week budget cycle by having a cell refer to the remaining budget in the previous worksheet.
I am trying to accomplish this using the following VBA script. I get an error (Run-time error 1004: Application-defined or object-defined error) on the 3rd to last line of the code. I think the issue is with how I am constructing the wsReference variable. I have seen other codes do something similar (VBA - How to refer to a different worksheet cell) but I am not having any luck.
Sub Budget_Rollover()
Dim i As Long
' Loop through all the worksheets, starting with the second sheet
For i = 2 To Worksheets.Count
Dim wsReference As String
Dim wsName As String
' Define the name of the previous expense cycle worksheet
[code]......
View 2 Replies
View Related
