VBA Macro To Move Shape Around Spreadsheet

Dec 16, 2009

I am trying to write a set of code, part of which involves moving a shape around an excel spreadsheet in a square shape within a certain range. It is based on a random number generator. The shape moves a number of cells to the left/right/up/down depending on the random number (d) multiplied by 16. If the shape reaches a row or column border point, it will change direction but continue moving in the new direction until it has gone as far as was determined by the random number (*16). I have some code which works fine... to a point! The shape starts out, reaches a right hand border, changes direction to go up, reaches another border, changes again and goes left, but then when it comes to change dorection the 3rd time and go down, it fails. The direction of the shape is determined by a word in cell A1 - left/right/up/down:


Sub MoveRed()
Application. ScreenUpdating = True
Dim d As Integer
Dim i As Integer

d = Range("AZ1").Value

With ActiveSheet.Shapes("RedDot")
For i = 1 To 16 * d

Select Case Range("A1").Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

ADVERTISEMENT

Macro To Move Around Large Spreadsheet....

Jan 6, 2010

Need the code to put into a command button which jumps from the cell selected, X number of cells to the right. Also will need the code to jump back again when clicking on a different command button.

View 5 Replies View Related

How To Move A Pasted Shape

Mar 2, 2013

I Have several images on Sheet 1, I can copy the needed Images to sheet 2 as needed.

Once the image is pasted to sheet2 how do I assign it to a shape variable so that I can position it as desired.

I do not want to have to use Sheet2.(Range("B2").activate or similar

I'm looking to use

Shp.top = 25
Shp.left =15

Code:

Sub Macro1()
Dim Shp As Shape
Sheet1.Shapes("Picture 1").Copy
Sheet2.Paste

'To set the shape variable to the shape on sheet2

Set Shp = "WHAT GOES HERE"
Shp.Top = 25
Shp.Left = 15
End Sub

View 4 Replies View Related

Move Shape Across Screen

Dec 24, 2007

I'm trying to write some VB to draw a line shape and then move it across the screen. I have managed to draw the line and have used the following code to move it:

Set linetest = ActiveSheet.Shapes(1)
For iCount=1 To 100
linetest.Left = linetest.Left + 1
linetest.Top = linetest.Top + 1
Next

i) I can only actually see the line move if I run the code using the debugger and step through the loop. If I just run the code then I only see the end position of the line (note, this is not an issue with the calculation being too fast for Excel to display the line. I tried the code with a delay loop in it and it made no difference). I can get it to work by calculating the active sheet for each iteration of the loop, but this means I have no control over the speed of the movement; incidentally it moves very slowly).

ii) This seems a poor way to move the line. Is there anyway to move the start and end node of the line rather than using the left, top, width and height properties (i.e. can I get the old coordinates of the points and set them to different numbers)?

View 5 Replies View Related

Move A Shape In A Straight Line Between 2 Cells

Sep 24, 2008

if I wanted to slowly move a shape (small circle) in a straight line between cells R20 & W7.

The straightest line possible is by drawing an arrow line between the 2 cells.

Is there a way to then use this arrow as a guide for moving the shape?

In other words, would it make sense for the porgram to draw a transparent line between the cells, and use that line as a "path" along which to move the shape?

I just don't know if this is worth the trouble.

View 9 Replies View Related

VBA To Move Shape From Current Cell Position To Cell 1 Row Up

Nov 11, 2008

I require a macro to enable a selected shape to be moved from current cell location to relative position but 1 row up. eg topleft address = A4 and shift shape to topleft address = A3

View 5 Replies View Related

Move Spreadsheet To End Of Workbook

Jun 4, 2009

I m moving a spreadsheet to the end of a workbook, however the amount of spreadsheets in the workbook increase each day.

Two files will be involved in this macro. 1) Headset Out Time and 2) Headset Out Time "Month". Where month is the current month.

Command Button 2 located on the file Headset Out Time will execute the macro. The macro will open Headset Out Time "Month" then move the first spreadsheet (different name on a daily basis), from Headset Out Time to the end of the workbook Headset Out Time "Month". Then it will reset the spreadsheet (this part i can handle)

View 3 Replies View Related

Highlight A Row And Move To The Bottom Of Spreadsheet

Nov 12, 2012

I'm working my way through a list of companies on an excel spreadsheet and making some notes.

After i've spoken to them its a simple keep them on the sheet in the current position or highlight the row red and move to the bottom or when they become a customer highlight green and move to the bottom.

I have a coloumn called customer (J) is it possible if i put a Y in that cell to make the row green and move to the bottom and also if i put a N in the column make the row green and move to bottom.

View 8 Replies View Related

Move Specific Data To Another Spreadsheet In The Same Workbook

Jul 5, 2007

