Copy and insert macro

I have been trying to create a button that inserts a group 
of cells between existing cells on a worksheet. I have 
been able insert those cells at the end of the worksheet 
but have been unable to figure out how to insert them 
between exisiting cells. 

To clarify; The following is a simple representation of 
what I am trying to accomplish:

Row 1 - Task Name and cost 
Row 2 - Task Name and cost
Row 3 - Sub Totals and markups 
Row 4 - End of used cells

I need to be able to add multiple tasks between the 
last 'Task Name and cost' and 'Sub Totals and markups'. I 
would like to accomplish this function through the use of 
a command button which would insert the new task between 
position 1 and 2 when clicked. 

It is possible to have dozens of tasks once the sheet is 
completed but I need the ability to add those tasks on an 
individual basis. 

Could anyone give me some suggestions on how to accomplish 
the above? I would greatly appreciate any assistance.

Thanks,

Dennis

0
dkalis (3)
10/1/2003 12:45:52 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
318 Views

Similar Articles

[PageSpeed] 33

Dennis
    You need to give more info about what you want.  What you want to do is
fairly easy to accomplish.  Some specifics you need to provide are:
Do you want to insert a blank row?
Do you want to insert more than one blank row in each instance?
How do you want to designate where you want the blank row inserted?  For
instance, above the row with the active cell?  Two rows down from the row
with the active cell?  Etc?
You say to insert a new task.  Do you mean you want to copy some data from
somewhere and paste it in a newly inserted blank row?
Post back with more detail.   HTH   Otto
"Dennis" <dkalis@hotmail.com> wrote in message
news:1b5ea01c38819$f2b2cd30$a601280a@phx.gbl...
> I have been trying to create a button that inserts a group
> of cells between existing cells on a worksheet. I have
> been able insert those cells at the end of the worksheet
> but have been unable to figure out how to insert them
> between exisiting cells.
>
> To clarify; The following is a simple representation of
> what I am trying to accomplish:
>
> Row 1 - Task Name and cost
> Row 2 - Task Name and cost
> Row 3 - Sub Totals and markups
> Row 4 - End of used cells
>
> I need to be able to add multiple tasks between the
> last 'Task Name and cost' and 'Sub Totals and markups'. I
> would like to accomplish this function through the use of
> a command button which would insert the new task between
> position 1 and 2 when clicked.
>
> It is possible to have dozens of tasks once the sheet is
> completed but I need the ability to add those tasks on an
> individual basis.
>
> Could anyone give me some suggestions on how to accomplish
> the above? I would greatly appreciate any assistance.
>
> Thanks,
>
> Dennis
>


0
ottomhello (94)
10/1/2003 2:25:54 PM
Hi Dennis,
See
   Insert a Row using a Macro to maintain formulas
   http://www.mvps.org/dmcritchie/excel/insrtrow.htm

You will have to redo your formulas to use OFFSET
before you use the macro, which is also described there.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Dennis" <dkalis@hotmail.com> wrote in message news:1b5ea01c38819$f2b2cd30$a601280a@phx.gbl...
> I have been trying to create a button that inserts a group
> of cells between existing cells on a worksheet. I have
> been able insert those cells at the end of the worksheet
> but have been unable to figure out how to insert them
> between exisiting cells.
>
> To clarify; The following is a simple representation of
> what I am trying to accomplish:
>
> Row 1 - Task Name and cost
> Row 2 - Task Name and cost
> Row 3 - Sub Totals and markups
> Row 4 - End of used cells
>
> I need to be able to add multiple tasks between the
> last 'Task Name and cost' and 'Sub Totals and markups'. I
> would like to accomplish this function through the use of
> a command button which would insert the new task between
> position 1 and 2 when clicked.
>
> It is possible to have dozens of tasks once the sheet is
> completed but I need the ability to add those tasks on an
> individual basis.
>
> Could anyone give me some suggestions on how to accomplish
> the above? I would greatly appreciate any assistance.
>
> Thanks,
>
> Dennis
>


