How can I get this macro to run without removing the hyperlinks from the 1st line of each group?
Sub Transpose()
sn = Sheets("sheet1").UsedRange.Columns(1).Resize(, 4)
For J = 1 To UBound(sn)
sn((J - 1) UBound(sn, 2) + 1, (J - 1) Mod UBound(sn, 2) + 1) = sn(J, 1)
Next
Sheets("sheet2").Cells(1).Resize(UBound(sn) UBound(sn, 2) + 1, UBound(sn, 2)) = sn
End Sub
I have a spreadsheet that I have various formulas and sheets. To simplify:
Sheet 1: Columns A & B have Name and Code Number. I can add additional names and code numbers to these two columns whenever necessary.
Example:
Tom 874
John 385
David 712
Hidden in adjoining columns are formulas to automatically sort them via Code Number. Next to that are the columns showing the sorted information, sorted by number.
John 385
David 712
Tom 874
Additionally, I have a seperate sheet for Tom, John & David. Since I put Tom in first, he's Sheet 2. John is Sheet 3, and David is Sheet 4. I can use hyperlinks so when I click on John, it goes to Sheet 3; David Sheet 4; Tom Sheet 2.
Now, let's say I add Kevin 192 to the next row. The hidden columns are set up so that it'll automatically resort Kevin to the top, with John, David & Tom in the next rows down.
The issue I have with the hyperlink is that it's cell specific, not content specific, as far as I know. I would like the hyperlink to move with John's info so that it would still go to Sheet 3. Same with David and Tom. Unfortunately, after the resort, If I clicked on Kevin's name (which is now at the top of the list), it would go to John's sheet, since the hyperlink is attached to that cell.
How to allow a hyperlink to remain with the content, versus the cell? I would prefer it to be not a macro, but I'll take a macro over nothing!
I have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example
ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10
I have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).
I have made a small macro which very basically makes a copy of sheet 1 (my template)
The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done
I have a big clunky spreadsheet that I thought I'd tidy up by adding a userform to keep the info
There's a combination of textboxes, option buttons, listboxes and a multipage. Everything seems to load correctly, and selecting an item in a listbox correctly fills the textboxes, but when I change the value of the textboxes, only 2 out of 4 holds that value and sends it to the correct cell.
The code that is supposed to send all the values is;
VB:
Private Sub CommandButton1_Click()
'check that a row has been selected
If Range("K3") = "" Then
[Code].....
The culprits are TextBox3 and TextBox4.
I've tried changing the names, I've checked that there are no other textboxes pasted on top, I've even tried adding in a MsgBox line to show the value of the textbox; if the form loads and the box grabs a value of "ABC" from the spreadsheet, and I change it to "DEF", when I hit the button to execute the above code the textbox goes straight back to "ABC".
But it's not an issue with TextBox2 ...
Other parts of the code include a UserForm_Activate block and 5 x ListBox_Click's.
I actually have time entered as a number and decimal.
10.29 means 10 minutes and 29 seconds. How can I get that to only seconds 629?
I have a coumn starting from A2 onwards that holds data with various characters, all that i require is the 1st 9 characters (These are always numbers).
View 6 Replies View RelatedIn a basic formula, I need to take one number from another. But I need to keep the answer to zero, not a negative number.
View 2 Replies View Relatedi am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out
so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage
what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received
i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess
have any of you got any ideas of how i could simplify this and make it look neat and easy
or alternatively is there some other software that may suit this application better?
I have a spreadsheet that has numbers in column A (example = 31, 15, 159) I want to count the number (159) each time it occurs and keep a running count of it in say column B... example..... A1 = 159, A2 = 31, A3 = 159, so B3 would show (2) as the number 159 has appeared 2 times thus far.
View 3 Replies View RelatedI'm using the hidden-row trick to auto-fill cells beneath the hidden rows.
For this to work, every cell that is to be auto-filled in must have a character in every cell above it.
How can I make sure that my unfilled cells always have a character in them? If a user deletes the contents of the cell, I want a single character to stay in the cell (I want a backslash). This way, there will always be either a backslash, or the user-filled data in the cell. It can never be empty.
my spreadsheet I have a column(7.65462E+11) it's a upc code.I can format the column to read as a number (value) but when I save it I does not save the number format.It's really interesting that there are some rows that read exactly the same and those rows keep the number formatting.Going nuts.Also when I try to save a date format(2013-mm-dd) It won't save,I've read that it's system default in U.S,is there a way around that ?
View 2 Replies View RelatedI'm trying to make it so that a lastrow I defined in one module will still be defined in another macro.
My background for this - my macro is too large for one module, therefore, I split it in two. However, the new module uses defined variables (multiple lastrow definitions) from the original module.
So, is there a way to make it so when I originally define a lastrow in one module, other modules will recognize it?
I have the following
[Code] .....
AK5 value will be 2 - 21.
I need it to display
A1D05
or
A1D21
or whatever the number is after A1D, but when AK8 = a value of 1 or 2 or 3...... It comes back as A1D1 or A1D2... How ca I keep the two digits in AK8 to stay and put the value the way I want in AE5 ...
I'm looking for when 'Chart 1' appears in the code, I would like it to link to the current chart I've just created.
View 4 Replies View RelatedWhen I copy/paste an Excel 2003 file containing macros into an email and send and open into another computer, the macros are gone. How can I ensure the macros go with the file?
View 8 Replies View RelatedI have a formula in column A, =AVERAGE(C2:Z2). I have a macro that moves columns C to Z over one column to the right every day. How do I keep the above formula the same as it is now. At present the range also moves one column. I have added $ but it still changes
View 5 Replies View RelatedI have dumped main frame data into excel. It is customer info that is in three rows of data each. I need to know if its possible to group or somehow keep three rows of data together for multiple sorts. The first row is the customer name, the second row is their first line of address, the third row is the city, state and zip. I need to add columns to this dumped data, but I will need to do a number of sorts for different views of the data. But as I've stated every 3 rows go together. I've tried different ways to group (for lack of a better word) the three rows, but nothing I've tried works. I've also done multiple searches, but either I'm not searching on the right word or this can't be done.
View 9 Replies View RelatedI have groups of information I need to stay together, but I'd like to be able to sort them by last name. Today, Tomorrow and Long Term are conditionally formatted (=ISBLANK is green and =ISTEXT is red). As long as the information stays together in a group, that won't be a problem. I have a hidden column A where every cell next to the form refers back to the cell with the last name in it. But when I tried to sort by that, it still seemed to mangle my form. There are no merged cells here. There will eventually be dates, locations (booked) and absent, etc.. filled in. I just want to be able to sort this entire block, without changing anything in it, by last name. There are about 100 of these blocks of info.
LAST NAMEFIRST NAMETodayTomorrowLong TermDATEBOOKEDReason for SubAbsent EmployeeHours is WorkingDayHappyPhone Number:555-5555Cell Number:555-1234Special Skills:Notes:
I want to create a kind of "Housekeeping Macro" that will run based on one single piece of information a number.
I want an inputbox to ask me for a number.
Based on that number I want the macro to do the following -
Go to Ws1 - Membership
Seach down column A until it finds the number put into the Inputbox
When it finds it do the following
Go to Column N and enter todays date as a value.
Go to Column Q and Enter No
Delete the data in R:S and U:V and X:Y and AA:AB and AD:AE
Go to Column J This contains the name of another worksheet i.e. Data1
Go to the worksheet Data1
Search down Column C and find all instances of that the the number entered into the inputbox (there could be several).
Go to each instance and delete the number.
I have a spreadsheet that I want to keep the blank rows that seperate the data for asthetic reasons. Can I sort just the rows with data in them and still keep the blank rows where they are?
View 9 Replies View Related I have two excel sheets in the same workbook. I've linked column B2 in Sheet1 with column A1 in Sheet2. It works great!
The only problem is that when I add a row in Sheet1, it appears in Sheet2, but the rest of the data on that sheet doesn't move alongside the rest of the rows. So if Sheet2 looks like this:
Abby 16
Amy 15
Jenna 14
And I add in another name in Sheet1, lets say Ben, then Sheet2 will look like this.
Abby 16
Amy 15
Ben 14
Jenna
when I want all my data to stick together.
I'm trying to sort by City first, then by Report #, but keeping the highlighted rows together. If I use the custom sort it will through my D2's at the bottom and not keep them with their city, report, and D1. How do I get them to stay together? I have attached my document example.
View 4 Replies View RelatedI have two columns, the left of which consists of merged cells. I got the document in this format, but to work with it, I need to unmerge the cells in the left column - simple enough.
However, when I do so, only the top cell in the group contains any actual data. What I get is this:
Age
18-20
21-25
26-30
And I would like it to look this way:
Age
18-20
Age
21-25
Age
26-30
Is there a simple way to get this result automatically when unmerging the cells? And if not, what is the fastest solution to avoid tons of manual copying?
There is one crucial feature to the 2007 Excel that has been overlooked.
Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.
There is no way to merge two columns of data and to keep all of the data
without one column overwriting the other. If only there were an automatic
way to merge two columns of data and to be able to place a delimited
character in-between, just like the “Convert Text to Columns Wizard”, except
in reverse. Currently, the only way to merge two columns of data is to
manually go row-by-row and cut and paste them together. However, for 500,000
rows of data… this is impossible. Or to use a function to merge two columns,
however this requires that the original two columns remain. This is also
unacceptable. If Microsoft really wants to make Excel more functional, how
can this vast improvement be overlooked?
I've managed to be able to create a macro to make a copy of a worksheet within excel.
However, when that copy is create all the macros are lost. If it is not possible to copy all the macros that are within the "Original Hours" sheet, I at least need a macro that will take people back to the original hours sheet (we have disabled sheet tabs, so need the macros to navigate through the worksheet).
Code used is below
Private Sub Hours_Click()
Sheets("Original hours").Copy After:=Sheets("Original hours")
ActiveSheet.Name = "Update hours"
ActiveSheet.Unprotect "PASSWORD"
With ActiveSheet.UsedRange
.Value = .Value
Sheets("Update hours").Select
End With
End Sub
In spreadsheet 1 I have a bunch of data, no formulas or anything, but data I need. I created spreadsheet 2 to take parts of S1 and have set links to cells in S1 so that when the data there is changed S2 gets updated automatically for me.
My problem is that when I add or delete rows or columns to S1 it's messing up my links in S2, not showing the right data. Issue with my linking that will tie the cells together when rows or columns are added or deleted.
I have a database with ~18000 rows and 29 columns. I would like to filter the data by duplicates in one column, based on total, but keep the remaining data in the row. For example: I have account numbers listed in one column, often duplicates. I can get the total in a pivot table no problem, but need the other data associated with that account. I do not need to see all accounts, only duplicates for accounts listed say greater than 5 times. The data in columns B+ are important.
I have a HUGE vlookup I created to paste in the pivot table data (account numbers and totals) to run a look-up based on those numbers, but I see that running into problems when you run 4k+ look-ups.
I want to see accounts listed only 5+ times, include that total (as in a pivot table) and the remaining 28 columns. I have tried to run this in a pivot table completely, but still too much data to process (plus all the subtotals that I have to keep removing).
I would like my cursor to stay in the same cell when I switch worksheets. If I'm in B40 on worksheet one, and switch to worksheet two I would like the cursor to be in B40.
I'm using very basic excel functions with no scripting at the moment.
I am about to undertake a fairly large spreadsheet development project. One of the key requirements is that the size of the spreadsheet be kept to below 4mb, preferrably below 1mb. This is due to an unavoidable restriction.
Although the spreadsheet will not have large volumes of data, it will contain approximately 30 sheets. There are no graphics embedded in the sheet. I imagine there will be a reasonable amount of VBA and/or forumulas in the sheet. Each sheet will basically be a data-entry form with a number of formulas. No sheet will have more than 100 rows.
Without knowing the full details of the spreadsheet, it is difficult for you to give a definite answer as to the size of the sheet, so I am simply looking for some helpful tips in keeping the size down.
Do formulas take up more space than VBA code? When I format cells, should I format the entire row, or just the range? Do features like sheet and cell protection take up a lot of space?