+ Reply to Thread
Results 1 to 16 of 16

Change VBA to insert copied rows not paste them.

  1. #1
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Change VBA to insert copied rows not paste them.

    Hello! I am new to VBA as you will probably be able to tell. I have read a lot on this forum and I am having a great time learning!

    My problem:

    I need to copy rows of data from the master sheet(sheet4) and insert into other sheets depending on the data in column A. The below code does everything correctly except it paste the rows instead of inserting them which overwrites my subtotaling data at the bottom. If it helps I can insert/paste the copied rows into row 8 in all other sheets as they are all the exact same with exception to the master sheet.

    OR...would I be better off making my tech sheets section for pasting longer than then i expected the pasted data to be and have a code to delete unused rows?

    I really hope this is understandable as I am horrible at explaining things. lol

    The code I was trying:
    Please Login or Register  to view this content.
    I have attached a very short version of my workbook(as in the end it will have over 40 sheets) and what I need the end result to be. Thanks so much in advance!
    after code.xlsmbefore code.xlsm
    Last edited by baby_kay_2003; 10-01-2014 at 01:47 PM. Reason: code tags

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Change VBA to insert copied rows not paste them.

    Hi, baby_kay_2003,

    welcome to ExcelForum. Please have another read at Forum Rule #3 to understand why you are asked to wrap your procedure with code-tags.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    Oops! I thought I had done the code tags but I posted my code twice so I guess the second time around I didn't. Sorry and thanks

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Change VBA to insert copied rows not paste them.

    Hi, baby_kay_2003,

    Iīd go a different way to repeating the code but have an array filled with the fields to filter for:
    Please Login or Register  to view this content.
    The procedure Private Sub Worksheet_Change(ByVal Target As Range) doesnīt make any sense in Module1 as it would only be triggered behind a sheet.

    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    The sub by Val isn't being used. I was going to try it and I actually forgot it was there. Lol. I have been working on this for about a week trying so many things. I am on my way home now and will try your code. Thanks so much for your imput! I will let you know how it goes

  6. #6
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    Holger,

    Thank you so much for your reply!!! The code works so wonderfully!!! There is only one small(I hope) issue. If there is no data for one of the array numbers then the code stops. Is there a way to make it continue thru the rest of the array numbers? Also just because I am trying to learn and this may sound dumb but what in this code says what worksheet to insert into?

    Thanks Again
    baby_kay_2003

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Change VBA to insert copied rows not paste them.

    Hi, baby_kay_2003,

    thatīs due toi the fact that SpecialCells will raise an error if no such cells arenīt mfound on the sheet.

    Please Login or Register  to view this content.
    Ciao,
    Holger

  8. #8
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    That works perfectly!!! Thank you so much!
    baby_kay_2003

  9. #9
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    Just one final question. What part of the code states which worksheet to paste into?

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Change VBA to insert copied rows not paste them.

    Hi, baby_kay_2003,

    a tip on finding out by yourself: place the cursor into the procedure in the VBE and press F8 for single stepping through the macro (you may see the values for the varibales if you hover with the mouse over them).

    Please Login or Register  to view this content.
    Code loops through all worksheets in the workbook and compares the left 10 characters to match - if so wsTarg is set and the loop is left, After the end of the loop there is an extra check to have a valid worksheet to copy to.

    Ciao,
    Holger

  11. #11
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    Thank you so much for all your help!!

  12. #12
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    I am so sorry to bother you again! Sometimes when I run the macro I get this error.
    "run-time error '1004':
    application-defined or object-defined error"
    and it highlights this section
    Please Login or Register  to view this content.
    And I cannot find the problem for the life of me. I have checked that all tab names mach. I have also attached the workbook saved where the macro stops.
    here is the full code:

    Please Login or Register  to view this content.
    Thanks in advance!

    Correction I believe I found the problem finally...I believe it is when there is a tech number in column A that is not in the array and for which there is not a tab to insert it. Can it not just skip that and leave it there and move to the next? It works so perfectly until there is a small goof like this.
    Attached Files Attached Files
    Last edited by baby_kay_2003; 10-07-2014 at 11:25 AM. Reason: correction

  13. #13
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Change VBA to insert copied rows not paste them.

    Hi, baby_kay_2003,

    why do you ask the same question as in your post #9? If I have written the code correctly there is no wsTarg if the TechId isnīt found in the array and that TechID will be skipped (could be seen if proper indention to the code had been applied).

    Ciao,
    Holger

  14. #14
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    I am sorry if i have offended you. I am not asking the same question at all. The error seems to be occurring when there is a techid in column a that is not in the array(I think). My post #6 was if there was no data for one of the array numbers. I am confused by your last statement about indention.

  15. #15
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,099

    Re: Change VBA to insert copied rows not paste them.

    Hi, baby_kay_2003,

    no reason to worry about that .- I had to worry a bit more about the RTE1004.

    The reason for the run time error is showing when at least one row is hidden when no data is found for that ID.

    A small change to the code will take care of that:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  16. #16
    Registered User
    Join Date
    10-01-2014
    Location
    Tx
    MS-Off Ver
    2013
    Posts
    50

    Re: Change VBA to insert copied rows not paste them.

    Again you are amazing! It is working perfectly!! Thank you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Change in code to get it to insert two blank rows instead of one and paste text
    By Excelsemi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-23-2014, 12:11 PM
  2. EXCEL: Restrict User To Paste Entire Row / But Allow Insert Copied Row
    By gaikwad.mm@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2013, 08:16 AM
  3. Copy paste data, insert rows, insert data and change . to -
    By xWiZardx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2010, 01:44 AM
  4. Insert Copied Rows
    By Altec101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-23-2007, 06:49 AM
  5. Can't Insert Copied Rows
    By Kent in forum Excel General
    Replies: 0
    Last Post: 04-07-2005, 06:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1