Macro: Insert Sheet & Name As Cell Text

Dec 14, 2006

I have a workbook with single sheet called Fronpage. I want to insert a new sheet and rename the sheet based on the character value user specified in R1C1 in the Frontpage, and also would like the new sheet to be the last sheet in the workbook. I have tried to use DDE, and also Macro recording to do it. It didnt work out. Can anyone have any code to do it ?

View 2 Replies


ADVERTISEMENT

Macro To Insert Date In Cell When Text Is Inserted In Another Cell

Feb 20, 2008

I'm trying to write a macro similar to the one found here: here:http://www.techonthenet.com/excel/macros/checkbox.php.

Rather then use a textbox, I would like the cell to display the date when text is inserted in the cell to the left.

ie. I insert text (the letter 'a') in cell E11, and the date appears in cell F11.

I would also like the macro to do this for a range of cells ie. for E11 to F21, then from G11 to H21, then from I11 to J21, all the way to column IV.

View 9 Replies View Related

Excel 2007 :: Macro To Insert A Text Box Auto-sized To Fit A Cell?

Nov 30, 2011

I am trying to write a Macro that will insert a Text Box that auto-fits the shape of a cell to hide its content. Once finished, the Macro will need to lock the cell and the text box so the contents of the cell are hidden. The idea is that I want to share a spreadsheet with someone but want to hide individual cells for various reason.

Sheet1A1SAMPLE DATA2sample3sample4HIDDEN5sampleExcel 2007

I tried to record a macro as a starting point but it recorded nothing. I searched around and it seems to be an issue without a solution.

I could obviously change the formatting and the contents of the cell but the idea is to preserve the contents if possible.

View 4 Replies View Related

Insert Text As Comment On Another Sheet Based On Date

Jan 18, 2013

Is it possible to insert text as a comment on another sheet based on a date?

I have Sheet 1, that has 3 columns, Name, Date, Reason

Sheet 2 is a monthly calendar with the dates in E5:AH5 and the names from D6:D10.

What I am trying to do, is when they enter their name, date and reason on sheet 1, I want the reason to to be inserted on sheet 2 as a comment in the cell that matches the date and the name.

View 1 Replies View Related

Macro Run Copy Text From Active Cell And Find That Value In Another Sheet

Dec 16, 2008

I would like to create macro, where it would on macro run copy text from active cell and find that value in another sheet (in column H) and select that cell.

What I did is this:

View 5 Replies View Related

Macro To Insert Text

May 31, 2007

I need a macro that will insert text into merged cells. I have merged blocks where all will be exactly the same size one after another. (there are 19 blocks). The problem is that each month the size of the blocks change.(see sheet for example). Right now blocks are 8 rows will get to 16 by end of year. In each block I need a "- (city) (Name)" Each block has a different city and name that goes with it.

It would be an awesome time saver If I could click a button and have the city and names be entered into the appropriate spot no matter what month were in.

View 6 Replies View Related

Macro To Insert Web Browser On Sheet?

Oct 11, 2012

I am trying to use a macro to insert a webbrowser on a sheet and then link the webbrowser but it keeps giving an error that it doesn't support what I am trying to do.

