When you enable macros the sheets 2, 3, 4 ect are visible but if you disable macros, you only see sheet 1 and and you can place a message on the sheet saying this will only work with macros enabled.
I have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
I recently took a macros my friend did and adjusted it so it would work for my problem.
It works fine but there is one thing i don't understand about it.
The initial problem was that i needed a macros that could copy a range and paste it 1 cell space to the left and then delete the last cell of that range, so it would look something like this:
FROM THIS:
HIJKLM2NIVEL 3 21 TO THIS:
HIJKLM2NIVEL3 21
so this is the macros i am using which works perfectly:
I've been trying to create an excel macro to calculate shortest route path using floyd algorithm..
Problem is, i can't figure out why this macro won't work with decimal numbers.. I'm pretty sure there's a VERY simple explanation for this.. Probably has something to do with excel's formating
See the attachment and press Run button, it won't produce the correct result, but if if you multiply all the numbers in "input" sheet by 100.
I need to lock the macros in spreadsheets at work.
If I go to Developer>Macros I can edit any of the macros.
So to protect them I went to Visual Basic>Tools>VBA Project
What I don't understand is that there is already a password there.
How it is possible that I can edit any of the macros under Developer>Macros when there is a password which I haven't entered under Visual Basic>Tools>VBA Project
My dad wrote me a VB programme to save me time entering repetitive data into an excel template for my business. At first it didnt work untilll i reduced security to low. Sice then I fried my computer and have had to reformat it with a diffirent version of windows, vista service pack 3. Now even on low security it doesnt work.
The little button in the worksheet to make the macro happen isnt visible or doesnt exist.
I wondered if I could pick your brains and hopefully get some answers to my problem. I have recently been using the Personal.xls worksheet to make all my Macros available to all open workbooks so that I don't have to cut and paste Macros each time I wanted to use them.
Now I know you sometimes have to make certain adjustments to Macros in order to make them work globally. I wondered if I could find out how to amend the codes below to make them work properly. Thanks.
The first code Macro below deletes all worksheets in a workbook except for the currently active sheet. However it no longer works.
1. I've written a few macros using MSExcel 2003 VBA that I've tested successfully on Office 2007, but with 2003 workbooks only. Since the filename extensions for Excel 2007 workbooks are different from those for 2003, what changes are required in my vba code to ensure that it will work with 2007 workbooks too?
2. Will changes to vba code be necessary even for changes in the operating system (e.g. Vista / Windows 7)? I have worked with my vba codes on Win XP only.
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
I've created a spreadsheet at work that has two summary tabs which contain hyperlinks to around 30 separate sheet tabs.
On each sheet tab there is a list of unique values in column A (and other information relating to each value in columns B to D which are repeated for more than one unique value). In column E, users enter a test script name against each unique value they wish to 'reserve', and the macro picks out the unique test script names and via the COUNTIF formula counts the frequency of each test script name for each of the different values in column B.
My problem is that the macro seems to work fine if the workbook is not shared, but errors if the workbook is saved as shared. The error is 'Run time error 1004 - Unable to select the MergeCells property of the Range class'.
Here is the macro code:
Sub Get_Policies_Per_Script(updCol As Long, ShtName As String) Dim rowctr As Long Dim tgtrow As Long
Const ppsformula As String = "=COUNTIFS($A$3:$A$65000,I$24,$E$3:$E$65000,$G"
If updCol = 5 Then 'test name column has been modified
I am currently using a macro to copy a sheet from a closed workbook in to my current workbook. However this copying is based on the sheet name. At present when I run the following code
This situation is as follows. I have a worksheet with 12 sheets. One sheet per month which is used to show monthly cost expenses for certain categories. I have a budget category, a paid this month, a paid previous months and paid year to date. I suppose I can take the paid previous months out if I can get this to work but I need to calculate one of those cells (prev months or ytd) to calculate and sum the previous months and then tack on the current month.
Previously I have had these categories and used =SUM(Month:Month!A1) Like --- > =SUM(Jan:Nov!C10)
This works and all I have to do is add on the current month to paid prev and get a ytd but this also means that I have to rewrite the formuals concurent with the noted month. Is there a way that I can make one sheet as a base and then just make 11 copies with a formula that will sum all previous worksheets to the start at a certain cell reference. So say sum all A1's on all worksheets before the one that is currently in use say for example Dec.
If I need to rewrite them that is find but I figured there should be an easier way to do this so I would not have to rewrite each month individually and then copy the formuals through that sheet.
how do I use a macro across a spreadsheet?I can record a macro OK,but when I try to use it again on the same spreadsheet all I get is the macro calculating using the same data as when I recorded it.I have several groups of data across one spreadsheet and I want to use the same macro over the entire sheet.How do I do this?
I am using Outlining on a sheet called "Today".The sheet is manually protected but now the outlining does not work as it says sheet protected.Is there a code i can use so that i can still have the Outlining working in a protected sheet.
I have below macro for Sheet2 and i have created a button on Sheet1.now whenever i press this button the macro will run and save Sheet2 as PDF file.
The problem is when i hide Sheet2 the macro doesnt work and it gives an error " Invalid procedure call or argument.
how to make this macro run even when the sheets are hidden ?
Sub PDF_Table() 'Sheet2.PageSetup.PrintArea = "$a$1:$x$140" '***** '*** can remove the above line if sheet areas are already set and will not be altered With Sheet2.PageSetup .CenterHorizontally = True .CenterVertically = True .Orientation = xlPortrait .Zoom = 60
Private Sub Worksheet_Change(ByVal Target As Range) ' Code goes in the Worksheet specific module Dim rng As Range ' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3") Set rng = Target.Parent.Range("C13:D25") ' Only look at single cell changes If Target.Count > 1 Then Exit Sub ' Only look at that range If Intersect(Target, rng) Is Nothing Then Exit Sub
[code]....
Which does not allow entry to range C13:D25 if D12 is empty. Works great!But I need it to work across ranges E13:F25 if F12 is empty, G13:H25 if G12 is empty etc.
I tried copy pasting the same macro over and over again but renaming it to e.g. Worksheet_Change2 causes the Macro to stop working completely.What am I doing wrong?
I am dealing with a workbook that will hold an undetermined amount of worksheets. Each of these worksheets will have have a series of numbers that are summed to a total within one cell. Lets say cell A10. For every spreadsheet, no matter what, this number will be stored in cell A10.
My first sheet will then be a master listing of all the other sheets in the workbook. In column A I will have the names of each sheet and in column B I want excel to list the value of A10 for the corresponding sheet listed in column A
So normally it would look like this on the master sheet
A | B Sheet 2 | =Sheet2!A10 Sheet 3 | =Sheet3!A10 Sheet 4 | =Sheet4!A10
Where the formula would give me the value of A10 in the respective sheet.
What I would like to do is, within column B, I want to replace the sheet reference with the cell that contains the name of the sheet. So it would then be a formula similar to the below setup(Although this does not work because I have tried it)
A | B Sheet 2 | =A1!A10 Sheet 3 | =A2!A10 Sheet 4 | =A3!A10
The hope is that excel would substitute in Sheet 2 for A1 and there by give me the sheet reference that I need. This way I can continuosly add sheets to the workbook and as long as I have the correct Sheet names in column A, all I need to do is drag the formula down column B and I will pull all of the necessary information without having to retype the formula each time.
It's actually a two-parter. I have two buttons over each of five sections - CUT and PASTE. So each salesperson (section) has its own buttons.
I need to be able to click a CUT button and have the macro either COPY or CUT the row of data and then DELETE the row. Then I need to be able to choose another worksheet and click PASTE over a section within that worksheet and have the macro INSERT a blank line and then PASTE the row of data.
The attached worksheet shows four buttons for each salesperson. The other two buttons work great. I've tried several ways with no luck; and I believe this is the only way I can work off various worksheets.
i've a macro which will read the data from worksheets and copies that data and paste the copied data into new workbook sheets.
it is copying the data into new work sheet. whats my problem is my original application contains formatted data like having cloros and some of the columns having big font size etc and some of sheets contains merged cells.
i'm totally having 8 worksheets with different layouts and the macro copying the data into 8 new worksheets but the look and feel is not similar to the original application. But i want as it is.
I have two macros, both written in "Module 1". I would like these both to run on the sheet named "profile" whenever any one or more cells in the sheet named "data" change in value. I have looked at the relevant FAQ's but unfortunately just don't have the experience with vba.
Problem- I need a combobox on sheet1 to reference a column in sheet2 and return only a distinct list.
[Code].....
If I put my sheet2 column info on sheet1, and then reference it appropriately as built in the code above, it code works great. So I know it's close. I cannot change it to work with 2 sheets though. I've tried to "set wsSheet2 = wbBook.Worksheets("Sheet2")" and call it 'where I think it's appropriate', which is the problem. I don't know what's appropriate.
changing the steps in the code above, where in my case the combobox is on sheet1, and the range/data referenced is on sheet2?
This is what I've changed the original VBA to, and when I run the debug, it picks up the last "with...combobox1" statement as the error (error 1004, app/object defined error):
i want to put a number of graphs on to a work sheet to pringt them out to make it look good. How can i using a macro fix the size of that graph and the location of that graph on the worksheet