Change VBA to insert copied rows not paste them.

baby_kay_2003

New Member
Joined
Oct 1, 2014
Messages
2
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 IDActivity IDOrder Sub TypeAccount NumberActivity DatePayment
DVOK8720061-1Q3QTLW6Former Install*3424*59/12/2014 4:09 PM $ 100.00
DVOK8720061-1Q4TJ35VNew Install**9*0*9*9/13/2014 2:10 PM $ 95.00
DVOK8720061-1Q7XVETCService3*35**049/13/2014 1:05 PM $ 25.00
DVOK8720061-1QBRTTBLService34*03349/13/2014 1:04 PM $ 25.00
DVOK8720061-1QBU5YNYNew Install*5*00049/12/2014 1:37 PM $ 125.00
DVOK8720061-1QCD2GW3Service**53*45*9/13/2014 1:04 PM $ 25.00
DVOK8720061-1QD3WQMQUpgrade*24003249/12/2014 1:37 PM $ 35.00
DVOK8720081-1Q6O4R56Former Install4*24**399/12/2014 6:17 PM $ 110.00
DVOK8720081-1Q9W91APFormer Install34**299*9/15/2014 4:23 PM $ 110.00
DVOK8720081-1QA5NNOQUpgrade4009****9/12/2014 3:45 PM $ 45.00
DVOK8720081-1QCP6VYWNew Install30*3*039/12/2014 10:05 AM $ 125.00
DVOK8720081-1QFETJRZNew Install2*05*4*09/15/2014 9:49 AM $ 125.00
DVOK8720081-1QGEFBFQNew Install**4*33*9/15/2014 1:05 PM $ 125.00
DVOK8720081-1QBXI29DUpgrade***4923*9/14/2014 2:11 PM $ 30.00
DVOK8720081-1QCF929UUpgrade*40*4*009/14/2014 2:25 PM $ 20.00
DVOK8720141-1PUH1L1RFormer Install**9*0*9*9/12/2014 3:41 PM $ 110.00
DVOK8720141-1Q5SO951Upgrade3*35**049/12/2014 9:00 AM $ 50.00
DVOK8720141-1QBK2CAFFormer Install34*03349/12/2014 12:33 PM $ 95.00
DVOK8720141-1QBR6H11Upgrade*5*00049/12/2014 9:58 AM $ 30.00
DVOK8720141-1QCMC58LService**53*45*9/15/2014 4:41 PM $ 25.00
DVOK8720141-1QD6UM5LNew Install**9*0*9*9/16/2014 12:28 PM $ 95.00
DVOK8720141-1QDHVE6MUpgrade3*35**049/16/2014 9:56 AM $ 35.00
DVOK8720141-1QFCO8Q8New Install34*03349/15/2014 9:52 AM $ 95.00
DVOK8720141-1QFSKEAUNew Install*5*00049/15/2014 2:44 PM $ 125.00
DVOK8720141-1QHYHZFOUpgrade**53*45*9/15/2014 4:26 PM $ 35.00
DVOK8720141-1QI0TBRPService335***39/15/2014 8:06 AM $ 25.00
DVOK8720141-1QK2V6LDService*5*****99/18/2014 5:49 AM $ 25.00
DVOK8720141-1QLA15YPNew Install*5***3*99/18/2014 9:56 AM $ 110.00
DVOK8720141-1QLHGMFGService**43**0*9/18/2014 4:16 PM $ 25.00
DVOK8720141-1Q829FOTNew Install*52*5429/16/2014 4:12 PM $ 95.00
DVOK8720141-1Q9Q3LLNUpgrade344*09*29/13/2014 11:16 AM $ 30.00
DVOK8720141-1QAQK7P2New Install**9*0*9*9/13/2014 12:49 PM $ 80.00
DVOK8720141-1QIKE00RUpgrade3*35**049/17/2014 2:25 PM $ 35.00
DVOK8720141-1QIRHG7WService34*03349/16/2014 1:20 PM $ 25.00
DVOK8720141-1QIVCSXUFormer Install*5*00049/17/2014 10:45 AM $ 110.00
DVOK872014
1-1QJVZ6T0Upgrade**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 Ending9/25/2014
Tech Name Tech Number DVOK872006
Tech IDActivity IDOrder Sub TypeAccount NumberActivity DatePayment
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 Ending9/25/2014
Tech Name Tech Number DVOK872006
Tech IDActivity IDOrder Sub TypeAccount NumberActivity DatePayment
DVOK8720061-1Q3QTLW6Former Install*3424*59/12/2014 4:09 PM $ 100.00
DVOK8720061-1Q4TJ35VNew Install**9*0*9*9/13/2014 2:10 PM $ 95.00
DVOK8720061-1Q7XVETCService3*35**049/13/2014 1:05 PM $ 25.00
DVOK8720061-1QBRTTBLService34*03349/13/2014 1:04 PM $ 25.00
DVOK8720061-1QBU5YNYNew Install*5*00049/12/2014 1:37 PM $ 125.00
DVOK8720061-1QCD2GW3Service**53*45*9/13/2014 1:04 PM $ 25.00
DVOK8720061-1QD3WQMQUpgrade*24003249/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 Ending9/25/2014
Tech NameTech 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>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,684
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top