In the example attachment on the weekly tab, you will see I have daily totals and a sum for weekly. On the summary tab I have the columns going down with a link to the weekly totals.
I have over 5 years of data in this format and don't want to link each cell in one sheet to another. If I drag and drop, obviously the weekly doesn't come down correctly.
Is there another way as I don't want to have to do this for over 250 weekly totals (and counting) manually.
I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.
I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.
Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.
I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.
I have a spreadsheet where I often have to exchange pairs of cells. I currently do this by dragging Cell A to a blank portion of the sheet, dragging Cell B to Cell A's former location, then dragging Cell A to Cell B's former location. I was just thinking that I might be able to use VBA to allow me to drag Cell A to Cell B and have them "trade places".
I don't even know what events are involved in the dragging process so I don't know where to start.
I have a form with a main list box and several other list boxes. I drag and drop records from the main list box to the others. When I drop a record onto one of the list boxes, a corresponding worksheet is updated with the same record. This works just fine.
The problem I have is:I close and re-open the form after having added some dataDrag/Drop a record to the list boxThe worksheet won't find the first empty rowExample: If the worksheet already contains 4 rows of data, I have to drag/drop 5 times before the fifth row will be updatedI would like to drop the data the first time and have the first blank row updated
Notes:
1.) I have tried variations of "xlUp", "xlDown", and SpecialCells(xlCellTypeLastCell) in the "Worksheets("Monday")... line of code; all to no avail.
2.) The cells contain formatting (borders)
Here is the relevant code:
Code:
'Copy items from the list box to the worksheet For intI = 1 To ListBox2.ListCount For intJ = 1 To ListBox2.ColumnCount If IsEmpty(Worksheets("Monday").Cells(intI + 1, intJ)) Then Worksheets("Monday").Cells(intI + 1, intJ).Value = ListBox2.List(intI - 1, intJ - 1) End If Next intJ Next intI
When I drag a text file from my desktop into an existing Excel worksheet it automatically creates a new workbook. I'd like to know how to modify this such that I can drop the file into my existing worksheet (and not have a new workbook create) and then run some VBA macros on it.
I've finally gotten the code down to drag an item from one list box and drop it into another listbox, but only for one item at a time. The list boxes are on a user form and I am using Excel 2002.
how to select multiple items in ListBox1, drag them to and drop them into ListBox2?
I am looking to if there is anyway to move an image box using a mouse drag and drop action. I have put together a little example of what I am trying to work with and was wondering if anyone on here had any tricks to make this work.
I realise the drag and drop method only applies to the textbox and listbox but have seen some old code that can simulate the drag and drop by creating a border of some sort to illustrate where the image box is going to be placed and final just setting the properties of that image box to where the border is placed.
In the example type in a size in the width and height textboxs (I used 3000,3000) and then what I want to do from there is move the white box using the mouse within the blue box.
I am wondering if it is possible to drag & drop, via left-click, the data in a cell but WITHOUT affecting it's reference -- i.e., like "Copy here as values only".
Private Sub Workbook_Activate() Application.CutCopyMode = False Application.OnKey "^c", "" Application.CellDragAndDrop = False End Sub Private Sub Workbook_Deactivate()
[Code] .......
OR This one:
Option Explicit Private Sub Workbook_Activate() With Application .CutCopyMode = False .CellDragAndDrop = False
[Code] ........
I noltice that one uses "Option Explicit" and one doesn't, one seems to be longer than the other, but when I tested both, they both seem to do the same thing. I did notice that neither one of them prevents a "paste" into the worksheet when something is copied from an outside source, but that is not a concern right now!!!
What do you think? I think it can be done but do not want to waste time going about it the wrong way. Please give me any ideas on how you would approach this problem.
Some information to start with. The modal dialog that pops up can be dealt with.
This one.
I can determine if I want to insert down or up. I am not concerned with shifting left or right at this point.
I can also determine the original address and destination address of the drag and drop operation by using the change event.
The ranges being dragged will be limited to a single cell.
I've created a macro to disable cut-copy-paste and drag-and-drop (below), but when the user exits Excel, the drag-and-drop option remains turned off.
The user has to click on the Office button, go to Excel Options, Advanced tab, and re-enable the fill handle manually. How can I have my macro do this automatically when the workbook is closed?
'*** In a standard module *** Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean) 'Activate/deactivate cut, copy, paste and pastespecial menu items Call EnableMenuItem(21, Allow) ' cut Call EnableMenuItem(19, Allow) ' copy Call EnableMenuItem(22, Allow) ' paste Call EnableMenuItem(755, Allow) ' pastespecial
'Activate/deactivate drag and drop ability Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys With Application Select Case Allow Case Is = False ....................................
I am researching the possibility of using Excel 2003 WITHOUT a Cad or Drawing package to simulate a drawing. (Consultants inquiries are WELCOME). The idea is to prompt the user (Sales Rep or Customer) for a few key dimensions and then plot out a closed curve based on the coordinantes entered. This shape, which would need to be accurately scaled, would then be sectioned and used as a canvas for dragging and dropping various icons.
Why use Excel instead of CAD?
Other than "everyone" having it, the placement of the "icons" relative to the perimeter of the curve will be used to estimate material costs and generate a quotation like a configurator application.
Key hurdles seem to be the drawing of a curve with cells as pixels and using drag & drop with custom icons - possible as an Add-In?
I making a template for multiple users to import data into for analysis. I want them to be able to delete and drag-and-drop data to get rid of any bogus values. However i need forumlas that reference those to NOT follow the values as there as moved. As a simple example say you have two columns, column A is a set of random numbers, column B is the number from coumn A times 2. If take the bottom half of column A and drag it up one cell, overwriting a cell, now the cell in column B next to the overwritten cell has a #REF err and ever cell below that now references a Cell in column A from the previous row. I want a formula that references a static address and will not follow the cell if it is moved.
If I use named ranges rather than specific cells references it works for the above example, however for my actual form it does not. Even though the ranges and formulas stay the same I start getting incorrect values. If there is no other way to this I will try to figure why the named ranges do not work.
is there any way where instead of selecting a cell one by one and run a macro, i can just drag the cell with the macro across other cells like how a formula runs?
I need to calculate ratings 1-5 of questions on client surveys received.
I have recurring sets of data, A1:E1, a number 1-5 is to be entered into one of the cells within this range. This same pattern is repeated 25 times....G1:L1, etc. At the end I need to total all the 1s, 2s, 3s, etc. Then I need to calculate an overall pct for each question based on the totals for that question, divided by the maximum score (5) multiplied by the total surveys received. The problem I am having is that my formula below is counting empty cells, and not giving me a proper pct.
I have a column that says either TRUE or FALSE this is dependent on what number i input into another cell, so if i input 8, TRUE shows on every 8th row,
I want the cells that say TRUE to also have a sequential number next to it in the column beside it.
Formula i have: =MOD(ROW(S2)-1,nth_No.)=0
So for example, every 3rd row is TRUE, i am wanting a sequential number beside it:
I am trying to link from one spreadsheet to another and drag the cells down to copy the forumula, however I want to drag vertically on Sheet 1, and Copy the values horizontally from sheet 2.
For example, in sheet 1 I link cell A1 to equal cell A1 in Sheet 2. If I drag down the formula in sheet 1 A1:A10 then it will copy the values in cells A1:A10 in sheet 2.
Now what I want it to do is for me to drag the formula in cell A1 down to A10 in sheet 1, but for this to return the values of A1:J1.
I would like to copy a large selection of hyperlniks down a column of 200 plus cells and would the hyperlink to increment as I drag the cells downwards.
Is there a simple way to copy and paste down a large selection of cells, so that I do not have to edit each hyperlink individually
I'm trying to write a formula where I can add the values found in non-sequential cells in a row based on if the value in the previous cell in the same row meets certain criteria.
In my worksheet i have several rows that are duplicates and i need to give each row a unique number. For example the first duplicated row needs to be 1 the second 2 etc.
I am making a sheet that has many colums of data that I want to sumup colum wise. I am doing is using this macro .. ============================================= Sub Add_Totals()
For Each NumRange In Columns("C").SpecialCells(xlConstants, xlNumbers).Areas
I have a spreadsheet with temperature data, all in one column (D), that was recorded every 10 minutes for several months. In two adjacent columns (E & F), I would like to record the daily max and min temperatures. I can manually do this with MAX and MIN formulas in column E and F respectively, MAX(D1:D144). But when I copy this formula to the next row, I get MAX(D2:D145) when what I really want is MAX(D145:D288)....and so on...
