Change VBA to insert copied rows not paste them.

[baby_kay_2003]

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! I have Office 2013 and Windows 8.1(dont knwo if that matters but to much information is better than not enough!)

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 so that my sum formulas on my total page would continue to work.

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

The code I was trying:
Sub Master_to_tech()

'872006
Dim lastrow As Long, lastcol As Long, nextrow As Long
Dim rng As Range

Application.ScreenUpdating = False

nextrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1

With Sheet4
lastrow = .Cells(Rows.Count, "F").End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
.AutoFilterMode = False
Set rng = .Range("A1", .Cells(lastrow, lastcol))
rng.AutoFilter field:=1, Criteria1:="DVOK872006"
rng.Offset(1, 0).SpecialCells(12).Copy Sheet2.Range("A" & nextrow)
rng.Offset(1, 0).EntireRow.Delete Shift:=xlUp
.AutoFilterMode = False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

Set rng = Nothing

'872008
Application.ScreenUpdating = False

nextrow = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1

With Sheet4
lastrow = .Cells(Rows.Count, "F").End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
.AutoFilterMode = False
Set rng = .Range("A1", .Cells(lastrow, lastcol))
rng.AutoFilter field:=1, Criteria1:="DVOK872008"
rng.Offset(1, 0).SpecialCells(12).Copy Sheet3.Range("A" & nextrow)
rng.Offset(1, 0).EntireRow.Delete Shift:=xlUp
.AutoFilterMode = False
End With
End Sub

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!

Mastersheet:

Tech ID	Activity ID	Order Sub Type	Account Number	Activity Date	Payment
DVOK872006	1-1Q3QTLW6	Former Install	*3424*5	9/12/2014 4:09 PM	$ 100.00
DVOK872006	1-1Q4TJ35V	New Install	**9*0*9*	9/13/2014 2:10 PM	$ 95.00
DVOK872006	1-1Q7XVETC	Service	3*35**04	9/13/2014 1:05 PM	$ 25.00
DVOK872006	1-1QBRTTBL	Service	34*0334	9/13/2014 1:04 PM	$ 25.00
DVOK872006	1-1QBU5YNY	New Install	*5*0004	9/12/2014 1:37 PM	$ 125.00
DVOK872006	1-1QCD2GW3	Service	**53*45*	9/13/2014 1:04 PM	$ 25.00
DVOK872006	1-1QD3WQMQ	Upgrade	*2400324	9/12/2014 1:37 PM	$ 35.00
DVOK872008	1-1Q6O4R56	Former Install	4*24**39	9/12/2014 6:17 PM	$ 110.00
DVOK872008	1-1Q9W91AP	Former Install	34**299*	9/15/2014 4:23 PM	$ 110.00
DVOK872008	1-1QA5NNOQ	Upgrade	4009****	9/12/2014 3:45 PM	$ 45.00
DVOK872008	1-1QCP6VYW	New Install	30*3*03	9/12/2014 10:05 AM	$ 125.00
DVOK872008	1-1QFETJRZ	New Install	2*05*4*0	9/15/2014 9:49 AM	$ 125.00
DVOK872008	1-1QGEFBFQ	New Install	**4*33*	9/15/2014 1:05 PM	$ 125.00
DVOK872008	1-1QBXI29D	Upgrade	***4923*	9/14/2014 2:11 PM	$ 30.00
DVOK872008	1-1QCF929U	Upgrade	*40*4*00	9/14/2014 2:25 PM	$ 20.00
DVOK872014	1-1PUH1L1R	Former Install	**9*0*9*	9/12/2014 3:41 PM	$ 110.00
DVOK872014	1-1Q5SO951	Upgrade	3*35**04	9/12/2014 9:00 AM	$ 50.00
DVOK872014	1-1QBK2CAF	Former Install	34*0334	9/12/2014 12:33 PM	$ 95.00
DVOK872014	1-1QBR6H11	Upgrade	*5*0004	9/12/2014 9:58 AM	$ 30.00
DVOK872014	1-1QCMC58L	Service	**53*45*	9/15/2014 4:41 PM	$ 25.00
DVOK872014	1-1QD6UM5L	New Install	**9*0*9*	9/16/2014 12:28 PM	$ 95.00
DVOK872014	1-1QDHVE6M	Upgrade	3*35**04	9/16/2014 9:56 AM	$ 35.00
DVOK872014	1-1QFCO8Q8	New Install	34*0334	9/15/2014 9:52 AM	$ 95.00
DVOK872014	1-1QFSKEAU	New Install	*5*0004	9/15/2014 2:44 PM	$ 125.00
DVOK872014	1-1QHYHZFO	Upgrade	**53*45*	9/15/2014 4:26 PM	$ 35.00
DVOK872014	1-1QI0TBRP	Service	335***3	9/15/2014 8:06 AM	$ 25.00
DVOK872014	1-1QK2V6LD	Service	*5*****9	9/18/2014 5:49 AM	$ 25.00
DVOK872014	1-1QLA15YP	New Install	*5***3*9	9/18/2014 9:56 AM	$ 110.00
DVOK872014	1-1QLHGMFG	Service	**43**0*	9/18/2014 4:16 PM	$ 25.00
DVOK872014	1-1Q829FOT	New Install	*52*542	9/16/2014 4:12 PM	$ 95.00
DVOK872014	1-1Q9Q3LLN	Upgrade	344*09*2	9/13/2014 11:16 AM	$ 30.00
DVOK872014	1-1QAQK7P2	New Install	**9*0*9*	9/13/2014 12:49 PM	$ 80.00
DVOK872014	1-1QIKE00R	Upgrade	3*35**04	9/17/2014 2:25 PM	$ 35.00
DVOK872014	1-1QIRHG7W	Service	34*0334	9/16/2014 1:20 PM	$ 25.00
DVOK872014	1-1QIVCSXU	Former Install	*5*0004	9/17/2014 10:45 AM	$ 110.00
DVOK872014	1-1QJVZ6T0	Upgrade	**53*45*	9/17/2014 2:01 PM	$ 35.00

