My OS is Windows 7. My version of Excel is 2007. I have read how to get a macro to move around a page in a relative way, but is there a way to move from page to page in a relative way as well?
View 5 Replies
I need to insert a picture on a Excel 2010 worksheet.
The worksheet contains a variable number of columns, and the column width is also variable.
The picture needs to be aligned such that the right edge of the picture is in line with the right side of the last used column.
This is so that when the worksheet is converted to a PDF document the picture (company logo) appears on the top right hand side of the page.
I can find the cell reference for the last column, and I can move the picture around the page. But I'm not sure how to find the position of the last column on the page?
I've got this far:
'copy picture and resize and position
Sheets("Output File").Select
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Sheets("External data sheet").Select
Cells(1, 10).Select ' I can make this be relative to the last column on the WS
ActiveSheet.Paste
Selection.ShapeRange.Height = 50
Selection.ShapeRange.IncrementLeft 0 'put the picture in a position relative to the cell selected
I am trying to hide a column titled "Test". This column is usually column E, but it is possible it can be moved to column D or F. If I use:
[Code] .........
Then column E is hidden, regardless of whether Test is moved left or right. The reason Test moves is due to the user inserting or deleting a column.
I am using Excel 2007 on Windows 7 Home Premium 64bit.
My Workbook contains 2 Worksheets, both with the same headings in row 1. Sheet2 is initially empty, except for the headings in row 1.
The following is a sample of the data in Sheet1:
[Code] ......
I want to create a Macro to operate as follows:
In the above sample data, rows 2, 4, and 8 are identical, so I want to copy rows 4 and 8 to Sheet2, and delete them from Sheet1.
Similarly:
Rows 3 and 9 are identical, so row 9 should move to Sheet2;
Rows 5 and 11 are identical, so row 11 should move to Sheet2.
Rows 6, 7, 10, and 12 are all unique, so should be unaffected by the Macro.
New records will be added to Sheet1 periodically, so the Macro will be used each time to move any newly discovered duplicates to Sheet2.
The Macro should make no assumption about how the rows are ordered, and should not change their order.
I have uploaded two Workbooks, such that Workbook1 shows the original data, and Workbook2 shows the result I want to obtain.
Attached Files :
Workbook1.xlsm
Workbook2.xlsm
Doing a search function where the cursor is on a row some where (unknown).
I would like to move the cursor to the first column of the spreadsheet.
did in [URL] but my issue is complicated by not knowing what cell I need to move the graph to.
I am using Excel 2010 and I am a realitive novice at writing VBA code.
In My spreadsheet, I have a list of properties and some related cost info. From week to week, the number of properties varies. Some days I will have 6 properties and others I could have 100. Because of this, I need to make all of my references relative to other cells. I am having trouble repositioning the graphs that I have created from the data to be 2 cells below the label I have created for the graph (which is a merged cell covering Columns B:I on a row 3 rows below the last property in the report.
So far, I have:
Dim r1 As Range, r2 As Range, GraphRange As Range
Cells(2, 1).Select
Selection.End(xlDown).Select
[Code]....
I am migrating a bookkeepping spreadsheet from Quattro v12 (c2001) to Excel 2007 and have hit a stone wall over relative 3-D references.
In this workbook each sheet holds data for one month, and this includes some lagging-12-month calculations -- that is, summing a range on a single cell for the preceding 12 sheets (including this one).
Unexpectedly, this formula:
=ISUM('201206:201305'!M6)
gives absolute references, even though I put in no dollar signs after the sheet names. This means it gives the wrong answer when copied to the next sheet to start a new month.
I can find no reference at all to converting the above formula from absolute to relative reference.
In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.
Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.
I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.
I have been using the below macro that has worked a treat, however I need it to copy as value as when it moves over some of the formulas and links go a little funny.
[Code] ......
What amendment I would make so that it pasted special as value?
The data is a copy of CSV file in to a spreadsheet. Below is what the paste looks like:
100100 Books
1234.ab
1345.bf
1456
1567
100100 Books
100200 Magazines
100300 Videos
1234.ab
1345.bf
1456
1567
100300 Videos
All of the numbers between the 100100 Books, 100300 Videos belongs to that category. This repeats hundreds of rows down. I need the column to the left of this range to display which division it under. I parse this in excel so Books, Videos, Magazines will be in their own column. I am not exporting the CSV so have to manipulate it in excel. I need to have the column to the left combine the whole identifier so I can perform a VLOOKUP on it. It should look as follows:
100100.1234.ab
100100.1345.bf
etc.
My approach was to get the column to the left of the range to display the correct division, then have another column combine by using (cell)&"."&(cell).
I am trying to create a form with a text box that when text is entered, it adds to the userform another textbox with that data in it. The focus then goes back to the original textbox so that more may be added indefinitely, all with relative position to the most recently added textbox. Here is what I have so far, works for one time, but not multiple textboxes. The original textbox is "txtAPID"
Private Sub MakeNewTextBox()
Dim newTextBox As TextBox
Dim ControlTop As Long
With LastAirport
ControlTop = 30
End With
Set newTextBox = Me.Controls.Add("Forms.TextBox.1", "txtNextAirport", True)
With newTextBox
.Left = 20
.Top = ControlTop + 3
.Height = 15
.Text = txtAPID.Text
End With....................
I've got a list of story titles that are organized by genre, and I'd ultimately like to have a formula (not VBA) that automatically generates a "story id" based on the title's position within the genre (not the list overall). Here's a snippet of the list:
CREATE TABLES LIKE BELOW?IDGenreTitle
1.1GeneralTitle 1
1.2GeneralTitle 2
2.1Horror / SupernaturalTitle 3
2.2Horror / SupernaturalTitle 4
3.1Sci-FiTitle 5
3.2Sci-FiTitle 6
So what I'm looking for here is the formula that would give me the ID shown in the first column (I can modify it later for specific purposes). I've tried variations on the MATCH, INDEX, ROW, and VLOOKUP functions but just can't seem to get it quite right. Note: All the items in the Title column are unique - there are no duplicates.
I need to change multiple conditional formats which apply to cell ranges 1 row x 17 columns wide. The specific change I am making is able to be acrried out with the code
With Selection.FormatConditions(1).Font
.ColorIndex = xlAutomatic
End With
However, I dont want the user to have to manually select each 17 cell area. I WANT THE USER TO JUST MANUALLY SELECT THE FIRST OF THE 17 CELL AREA. I want to write a macro that will THEN select a row of cells, 17 cells long, from the MANUALLY SELECTED position as at the moment of running the macro. Unfortunately the cells are not always on the same row in each sheet. On one sheet they may be on row 5, on another row 8 and on another, row 15 etc. I was wanting to be able to select a 17 cell range 'relative' to where the cursor is. How do I write the code to select the relative cell range? Is there an answer in an R[1]C[1]:R[1]C[17] sort of code?
I have been trying to write a macro to add a page break in Excel 2007, but I have had no success.
A friend recomended:
Columns("CC:CC").Select
ActiveWindow.SelectedSheets.Vpagebreaks.add Before:= ActiveCell
This has not had any affect when I have printed.
I'm working with a very large spreadsheet which has somehow divided itself into print areas. Each section has 'Page 1' or the equivalent as a background, behind the data itself. I thought it would be simple to remove this, but for the life of me I can't see how. I'm using Excel 2007.
View 6 Replies View RelatedI suddenly can't adjust my page breaks in any Excel spreadsheet while in Page Break Preview. Is there some option to turn it on and off?
View 7 Replies View RelatedI have a macro that exports a row (or rows) to destination sheet ("Amber") based on criteria in Column K. Once exported I return to the source sheet ("Register") and the row that had the data that I wanted exported has exported the info but the row just sits where it was and appears to only 'clear the contents'. The macro I use is:
Sub Extract_FW_Rows()
Set ws = ActiveSheet
endrow = ws.Cells(65000, 1).End(xlUp).Row
For i = endrow To 1 Step -1
[Code] ......
I set up the macro on Excel 2002 in work as thats what we have....however when I input the same code into my home laptop working Excel 2007, all works perfectly i.e. it cuts the data to destination sheet, goes back to source sheet and the rows have moved up accordingly - I do realise that the action taken is a row removal i.e. I had 1000 rows, macro executes, I now have 999 rows in the source sheet.
What I would like is the same result in 2002 as I get in 2007.
I got the following script from another site. What it is intended to do is that it has three columns in a sheet.
Column A = Folder Name
Column B = Sender Name or Mail Subject
Column C = Values for Column B
Based on what values are in Column C (sender name or email subject), it will then look at the mails in outlook within the 'inbox' folder and look for the matching criteria and if it finds a match, move it to the folder named in column A (if folder does not exist, create folder)
Running the script, I get runtime error '438' object does not know property or method - on the line highlighted in red. I am using office 2007 and looking at the code, it is using late binding so no reference library is needed?
Code:
Option Explicit
Const olFolderInbox = 6
Sub moveOutlookMails()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
[Code] .......
I have encountered some difficulty in modifying a macro I wrote into what I need. I created a macro that searches a column (Column C) for a cell value of, "stop", and then it copies everything above that cell and pastes it onto another sheet. In the sample data set that I was using, "stop" first occurred in cell C541, so the macro copies C1:C540 and pastes it onto another sheet. The problem is that the macro created an absolute reference to C540. What I desire is for the macro to use the 'Find' function to locate the first occurrence of, "stop", offset one cell above that cell, and then reference the active cell (which was positioned by these last two steps) in the range that should be copied. Basically, I'm hoping to have cells C1 through the active cell copied and then pasted onto another sheet.
Code below.
Sub FAIL()
'
' FAIL Macro
'
'
Sheets("Reformatted").Select
Columns("C:C").Select
Selection.Find(What:="stop", After:=ActiveCell, LookIn:=xlValues, LookAt _
[Code] .......
In versions prior to Excel 2007, I was always able to copy a chart when I copied a tab. However, when I copy a tab in 2007, the data copies fine, but the chart does not. Something similar happens when I try to move a tab with a chart on it to another workbook.
View 5 Replies View RelatedI'm working in Excel 2007 and need to move data from multiple rows to a single row if the ID matches.Below is sample data I would be working with. I want to move data from columns F-U to the right of the original data in the row above it. I would also like to delete the rows that had data moved.
GIDSurnameNameEmployee Number OriginalDate of birthGranting ARE
Employing ARECountry Employing ARECHCM Supplier IDVehicle
Investmt. shares / Awards at termination dateMatching Shares
at termination dateTermination
[Code]....
i am totally new in excel and i am using excel 2007, my question is : how can i create an object in an excel sheet, so that when the mouse cursor move to it, it pops up anohter image?
View 4 Replies View RelatedI am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..
I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...
What would be the best way to accomplish this?
How to change from absolute references to relative references.
Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365
Each week I format a report that I receive that lists every product a particular employee closed. Its a list of each employee, the product and date. I'll have about 20-30 lines for each employee. I can easily sort by the employee name to have a nice list.
From here what I do is use a forumla to mark where each employee's name changes, then filter by that changed line and insert page breaks for each of these lines. Is there a way to macro this to save me time, having 270 employees takes alot of time still, because thats 270 manually inserted page breaks.
I want to set range a entire column,the rows are dynamic and i want to do it on all the columns in the sheet, my problem is that after i set the range(i hope i did good) i want to do a average of the range and to put the result in a new sheet on a row. I'm sorry for the mess in the code its one of my first macros
Option Explicit
Sub Average()
Dim R As Integer
Dim help As String
Dim Range1 As Range
Dim E As Integer
Dim Avg As Integer
R = 1
'/////CREATE NEW SHEET AND COPY THE FIRST TWO ROWS TO THE NEW ONE
Sheets("UXP data").Select
'Sheets.Add
Sheets("UXP data").Select......................
how can i define, in vba that after click on button in sheet1, it looks at value in cell B1 in this sheet and move my cursor exactly on this value in sheet2 where i have data in range (B4:C6000).
View 2 Replies View RelatedI have a 2003 vs. 2007 problem that I've spent a week or so trying to resolve, to no avail. I have created a fairly complex "dictator application" in Excel 2003. The application performs a bunch of calculations, and stores this inforamtion in a template workbook. The template itself contains one worksheet (to hold the data), and twelve empty "chart-sheets" which I later use to create plots. Of course, everything works fine and life is good in Excel 2003.
Flash forward two years, and half of my company is running 2007. When a 2007 user fires the plot creation routine, he is greated with a lovely error message (# 2147467259): "Method 'Position' of object 'Legend' failed".
Here's the full procedure I'm running to create the plots and position the legends:
Function bChartSumFileData(ByVal lTotalPlots As Long)
Const sSOURCE As String = "bChartSumFileData" 'Procedure Name (error-handling)
Dim bReturn As Boolean
Dim i As Long, j As Long, lNumSeries As Long
Dim sTemp As String, sPlotSetting As String
Dim chtTemp As Chart
Dim wksData As Worksheet, wksInfo As Worksheet
Dim serTemp As Series
Dim uSettings As CHART_SETTINGS
Dim vaPlotInfo As Variant
Obviously, lots of custom functions here; I'm also using a custom "type" to store chart settings (which I read from a *.txt file in a separate function). However, as far as the Legend goes, it shouldn't make a difference with the above code. When I stop execution after the error and look at the template workbook, the chart is created just fine, and the legend is visible, so I'm not sure why the error is occuring. Even better, the number of charts created prior to erroring-out is not fixed: sometimes the error occurs on the first plot, sometimes on the third, etc.
I have created a dashboard in excel and want to convert it into an interactive web page.
View 3 Replies View RelatedMy doubt it's at the final of the code when the condition If IE.Document.URL Like constantsValues.urlLoginData Then is true, it enter into a class an introduce the user and password from a login page AND then the page is REDIRECTED to another page SO when it goes out the IF CONDITION, the values that I had at the beginning in the IE Object are now DIFERENT because I'm now in a new page.
So that's why I put Application.Wait for 30 seconds, and then try to refresh the values of the IE object and get the new ones, but it always get the first values (the values of the login page :S) NO the new ones :S
How can I update the IE object to get the values of the actual page?
Code:
Private Sub runMacro_Click()
Dim constantsValues As CConstants
Set constantsValues = New CConstants
[Code].....