What I want to know: Is is possible to have information from one spreadsheet move specific data to another spreadsheet in the same workbook?

for example: I have hundreds of listings of tool measurements.

Date Tool type Measurements


What I want to do is have all the different types of tools and the information from the above line seperated into other spreadsheets. I do not want to sort this information and manually move it I want a formula that will move each applicable tool into that sub sheet. I have tried to use if statements but find that it creates subsheets with the correct tool types but has the spaces in between the parts. how do I eliminate the spaces?

View 9 Replies View Related

Change Shape Text Without Selecting Shape

Mar 4, 2009

when i run the below code i get an error 438 'object doesnt support this property or method'

View 2 Replies View Related

Macro To Enter Shape Or Picture?

Sep 30, 2012

I am making a spreadsheet file for our football offcials group to review rules.

I tried to record a macro to copy a shape and put it into the spreadsheet, but the shape does not copy.

How do I get the shape to copy into the spreadsheet?

What I want is for them to "think" what they would do and then click the macro and it will show them where the ball would be after the foul/fouls.

View 2 Replies View Related

Assigning Macro To Shape In 2007

Dec 5, 2008

I posted similar yesterday. No one helped...sniff sniff (where's the crying smily)

Now verified to be occur on other machines running various versions of Office 2007...

In Excel 2007, write this simple macro and assign it to a shape:

View 6 Replies View Related

How To Add Formatting To Shape Creation Macro

Aug 7, 2013

I have to create an identical button on 20 sheets.

I have a shape creation macro but it creates in the default format.

Can I either change the default format so they are created in the way I want or code to add formatting? I need no shadow, can colour (pref with gradient fill), bevelled edges and text.

Sounds like the first option will be easiest if it works!

View 1 Replies View Related

Macro To Select Shape To Print Off

Jan 2, 2014

I created a shape and named it Cheque and created a macro to print the shape as follows:

ActiveSheet.Shapes("Cheque").PrintOut

But the macro is not printing!

View 4 Replies View Related

Using Shape To Call Macro (Filepath Error)

Apr 9, 2014

I am changing command buttons over to shapes for aesthetic purposes. I went to assign a macro to call useform4 using a rectangle with the following code:

[Code] .....

It throws me some kind of file path error which makes no sense. Do I have to dim the rectangle2 for active sheet or something?

View 11 Replies View Related

Run Macro After Leaving Shape Editing Mode?

May 14, 2014

I've got a macro function which calculates the area of a polygon freeform using the 'shoelace'-methode. I would like to excute this function after i leave the shape editing mode.

For example: I have a square, freeform shape. By right-mouseclick I can edit the shape nodes. If i drag one of the nodes of the square to another position and click outside of the shape, the new shape is updated. Right, on that moment i would like to trigger my macro to recalculate the area.

Is there an event in excel to aim for, like the Worksheet.change-event or something similar?

View 2 Replies View Related

Automatically Assigning Macro To Any Shape Created

Aug 14, 2014

I've got a macro that I can rund when I click on a shape. It's called "Shape_Click".

Basically, I want this macro to be automatically assigned to any shape I create, as I create it.

I know the line will be something like: [Code] ........

View 6 Replies View Related

Automatically Assigning Macro To Created Shape?

Aug 14, 2014

I have a macro that runs when I click on a shape. This macro is called "Shape_Click"

I can apply that to a shape by selecting it and manually running this macro:

[Code] .....

How do I get the subroutine to run as soon as the new shape has been selected, rather than haviong to manually run it.

Something like "Sub Worksheet_ShapeAdded" or something like that? I can't work it out.

View 2 Replies View Related

Determine Shape Clicked For Running Macro

Nov 19, 2008

I have map shape in my excel sheet. each country is a shapes on its own. If i Select one of the country i want to display a pop up Box. For examle if i select Americas I need it to display another box with some data and when i deselect it it should hide the box. Tried some code over here but does not work.

Sub HideShowRectangle()
With ActiveSheet.Shapes("Data1")
If ActiveSheet.Shapes("Americas").Select = "True" Then
.Visible = True
Else
.Visible = False
End If
End With
End Sub

View 5 Replies View Related

Excel 2002 :: Activate Macro By Clicking A Shape

Feb 3, 2013

I found in an old file that i triggered the macro by clicking a button! I would post a pic but i don't know how. How do I activate a macro by clicking a shape on the excel 2002 page?

View 7 Replies View Related

Locate A Shape Within A Shape

Jan 24, 2014

On the attached spreadsheet there is two irregular shapes. "Area1" & "Area2". I need to be able to determine which area the "ball" shape is located in. If the ball is in Area1 then "Multiply 8" gets the ball and "Multiply 9" moves to the blue cell "AH39". If the ball is in "AreaB" Multiply 9 gets the ball and "Multiply 8" runs to cell "S37"

I should be able to do the moving of the shapes using all the samples, its determining which area the ball is in is the problem.

In the real spreadsheet i will probably have around 10 different Areas.

Move Two Objects - Select Case.xlsm‎

View 5 Replies View Related

Macro Request - If Macro Is Unable To Perform One Of Its Jobs Then Move Onto Next Line

Oct 3, 2012

I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.

However, if the user is not online, the query is unable to refresh and the macro just hangs.

Is there a code which will enable me to say " if unable to refresh then move on to the next line"?

here's the code below.

Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub

View 2 Replies View Related

Macro Fixes Macro: Copies Tabs From The Spreadsheet

Jun 26, 2007

I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.

View 2 Replies View Related

Macro To Move Between Worksheets

Apr 4, 2014

I have a work book with 6 worksheets, sheet2 is called Maersk, sheet3 Mariana, and so on ....

I need a macro so when I type Mariana in sheet1 g20 and hit enter it go's to Mariana sheet .....

View 5 Replies View Related

Macro To Move To New Row On A Form

Mar 26, 2014

I have a macro that is auto opening a form to be completed, but it currently opens on the first row of data that has been inputted.

How do I get the macro to move to a new row immediately to stop people from over-typing the first row of data?

View 5 Replies View Related

Macro To Move To A Column

May 14, 2009

I need a macro that I can assign to a button so that it will jump the screen to a particular cell. I want it to select the cell in a chosen column in the same row as the cursor already was. So, if I'm in M10 and I hit the AK button, it'll jump to AK10. If I'm in F54 and I hit the AK button it'll jump to AK54.

View 2 Replies View Related

Macro To Move Text

Oct 14, 2009

I am trying to create a macro to do the following (Microsoft Excel 2002 SP3):

1 - Select the text from the active/selected cell (i.e. B1)
2 - Cut the text from B1
3 - Move to the cell directly above the active/selected cell (i.e. A1)
4 - Add a space to the end of whatever text is in A1
5 - Paste the contents that were cut from B1 after the text and space in A1
6 - Delete the row where the text was cut from (i.e. entire row B)

I have never written code for macro's before, only done recording, but just can't seem to get this sequence to work .

View 8 Replies View Related

Macro To Cut And Move Columns

Feb 22, 2010

I've used the macro recorder to copy a cut and paste routine, but it won't play back correctly. Here's the macro I recorded:

View 7 Replies View Related

Cut And Move Rows - Macro

Nov 2, 2011

I need to cut 2 rows when i put "V" in E column, and then to paste them below row that have in column A, "OLD".

Ex:
I have many rows with data like this:

.......A..........B.........C.......D......E
1...subject
2...notes
3...subject............................."V"
4...notes
5...subject
6...notes
...

25..."old" row...............................

I need to cut the row with "V" in column E and the below row, and paste them below row with "old" in A column, like this:

.......A..........B.........C.......D......E
1...subject
2...notes

3...subject
4...notes
...

23..."old" row...............................
24...subject............................."V"
25...notes

View 9 Replies View Related

Using Macro To Move A Cell

Dec 19, 2013

I've been using the record macro function to accomplish a lot of my formatting tasks for my work reports.

I'm running into a snag that I'm not sure how to fix. Essentially I want the program to recognized the term "Grand Total" and the cell to the right of it. Then I want those two cells to be moved to the far left.

The # of rows will continuously change but he column shouldn't change.

I've tried naming the cell selection to have it moved but I can't seem to make it work.

When I use the record macro function and do a CTRL F search for "grand total", excel doesn't seem to recognize that I want that cell and the cell to the right to be the ones to move. Instead, whatever row of cells I create the macro with, it wants to duplicate the move with the same exact cell.

View 2 Replies View Related

Move Function In Macro

May 14, 2008

I have a piece of my macro that creates a chart on the second sheet in my workbook.
However, it tends to appear way off to the right, and then I have to readjust it every time to be where I'm working (columns A-D approx.)

Can I improve the move to specify it to place the graph in a certain
All I'm currently doing is:

ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"

View 9 Replies View Related

Macro Move Sheets

Jul 23, 2008

I have about 80 sheets in a workbook, and I would like to put each sheet into it's own workbook, and have that new workbook called by the value in Column A.

View 9 Replies View Related

Move/Rename Macro

Aug 6, 2008

I have a workbook with 52 worksheets.
6 are hidden, 46 are visible.
I am trying to move a worksheet to the end of the workbook and rename as the next sheet.
It works the first time I run it (it renames the worksheet to Client51, the new last worksheet) but after that it gives me a 1004 error 'cannot rename a sheet to the same name as another sheet...."
Here is the macro I am using:

Sub rename_client()
Dim NewName As String
ActiveSheet.Move Before:=Sheets(Sheets.Count)
NewName = "Client" & Sheets.Count - 2
ActiveSheet.Name = NewName
End Sub

View 9 Replies View Related

Macro To Move Row To Another Worksheet

Sep 16, 2008

I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.

View 9 Replies View Related

Macro To Move Columns And

Mar 13, 2009

I started a project where I want to export data from Access (only 2 columns but # of rows vary every day). When the data that is copied to the first tab is a week old and is a Tuesday I want to copy and paste these values in the next tab.

View 9 Replies View Related

Find And Move VBA Macro

Jun 1, 2006

find and move VBA macro - funky when WhatToFind in more than one row

Making a program that will find a value input by the user, and move the row of the cell that value is found in to the top of the list.

Works perfectly if there is only one row containing the search string. If more than one row contains the search string, it moves things to odd places.

here's the

Sub FindAndMoveToTop()
Dim FirstCell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
'Window prompt allowing user to define WhatToFind
WhatToFind = Application.InputBox("What are you looking for?", "Search", , 100, 100, , , 2)
'If WhatToFind is a value and not blank, move on
If WhatToFind <> "" And Not WhatToFind = False Then
'Start with first worksheet
Worksheets("Sheet1").Activate
'Start at first cell
Range("A1").Select
'Find the first cell containing WhatToFind (specified by user)
Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
'If FirstCell exists, move on
If Not FirstCell Is Nothing Then
'Ok, First Cell is set
FirstCell.Activate
'Keep going...

View 4 Replies View Related

Macro To Find And Move A Column

Aug 11, 2009

I regularly receive a file with a column called ‘Project Key’ which for some unknown reason can be located anywhere in the worksheet i.e. in any column from A: to BZ and beyond.

What I would like to have is a macro that finds the column ‘Project Key’ and moves it from it’s current position and moves it directly after a column call ‘XXX’ and delete the blank column where it orginally was. There would be no need to check if the columns exceed Excels maximum as my file never gets that big

View 3 Replies View Related

Move (Cut / Paste) From One Sheet To Other With Macro

Jul 24, 2014

Macro, which can cut/Paste from sheet "Pastin" to Sheet "Robot".

The input in sheet Pastin is veritical and the the result is in a row horiontsal.

There can be no references between the names for the columns or rows, only the cell to move.

I would like it to be so when I run the macro it example move it to sheet robot from row A3, and if I do again it make the next one from Row A4.

What I want to move can be seen in below.

Cell on the left is sheet "Pastein" and cells on the right is sheet "Robot"

Special is that one move have to be taken from on cell comma separated input, so it goes to sheet "Robot" for each text/number, between the comma.

I have attached a test sheet to work with also.

[Code] ...

Testmove.xls‎

View 6 Replies View Related

Macro To Move Data To Another Format

Feb 25, 2014

I'm looking for a macro to select data from a larger data set, and then put it in another worksheet. Basically, I want to select any cell in column A that contains "SO5" and move those to the column A of a separate worksheet. Then, for each cell that contains "SO5" would like to move to the right 5 columns and down 3 rows and select from that cell all the way to the end of the table. There may or may not be data in all of the table cells, so the selection should be based off of the column headers which will always have values. I would like to take that data and put it with the corresponding "SO5" in the new sheet.

Here's a sample:

MacroSample.xlsm

This is a simplified version, but I'm trying to get the data from sheet1 into the format in sheet2. Keep in mind that the number of columns and rows may change so it needs to be as dynamic as possible.

View 1 Replies View Related

Macro To Move Rows And Organize

Mar 4, 2009

I have been trying to do this for 3 days using "Record Macro", but something always seems to be messed up. The main problem is that the worksheets are protected and the macro asks for the password.

Here is a calendar sheet that I've been working on. It contains the calendar on sheet 1(not important) and a client roster on sheet 2. The client roster gets filled quickly, and obviously gets unorganized. What I'd like to do is create a macro so that when a date is entered into column AA (when a client is discharged), that entire row is moved onto sheet 3 (Discharged). At the same time, I'd like all of the names in column A alphabetized.

I even tried to create an "Organize" button so that the users could just click it and alphabetize. The problem I ran into with that was that the users are using Office 2000, and the names get alphabetized but all of their information no longer matches up.

View 3 Replies View Related

Move Cell Locatin In A Macro

Mar 30, 2009

Below is my the macro that I recorded. I want to copy and paste the same information to a new location everytime. The macro tells it to go to cell IV1, end left, then I want it to go right one cell regardles of the cell location. Here it tells it to got to ("K1"). It will do this everytime and I do not want it to go to K1, just move right one time and then paste the information.

View 3 Replies View Related

ADVERTISEMENT