<colgroup><col style="mso-width-source:userset;mso-width-alt:4039; width:85pt" span="4" width="114"> <col style="mso-width-source:userset;mso-width-alt:4551;width:96pt" width="128"> <col style="mso-width-source:userset;mso-width-alt:4039;width:85pt" width="114"> <col style="mso-width-source:userset;mso-width-alt:56;width:1pt" width="2"> </colgroup><tbody>
</tbody>

Tech Sheet:

Pay Period Ending	9/25/2014
Tech Name			Tech Number	DVOK872006
Tech ID	Activity ID	Order Sub Type	Account Number	Activity Date	Payment
		Sub Total			$0.00
		Company Fee			$0.00
		Invoice Deduction (Child Support)		$0.00	`
		Invoice Deduction (Loan from Eric)		$0.00
		Sub Total			$0.00
		Insurance			$ -
		Total			$0.00

<colgroup><col style="mso-width-source:userset;mso-width-alt:3925; width:83pt" span="4" width="110"> <col style="mso-width-source:userset;mso-width-alt:4551;width:96pt" width="128"> <col style="mso-width-source:userset;mso-width-alt:3925;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:56;width:1pt" width="2"> </colgroup><tbody>
</tbody>

After Code:
blank on master sheet




After code tech sheet:

Pay Period Ending	9/25/2014
Tech Name			Tech Number	DVOK872006
Tech ID	Activity ID	Order Sub Type	Account Number	Activity Date	Payment
DVOK872006	1-1Q3QTLW6	Former Install	*3424*5	9/12/2014 4:09 PM	$ 100.00
DVOK872006	1-1Q4TJ35V	New Install	**9*0*9*	9/13/2014 2:10 PM	$ 95.00
DVOK872006	1-1Q7XVETC	Service	3*35**04	9/13/2014 1:05 PM	$ 25.00
DVOK872006	1-1QBRTTBL	Service	34*0334	9/13/2014 1:04 PM	$ 25.00
DVOK872006	1-1QBU5YNY	New Install	*5*0004	9/12/2014 1:37 PM	$ 125.00
DVOK872006	1-1QCD2GW3	Service	**53*45*	9/13/2014 1:04 PM	$ 25.00
DVOK872006	1-1QD3WQMQ	Upgrade	*2400324	9/12/2014 1:37 PM	$ 35.00
		Sub Total			$430.00
		Company Fee			$0.00
		Invoice Deduction (Child Support)		$0.00	`
		Invoice Deduction (Loan from Eric)		$0.00
		Sub Total			$430.00
		Insurance			$ (21.07)
		Total			$408.93

<colgroup><col style="mso-width-source:userset;mso-width-alt:3925; width:83pt" span="4" width="110"> <col style="mso-width-source:userset;mso-width-alt:4551;width:96pt" width="128"> <col style="mso-width-source:userset;mso-width-alt:3925;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:56;width:1pt" width="2"> </colgroup><tbody>
</tbody>

Total page doesnt work anymore after manual insert of rows...only on paste (if I have enough rows...but then I have to manually delete them)

Pay Period Ending	9/25/2014
Tech Name	Tech Number	After Insurance	Before Insurance	Sub total
Brandon Stancell	DVOK872006	$ 408.93	$ 430.00	$ 408.93
Chris Jetton	DVOK872008	$ 656.19	$ 690.00	$ 690.00
Oscar Lopez	DVOK872014	$ 1,226.79	$ 1,290.00	$ 1,290.00
Total		$ 2,291.91	$ 2,410.00	$ 2,388.93

<colgroup><col style="mso-width-source:userset;mso-width-alt:4551;width:96pt" width="128"> <col style="mso-width-source:userset;mso-width-alt:3527;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:4295;width:91pt" width="121"> <col style="mso-width-source:userset;mso-width-alt:4295; width:91pt" span="2" width="121"> <col style="mso-width-source:userset;mso-width-alt:227;width:5pt" width="6"> </colgroup><tbody>
</tbody>

 

[baby_kay_2003]

Trying to edit to put in code tags but do not see a way to edit..