0
dmcritchie (2586)
10/1/2003 2:50:32 PM
Otto,

First of all thanks for the help. Here are the answers to 
your questions:

Do you want to insert a blank row? - Yes, I do, but only 
one blank between each instance.

How do you want to designate where you want the blank row 
inserted? - I would like the row to be inserted below the 
last active cell.

With regards to the insertion of a new task; what I am 
trying to do is establish a worksheet that will allow me 
to copy a group of cells which contains the basic 
information on the new task. This basic information is 
then modified to describe the specific task, such as what 
is to be accomplished and the charge out rates associated 
with each member of the workforce. So, I would like to 
copy some data and insert it into the newly inserted blank 
rows but that insertion point needs to fall below the 
previous task and above the calculations for the mark ups 
and totals.

If you need any more information please let me know and I 
greatly appreciate your help with this.

Thanks,

Dennis
>-----Original Message-----
>Dennis
>    You need to give more info about what you want.  What 
you want to do is
>fairly easy to accomplish.  Some specifics you need to 
provide are:
>Do you want to insert a blank row?
>Do you want to insert more than one blank row in each 
instance?
>How do you want to designate where you want the blank row 
inserted?  For
>instance, above the row with the active cell?  Two rows 
down from the row
>with the active cell?  Etc?
>You say to insert a new task.  Do you mean you want to 
copy some data from
>somewhere and paste it in a newly inserted blank row?
>Post back with more detail.   HTH   Otto
>"Dennis" <dkalis@hotmail.com> wrote in message
>news:1b5ea01c38819$f2b2cd30$a601280a@phx.gbl...
>> I have been trying to create a button that inserts a 
group
>> of cells between existing cells on a worksheet. I have
>> been able insert those cells at the end of the worksheet
>> but have been unable to figure out how to insert them
>> between exisiting cells.
>>
>> To clarify; The following is a simple representation of
>> what I am trying to accomplish:
>>
>> Row 1 - Task Name and cost
>> Row 2 - Task Name and cost
>> Row 3 - Sub Totals and markups
>> Row 4 - End of used cells
>>
>> I need to be able to add multiple tasks between the
>> last 'Task Name and cost' and 'Sub Totals and markups'. 
I
>> would like to accomplish this function through the use 
of
>> a command button which would insert the new task between
>> position 1 and 2 when clicked.
>>
>> It is possible to have dozens of tasks once the sheet is
>> completed but I need the ability to add those tasks on 
an
>> individual basis.
>>
>> Could anyone give me some suggestions on how to 
accomplish
>> the above? I would greatly appreciate any assistance.
>>
>> Thanks,
>>
>> Dennis
>>
>
>
>.
>
0
dkalis (3)
10/1/2003 4:07:35 PM
Hi Dennis,

Below the InsertRowsAndFillFormulas macro on
    http://www.mvps.org/dmcritchie/excel/insrtrow.htm
is this macro which invokes the above.   I may be wrong but it would seem
to me that inserting the blank row is really so you can enter information
into it.