Code:
Sub Macro1()
Sheets("sheet2").OLEObjects.Add(ClassType:="Shell.Explorer.2", Link:=False, _
DisplayAsIcon:=False, Left:=117.75, Top:=25.5, Width:=256.5, Height:= _

[Code].....

View 4 Replies View Related

Macro - Insert New Sheet If Value Found

Aug 29, 2013

I have a code that copies rows to another sheet if a value in D:D is found. BUT I need to add a "create sheet" IF the value exist.

So if VBA finds the value "TWO" in Column D, then insert a sheet at the end named "Two"

Then the code below will copy all the values of "TWO" over to sheet "TWO"

Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("D" & i).Value = "9" Then Rows(i).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i

In essence, the VBA should:
Search for "ONE" in D:D, not find anything and move on.
Search for "TWO" in D:D, will find it, Create a New Sheet named TWO, then the above Code to copy all matching rows into sheet TWO.
Search for "THREE" D:D, not find anything and move on....

There will be roughly 12 sheets created on average, so if I create all sheets beforehand.

View 1 Replies View Related

Insert Sheet & Name Macro Code

Apr 8, 2008

I am trying to insert 50 tabs (at different points in time during a macro) that each has a state abbreviation. I know how to get the macro to add a tab, but to name it, it wants to select " Sheet 4". The problem with this is, the tab that was created might be sheet 15 or sheet 1, and I need it to be able to name the tab no matter what sheet # it is.

View 7 Replies View Related

Insert Row Macro Based On Text String

Nov 20, 2009

I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler

The Data Begins in Row 5 of Column A

My Current Macro is as below which I have adapted from another one I used

It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.

View 6 Replies View Related

Macro Code To Find / Insert And Fill Text?

Oct 11, 2011

I need a macro script that goes to a particular cell in a column based on a criteria (say, locate the first instance where $0.21 appears in col. H), insert a row above it, and fill the row with the text "Note:These are above $0.20"

View 2 Replies View Related

Creating Macro To Insert Text Into Predefined Cells

Apr 22, 2014

I had posted earlier to see if there was a way to protect certain cells from being updated when using a linked excel spreadsheet to access. Since there have been no replies I'm assuming not.

Different approach, can I create a Macro that would run after the update that would insert my required text into each cell from A2 through to R2.

The text would always be the same and always in exactly the same cells. This would need to be an insert into and not a replacement of the data already in those cells.

A1 B1 C1 D1
State
BUName
InsuranceCompanyName
Claim
ROnumber
Renter Last Name

[Code] .......

A2 B2 C2 D2
CategoryFilter CategoryFilter CategoryFilter NoFilter

The text under each of the A2, B2, C2 etc is what I would want the macro to do for me, essentially putting back what the update from the linked source removed.

View 3 Replies View Related

Conditional Macro - Insert Text And Lock All Duplicate Cells Except For First One

Sep 16, 2012

I am new to VBA but I am trying to insert text and lock all duplicate cells except for the first one. Below is the macro I am trying to make. I keep getting an error on the line:

If(COUNTIF($A$9:$A$76,A9)=1,FALSE,NOT(COUNTIF($A$9:A9,A9)=1))

This is my macro:

Sub SetVoters()
'
' SetVoters Macro
'

'
Sub SetVoters()
'
' SetVoters Macro
'

'
ActiveSheet.Unprotect

[Code] ......

View 1 Replies View Related

VBA Code To Convert Excel To Text File Of DML Statements (Insert) Using Macro

Jun 11, 2014

I need a VBA code to create a Text file which should be in the form of DML (my scenario is to get Insert Statements for the Data available in the Excel) from Excel Sheet. And this has to be done by assigning a macro to command button where by clicking on it the DML text file should be generated. Even the syntax for the scenario will do.

View 1 Replies View Related

Insert Sheet Name In A Cell

Jun 5, 2009

try to insert sheet name in a cell but without luck.

there is anyway to do this 'simple' operation?

View 9 Replies View Related

Add/Insert Sheet Named Off Cell

Oct 6, 2006

I am trying to get VBA to insert a new sheet and have it be renamed or just named by data that is in a certain cell. My attempt:

sheets.add.name = range("A1")

View 3 Replies View Related

Automatic Insert Row If Cell Above Contains Text Or Else?

Aug 15, 2014

With "sheet1" there's a table c14:m38 with all formulas. But i want to expand the table if cell c38 contains a value or text.

Is it possible to automaticly insert a row containing the above formulas in row c14:m14?

View 12 Replies View Related

Insert Row On Sheet & Move Active Cell Row To It

Oct 23, 2007

I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time.

Example:

Sheet 1 – is current jobs and sheet 2 is old jobs.

My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2.

I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.

View 9 Replies View Related

Insert Text And A Cell Value In Conditional Formatting?

Aug 8, 2014

Is it possible to insert text an a cell value in conditional formating, i,e Ive got the conditional formatting:

=AND(H$4>=$B5;H$4<=$C5)

I want to insert this text whenever this condition is true once and not to repeay it:

="Load " &TEXT(G$5;"dd-mmmm") -- where G$5 is a vallue cell_

View 1 Replies View Related

How To Insert Blank Row Below A Cell Containing Specific Text

Oct 19, 2009

I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.

For example:


- All of my data is in column A

-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.

View 5 Replies View Related

Insert Text String From Inputbox In Cell?

Jan 13, 2012

I'm trying to write a bit of to take a text string collected from an inputbox, and paste it into a specific cell.

Should be easy but where my text string from the inputbox is "XYZ", when it enters it into the required cell it enters it as " ="XYZ" ".

Attempt at code is below -

Code:
Sub EmailEdit()
Dim Response As String
Response = Application.InputBox("Input administrator email address", , , , , , , 2)

'Check to see if Cancel was pressed.
If Response = "" Then

[Code] .....

View 2 Replies View Related

Insert Pic Into Comment For Every Cell That Has Text Containing String .jpg Or .png

May 10, 2009

I need a nudge in the right direction with how to amend the below code so that it :
1: cycles though all cells in a workbook, and sees whether the text reference of the cell contains a picture filename (i.e. searches for .png or .jpg in the cell contents)
3: if (1) is correct, it retrieves the picture from "C:/Users/jeff/Documents/Standards/" and pastes it as a cell comment background in the cell to the right (and overwrites any comment backgrounds that might already exist there)

The code below does something a bit different: it looks in a defined range, then adds a comment with a background picture retrieved based on the text in the cell to the left.

I'm sure this is a pretty basic change, but my VBA skills aren't up to it...I've only just started reading though Walkenbach's Power Programming! I'm using Excel 2007

Sub InsertComment()
'www.contextures.comxlcomments03.html
Dim rngList As Range
Dim c As Range
Dim cmt As Comment
Dim strPic As String

On Error Resume Next

Set rngList = Range("A1:A5")
strPic = "C:/Users/jeff/Documents/Standards/"
On Error Resume Next
For Each c In rngList
With c.Offset(0, 1)...................

View 9 Replies View Related

Insert Text From Cell Into Inputbox Message

Apr 21, 2006

I want to be able to specify inside the message the word that is in cell F6. So it would say "You Must Give A Reason For The Amount Of Mgr Voids For Shawn"... shawn being the name in cell F6.

If Range("F9").Value > 50 Then
MyInput = Application.InputBox("You Must Give A Reason For The Amount Of Mgr Voids For This Employee")
If MyInput = "" Then End
If MyInput = False Then End
ActiveSheet. Unprotect ("13792468")
ActiveSheet.Range("F9").AddComment
Range("F9").Comment.Visible = False
Range("F9").Comment.Text Text:="" & Chr(10) & (MyInput) & Chr(10) & ""
ActiveSheet.Protect ("13792468")
End If

View 3 Replies View Related

Find Cell With Text & Insert Formula Below

Jun 11, 2008

I want the macro to:

1. search A1:AZ1 to find the cell that has the text "VBA Test" in the cell. There could be other text in the cell as well - this is not an exact match - but these two words are the common text.

2. go to that cell

3. go to one cell below that

4. enter a formula (I've got it from here ....)

View 4 Replies View Related

Insert Variable From Sheet Cell Into SQL Statement In VBA Code

Oct 29, 2013

I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.

Here is my code

cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear

Set rs = New ADODB.Recordset

[Code] ........

This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.

View 1 Replies View Related

Insert Text In First Empty Cell In 2 Dimensional Range

Nov 20, 2013

I'm looking for a quick and easy way to insert a value in the first empty cell in a multi-column, multi-row range using VBA. I suppose I could loop through the range, but the table could grow to immense proportions and I don't want to slow everything down.

For example, the new value should be placed in cell C4. It doesn't matter whether the range is looped through the rows or columns, either will work just fine.

A
B
C

1
5
65
56

2
32
12
89

[code]...

View 1 Replies View Related

Insert Text From Once Cell Into A Formula In Another Cell

Sep 11, 2009

I built a formula that should work, but it's too long so I need to condense it.

I have three columns, column 1 has names, column 2 has a formula.

I have 15 sheets, each with a name that could appear in column 1.

If the cell in column 2, sheet 1 is Bob, I want it to pull H5 from sheet bob. That works as:

=IF(A5="Bob", 'Bob Data'!H4, "Work in Progress")

But if I build that formula for all the possible names, it's too long. Is there a way to make the formula autofill with the name in cell A5

So: =IF(A5="XXXX", 'XXXX'!H4, "Work in Progress")

View 13 Replies View Related

Find Empty Cell In Row After Certain Point Then Insert Values From Another Sheet

Jun 4, 2014

I'm trying to create a macro that when run, scans Row 12 (only after column E), Finds the first empty cell, then inserts the cell value from Sheet4 CellE8. Then the hard bit begins. I need it to insert cell info in all the cells below it, from different locations...

For example

A
B
C
D

[Code].....

In the above sheet, I need it to go to cell D2 and insert the values from Sheet4 CellE8, Then proceed to D3 and insert data from Sheet3 D4, then to cell D5 and insert data from Sheet1 A7, etc etc

I dont mind doing each cell individually, but they will always be in the same column (and row 2 "Value" will always be the one that determines the next empty column).

View 1 Replies View Related

VBA Macro - Insert Pic In Cell

Aug 1, 2008

when you decide to cancel the option from choosing any picture I keep getting the debug error, "Run-time error '1004': Insert method of Pictures class failed". I would like it not to error out at all, obviously.

Sub InsertPicture()

Dim myPicture As String

myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", , "Select Picture to Import")

'If myPicture = Null Then
'MsgBox ("You did not make a selection.")
'Else
If myPicture "" Then
ActiveSheet.Pictures.Insert (myPicture)
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Select

With Selection

.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = ActiveCell.RowHeight
.ShapeRange.Width = ActiveCell.ColumnWidth * 5.25 + 4
.Placement = xlMoveAndSize

End With

End If

End Sub

Private Sub CommandButton1_Click()
InsertPicture
End Sub

View 9 Replies View Related

Macro: Insert A Row A Cell = Particular Value

Jan 27, 2009

I need a macro in Excel sheet.
What I need is If Cell Value A4 = India then it should insert one row automatically after Row A10 & Value of Cell A 11 should be "VAT".

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved