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

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

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

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

That works perfectly!!! Thank you so much!

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

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

Thank you so much for all your help!!

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.

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

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.

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

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