Sub InsertBeforeTotalinColumnA()
    Columns("A:A").Find(What:="total", After:=Range("A2"), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(-1, 0).Activate
    Call InsertRowsAndFillFormulas(1)   'see my  insrtrow.htm  page
End Sub

Otherwise specifically what you asked for would be (dependent on
something in column A),  Inserting a blank row.

Sub InsertBlankRowBeforeLast()
   Cells(Rows.Count, "A").End(xlUp).EntireRow.Insert
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Dennis" <dkalis@hotmail.com> wrote in message news:054101c38836$20e117e0$a101280a@phx.gbl...
> Otto,
>
> First of all thanks for the help. Here are the answers to
> your questions:
>
> Do you want to insert a blank row? - Yes, I do, but only
> one blank between each instance.
>
> How do you want to designate where you want the blank row
> inserted? - I would like the row to be inserted below the
> last active cell.
>
> With regards to the insertion of a new task; what I am
> trying to do is establish a worksheet that will allow me
> to copy a group of cells which contains the basic
> information on the new task. This basic information is
> then modified to describe the specific task, such as what
> is to be accomplished and the charge out rates associated
> with each member of the workforce. So, I would like to
> copy some data and insert it into the newly inserted blank
> rows but that insertion point needs to fall below the
> previous task and above the calculations for the mark ups
> and totals.
>
> If you need any more information please let me know and I
> greatly appreciate your help with this.
>
> Thanks,
>
> Dennis
> >-----Original Message-----
> >Dennis
> >    You need to give more info about what you want.  What
> you want to do is
> >fairly easy to accomplish.  Some specifics you need to
> provide are:
> >Do you want to insert a blank row?
> >Do you want to insert more than one blank row in each
> instance?
> >How do you want to designate where you want the blank row
> inserted?  For
> >instance, above the row with the active cell?  Two rows
> down from the row
> >with the active cell?  Etc?
> >You say to insert a new task.  Do you mean you want to
> copy some data from
> >somewhere and paste it in a newly inserted blank row?
> >Post back with more detail.   HTH   Otto
> >"Dennis" <dkalis@hotmail.com> wrote in message
> >news:1b5ea01c38819$f2b2cd30$a601280a@phx.gbl...
> >> I have been trying to create a button that inserts a
> group
> >> of cells between existing cells on a worksheet. I have
> >> been able insert those cells at the end of the worksheet
> >> but have been unable to figure out how to insert them
> >> between exisiting cells.
> >>
> >> To clarify; The following is a simple representation of
> >> what I am trying to accomplish:
> >>
> >> Row 1 - Task Name and cost
> >> Row 2 - Task Name and cost
> >> Row 3 - Sub Totals and markups
> >> Row 4 - End of used cells
> >>
> >> I need to be able to add multiple tasks between the
> >> last 'Task Name and cost' and 'Sub Totals and markups'.
> I
> >> would like to accomplish this function through the use
> of
> >> a command button which would insert the new task between
> >> position 1 and 2 when clicked.
> >>
> >> It is possible to have dozens of tasks once the sheet is
> >> completed but I need the ability to add those tasks on
> an
> >> individual basis.
> >>
> >> Could anyone give me some suggestions on how to
> accomplish
> >> the above? I would greatly appreciate any assistance.
> >>
> >> Thanks,
> >>
> >> Dennis
> >>
> >
> >
> >.
> >


0
dmcritchie (2586)
10/1/2003 5:13:44 PM
Reply:

Similar Artilces:

Sub Level project copy
We want to copy sub level entries from on project to another. So we chose "project cost-item copy" but it just won,t work We used the project copy instead anyone knows How???? Or in this case what is the easy way to copy SPjt departement Data from one project to another? Fabien ...

I am trying to write a macro using If...Then
I am trying to write a macro that will look at the value in column G. If that value is blank or zero, then select the entire row and delete it. Then I want it to populate columns A with a number "111119" and column H with "TEXT" Here's what I tried, but it's not working. (There are other sorts, etc. that precede this item in the macro, that seem to be working fine.) If Range("g:g") < 1 = true Then ActiveWorkbook.Worksheets("Tab3 Upload").Rows.Select Selection.Delete End If If Range("b:b") >...

bizarre excel copy/paste problem
I've had this problem for as long as I can remember with Excel 2000 and upgrading to Excel 2002 has not changed anything. Any input is appreciated. When I copy a cell (ctrl+c) and try to paste (ctrl+v) into the SAME worksheet, Excel will just paste it once and deselect the highlighted cell. That single copy works fine (ie while copying formulas), but it just wont let me paste multiple times. Upgrading Excel has not changed this behavior. Interestingly, MS Word does not seem to have this problem. Also if I use the clipboard, I can paste repeatedly. Does this make sense to anyone? I'...

disappearing macros #2
I have a user who has successfully recorded some simple macros and saved them to the Personal Macro file. the macros have worked up till now. They have now disappeared from the macro list. Tools, macro, macros produces a blank list. I tried to find the macros by unhiding the personal book but the unhide option was greyed out - Personal wasn't in the Windows file list though and we couldn't display it in any other way. I thought that it may have been overwritten so tried to create another one. I got a message to say that it was already open, went to the Windows menu and t...

use macro and allow undo
Hello. I have a macro that I recorded which works great. It does a copy and special paste to another work sheet. The only problem is that after it has run, if I made some kind of mistake and it copied the wrong stuff (or whatever), I cannot click on undo or alt-backspace. Is there anyway that the copy and paste of the macro can be undone this way? Thanks, Ben Hi Ben Sorry, you can not undo any macro operation.... Regards, Per� "ben" <abc@efg.com> skrev i meddelelsen news:%23DwBhBsUJHA.5860@TK2MSFTNGP02.phx.gbl... > Hello. > > I have a macro that I recorded ...

? Rich Edit Formatting Lost When Inserting At Position 0
Hi, I'm using a CRichEditCtrl but am having trouble with the formatting. I use SetSel and ReplaceSel to insert text into the control and if the text is inserted in the middle or at the end of the control's buffer it works just fine but if I insert it at the start (SetSel(0,0)) then it takes on the formatting (both character formatting and paragraph formatting) of the text immediately after it (the text formerly at 0). There is a small note about ReplaceSel in MSDN: "This function will format the inserted text with the existing character formatting. When replacing the e...

Macro and hidden sheets
Hi, I have a WB with 10 worksheets. At the time I did this WB, I also created a Macro that had the task to change the format of all the sheets. Today, I have decided to hide some of the sheets and the macro does no longer work. I guess this is because it cannot find the sheets which are hidden. My question is: Is there a way to allow the Macro to work as before, meaning to continue changing the format of the hidden sheets as well? Something like a condition that says, if Sheet XY is hidden then look for it into etc. Thank you Alex Hi Alex, A hidden sheet cannot be selected, so try r...

Replacing export with descriptions -Vlookup Macro
I have a monthly report that I export from an old system. Column A contains a number and I have been using VLookup to reference the description of the number from Worksheet2 Column A, B with Column B listing the description. Is there a way of creating a macro to do this? If so, will I still have to create an empty column for the result or can the macro replace the data in the cell? Thanks! Hi Joey, You can write a macro. The macro could replace the cell containing the number. Something like this: Sub mytest() For Each cell In Selection cell.Value = WorksheetFuncti...

How does one copy subtotal rows?
How does one copy subtotal rows? -- Since my access to NNTP is limited, a copy of your reply to ALEXANDEReBARNEs@Yahoo.Com is especially appreciated. Alexander One usually selects the rows he wishes to copy, Then hits <F5>, Then, in the "GoTo" window clicks on "Special", Then, in the "GoToSpecial" window clicks on "VisibleCellsOnly", then <OK>. Now, right click in that selection and choose "Copy", Then navigate and paste to "wherever". -- HTH, RD ------------------------------------------------------------------...

How to copy/paste from Excel to Word without showing gridlines?
How can I copy/paste from Excel 2007 to Word without the image showing the gridlines? Thanks If you are going to paste as image instead of Table, turn OFF gridlines in Excel before you copy. S wrote: > How can I copy/paste from Excel 2007 to Word without the image showing the > gridlines? Thanks If you just want to copy/paste the text (not as an object): 1. Copy the cells you want from Excel 2. In Word, Edit<Paste Special<Unformatted Unicode Text -- -Brigette "Bob I" wrote: > If you are going to paste as image instead of Table, turn...

How do I copy a Word text file into Excel in a way that each char.
How do I copy a Word text file into Excel in a way that each character ends up in a separate Excel cell? Paste each line into column A. Then put this in B1: =MID($A1,COLUMN()-1,1) drag to the right (as far as you need--but you've only got 256 total columns) and drag down the range Newtech programmer wrote: > > How do I copy a Word text file into Excel in a way that each character ends > up in a separate Excel cell? -- Dave Peterson Thank you. Very helpful. "Dave Peterson" wrote: > Paste each line into column A. > Then put this in B1: > =MID($A1,CO...

Help with macro code #2
I have a simple command button macro to hide a work sheet page. Is ther a way to keep the sheet from being hidden if there is a value in boxe e.g �g13,o13,g24,o24� then open a msgbox �sheet in use�. Thanks fo any help with this Private Sub CommandButton5_Click() Application.ScreenUpdating = False Sheets("Sun Misc Log").Visible = False Application.ScreenUpdating = True End Su -- Db171 ----------------------------------------------------------------------- Db1712's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelfo...

I can't get pictures to appear when I insert tem in a frame.
when I insert a picture file into a document, it does't appear on the sceen but seems to be there nevertheless. Do you have View, Pictures, Detailed display enabled? If not maybe your video card needs upgrading. Read the fourth FAQ here: http://ed.mvps.org/Static.aspx?=Publisher/FAQs -- Mary Sauer http://msauer.mvps.org/ "Kilno" <Kilno@discussions.microsoft.com> wrote in message news:DC42D33A-C0EB-4F1C-9D1E-2ECC56936509@microsoft.com... > when I insert a picture file into a document, it does't appear on the sceen > but seems to be there nevertheless. ...

Need help with copy if
What I am trying to do is copy column A-D from "scheduled" worksheet t "test" worksheet if column X has an "X" in the column. Is thi possible and if so how is it done -- jkrist46 Enter and run this small macro: Sub Macro1() Dim r1 As Range Dim r2 As Range Dim L As Long Worksheets("scheduled").Activate For L = 1 To 65536 If Cells(L, 24).Value = "X" Then Set r1 = Worksheets("scheduled").Range("A:D") Set r2 = Worksheets("test").Range("A:D") r1.Copy r2 Exit Sub Else End If Next ...

my macros disappear... is it SP2?
Good Morning! My macros which I use to sort data in excel have not worked since I installed SP2... it may be unrelated?? Here is what happens: When I click on the button which I setup to launch the macro, it brings up the macro list, like it does not have one set. When I select one... and then hit the button again... same thing... like it will not allow the macros to be assigned to it. I have tried rewriting the macros... I have created a new toolbar... everything. My only thought is that having resently installed SP2, that changed some security setting somewhere. I have checked t...

When copying and pasting charts the chart size decreases
Sometimes when in excel I can not copy charts and paste them without them automatically decreasing in size by what looks to be a set ratio. i know to select "don't move or size with cells", but this does not help. This problem is erratic and can just start occurring in a worksheet after i have pasted other charts without this problem. Any help with this would be much appreciated. In Excel 2003 and earlier, if the window zoom is not 100%, if you copy an embedded chart and paste it, it will be pasted at a size approximately equal to the original size times the zoom. This is...

Copy Top 10 conditional formatting down 1000+ rows
I have 20 columns which I am using top 10 conditional formatting. When I copy this (B3:T3) down to the bottom of my spreadsheet, the range changes to (B4:T1200) instead of each row (such as row 4 = B4:T4 and row 10 = B10:T10). Any ideas on how to copy this logic down the spreadsheet without doing it row by row? thanks This code should work. Start with leftmost cell in top, conditionally formatted row. Good luck! Dim MyCounter As Integer Range(Selection, Selection.End(xlToRight)).Select Application.CutCopyMode = False Selection.Copy For MyCounter =...

Copies of Inbox and Sent Items not working in Outl;ook Express
I needed to reload my entire machine and wanted to save a copy of the Inbox DBX file and Sent Items DBX file. I contacted my service provider. They instructed me to do the following: 1. In Outlook Express under Tools/Options/Maintenance click the Store Folder. This displays the location of these two files. 2. Copy the location 3. Click Start/Run, paste the location and click OK. 4. This opens the folder with that location. 5. Copy the Inbox and Sent Items and store them (I did on my backup CD). They told me that after I reloaded the machine I could then copy the files from the ...

insert into
I have an insert into query which is as follows; strgoaldescription = InputBox("Type new goal.") strservicetype = InputBox("This goal is for what service?") intprGroup = InputBox("Indicate the pr_group number if any") intprGroup = IIf(intprGroup = "", Null, intprGroup) strsql = "insert into goal (goal_description,los_id,pr_group_id) values('" & strgoaldescription & "', '" & strservicetype & "', " & IIf(intprGroup = "", Null, intprGroup) & ")" ...

Outlook 2003, "Insert ink annotations" is not available when writting new e-mail
I have a tablet Pc and office 2003. All the sudden, cant not insert ink annotations in new e-mails. The corresponding icon is gray and l can not click it. However, this works in Power Point and Outlook. Any ideas? Thanks! make sure plain text isn't selected. "ANGEL REBOLLO" <rebolloa@hotmail.com> wrote in message news:05b501c3b87c$4b1c0990$a401280a@phx.gbl... > I have a tablet Pc and office 2003. All the sudden, cant > not insert ink annotations in new e-mails. The > corresponding icon is gray and l can not click it. > However, this works in Power Point...

Macro code mod
Hi, The macro below allows users to group/ungroup rows in a protected sheet. Question: how to modify the VB code so I can incorporate additional tab names or better yet, have the macro apply to the entire workbook? Thanks in advance for your help, Charlie Sub workbook_open() With Worksheets("Sheet1") .Protect Password:="xlhelp", userinterfaceonly:=True .EnableOutlining = True End With End Sub Sub workbook_open() For Each sh In Activeworkbook.Worksheets With sh .Protect Password:="xlhelp&q...

Enable macros
I've got a fairly compex workbook with lots of named ranges. I've been using and updating this WB for over a year. All of a sudden, I now get the following message every time I enter something in any field: "The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros." I don't have any macros in this workbook. Les ...

Rule to move leaves a copy in Inbox ???
I have a number of rules that move email to varying boxes based on word in the senders address. Then the last rule moves any remaining emails to other folders based on account. Problem is I end up with a copy in both places. I have checked and rechecked to make the rule is to MOVE the emails, not copy. The rule is definitely MOVE. Why do I end up with a copy in both places ? This is Outlook 2003, but I recall having that problem with OL XP too. I ended up using a third party tool, but I would rather not do that again if I can help it. Thanks, Scott Do you have the stop processing add...

Copy Functionality Lost
Up until a day or two ago, I could select a cell to copy, paste it into another cell and the original copied cell would remain available to be copied again (it would be highlighted with the dotted border). Now, after one paste the copy cell reverts to normal, and I have to re-select it to copy into another location. I know I can use CTRL or SHIFT, but I find the retention of the copy ability useful. This is a networked office PC and imagine I might have to ask our IT Dept (as Administrator) to do something. Any ideas appreciated, thanks If you paste by pressing Enter you'll exit ...

Scan and copy cells from one spreadsheet to another.
How to take data from one spreadsheet and copy it to another. The porblem is I have to scan columns on both for a match first. The only example I could think of is something like this. If A5(on first spreadsheet) = C68(on second spreadsheet) then copy H68(from second spreadsheet) to E5(on first spreadsheet) else enter 0 There is over 1500 lines so I would hate to have to do this manually. Thanks! Hi Mark do you mean: If the value occurs in column A on the first sheet and in column C on the second sheet, then copy the value from column H of the same row as it occured in in C (of the s...