VBA to copy/paste portion of a row if criteria is met from one worksheet to another

Each month, we run a report to perform a distribution of fees for various fire districts in the county. This data is dropped into this excel program on the "Data" worksheet.  From there, a staff member has been sorting the data in Column E (Fire District Description) and then cutting and pasting the rows of data for that fire district to the matching worksheet.  For example, rows 2A through J8 would be copied to the worksheet named FD1 and pasted in cell A8 (we leave row 7 blank between the headers).  We only want the data from column A thru J to copy over to each worksheet as there are formulas in columns L thru R that will calculate once the data arrives.  I am sure we could automate the formulas to generate as well only when data is present, but I am new to macros and would be satisfied if we could simply get the data to go to the appropriate worksheets by using a button to click once the data is dumped on the "Data" worksheet.

I've allowed for data up to row 4,000 as I don't believe we will ever exceed that number of rows.  
I have attached a sample file.

Pay no attention to the other three worksheets (REPORT-ALL FDs, JE and RECAP). I left them in place in the event that adding in other worksheets would alter the macro so I wanted you to see the exact order of the worksheets.

Each month, we would like to automate this process so that once we dump the data on the "DATA" worksheet, we can use a button to click and have the data copy over automatically.  We would also like to be able to clear it afterwards to prepare for the next month.

Thanks for any help you can provide with this.
Johnette ConnelleyBudget Analyst / ProjectsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
The data in column "E" appears to be already sorted. Are you saying that that will not be the case in the real world?
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
Yes.  It wont be sorted when we dump the data initially. Thanks.
Martin LissOlder than dirtCommented:
Try this. You'll find two buttons above the 'Fire District Description' heading.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Martin LissOlder than dirtCommented:
A word of caution. Please be aware when adding new sheets that the 'Clear Districts' button will clear data on all sheets where the sheet name starts with "FD" and is less than 5 characters in length .
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
This looks great with only one other change.  Instead of moving the data, can it be copied and still leave the data on the data sheet?  with that change, it works perfectly!
Martin LissOlder than dirtCommented:
Yes, sure, but I found a big problem; the 'Clear Districts' button is deleting the formulas along with the data on the district sheets. I'm working on it but I have to leave for a couple of hours. I'll post a correction when I have it.
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
I noticed that as well while I was working with it some more.  I hate having all of those formulas running down but wasn't sure of another way to do it..  THanks for your help!  Its no rush. I really appreciate what you are doing!
Martin LissOlder than dirtCommented:
In this workbook:
  • You don't need to worry about the number of data rows in the district sheets because I now add the row formulas along with the data. Note that to make the coding easier I added a sheet called 'Formulas' that contains the needed formulas and you can hide that sheet if you like.
  • I initially also deleted all the data rows (down to 4000) so that my code which uses 'UsedRange.Rows.Count' does not need to do extra work. If you copy my code into another workbook you should do the same.
  • The data DOESN'T need to be sorted.

Would you like a msgbox that says when the distribution and/or the clearing is done?
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
The message box would be WONDERFUL!
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
In testing the last attachment, I got a Run-time error '9': Subscript out of range.  See attached screenshot.  If for some reason the screenshot doesn't show up for you, it was showing up about middle ways through the code.  This is the line that was highlighted:
 lngDestRow = Sheets(strSheet).Range("A1048576").End(xlUp).Row
Also, I noticed that the distribution isn't correct now. After the distribution, you only see FD11 on the DATA sheet now (you can click on the filter to get all of the data back, but when the code stops, you only see FD11.)
Then, if you go to the individual tabs, FD1 looks good, but there is no info on FD2 and FD3 only shoes FD11 data.  Others are like that too and then when you get to FD11, it includes FD1, FD10 and FD11.  
Thanks for your hard work on this!  Its much closer than I was able to get on my own, for sure!  

Martin LissOlder than dirtCommented:
I didn't get that error when I ran it, and when my execution was done all the data was there, but I'll look into it.

A couple of questions:
  • I said that the data didn't need to be sorted, but I could be wrong. Was the data sorted?
  • Did you change the data in any way?
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
No. I didn't changed the data.  It wasn't sorted when I downloaded it.
Martin LissOlder than dirtCommented:
Could you please attach the workbook that you were using when you got the error?
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
Here you are.  I ran the distribution and you will see on the Data worksheet, that the filter is only showing one FD.  Then take a look at FD2 and some of the others.  FD1 looks correct.
Martin LissOlder than dirtCommented:
When I un-filter the data on the DATA sheet and click the 'Distribute' button it runs successfully to completion, so there's probably something different about our environments, and so it will take some work for us to resolve this.

First, when the code stops at yellow line, hover your mouse over 'strSheet' in the line or in the previous line and tell me what it says. Also if you aren't familiar with debugging then please take a look at my article on the subject.
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
lngDestRow shows a message as follows: "lngDestRow=182".  Also, StrSheet shows "StrSheet=""
Martin LissOlder than dirtCommented:
I'm sorry I wasn't clearer. Here's what I'm looking for. In this picture my mouse was over the strSheet variable and it's telling me that in this case the value of the variable is "FD1".
 Cursor on strSheet
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
Yes. In mine, it shows the value as "".
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
StrSheet Value
Martin LissOlder than dirtCommented:
That's what I suspected but I don't understand why it is happening. Do the same thing with lngStartRow in the line above that and tell me the value. Also please tell me if at that point any of the data is visible on the DATA sheet, and if so how many rows.
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
I was in the process of reading your article and using the immediate window to discover the values of lngDestRow and lngStartRow.
lngDestRow is at 169 (data runs to line 171) and the lngStartRow is 180.
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
When I run the distribution, this is what my 'DATA" tab looks like.  It only shows FD11.  I have to click on the filter to get all of the data to regenerate (which is fine) but the district is only correct on the first tab, FD1.  Every one after that is incorrect.
Here is what is on the tabs after distributionData-screenshot.png:
FD1 = Correct.  Contains FD1 data only
FD2 = No data
FD3 = Incorrect. Contains FD11 data (lines 8 thru 13)
FD4 = No data (when you click on worksheet, the cursor is near the 3,989 line)
FD5 = Incorrect. Contains FD11 data (lines 8 thru 13)
FD6 = No Data
FD7 = Incorrect. Contains FD11 data (lines 8 thru 13) and cursor starts at line 3989
FD8 = No Data
FD9 = Incorrect. Contains FD11 data (lines 8 thru 13)
FD10 = Incorrect. Contains FD1 and FD10 data (lines 8 thru 17)
FD11 = Incorrect. Contains FD1, FD10 and FD11 data (lines 8 thru 180)
FD12 = Incorrect. Contains FD12 data only, but there were 8 accounts and it only picked up 6 of them (Lines 8 thru 13)
FD13 = No Data
FD14 = Incorrect. Contains FD11 data (lines 8 thru 13)
FD15 = Incorrect. Contains FD11 data (lines 8 thru 13) (Noticed the formula ran to line 19.  The others didn't do that.)
FD16 = No Data
FD17 = Incorrect. Contains FD11 data (lines 8 thru 13)
FD18 = No Data
FD19 = Contains FD11 data (lines 8 thru 13)
FD20 = Contains FD11 data (lines 8 thru 13)
FD21 = No Data
FD22 = Contains FD11 data (lines 8 thru 13)
FD23 = No Data
FD24 = Contains FD11 data (lines 8 thru 13)
FD25 = No Data
FD26 = Contains FD11 data (lines 8 thru 13)
FD27 = No Data
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
Here is the exact file I just used.  See if you see the same thing.
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
I will be leaving here for the day soon but will respond from my home computer over the weekend if you are still working. If not, next week is fine. I cannot thank you enough for all of the help. I'm sorry for the frustration. :(  You have been very patient with me and I appreciate it!
Martin LissOlder than dirtCommented:
I enjoy a challenge; at 77 if you don't use it you lose it very quickly.

I meant to say previously that when I used your Copy-of-29079003a.xlsm workbook it finished without any errors.

Thank you for reporting what you found. lngDestRow is the row in the FDn sheet where the DATA is copied to and it's not a part of the problem.

lngStartRow however is supposed to be the first visible row on the DATA sheet when it's filtered but I found that it's not and I believe that is what is causing your problem. Based on that I've made a number of changes and now that value is correct. I also made a couple of other minor changes including freezing row 1 of the DATA sheet so that it remains visible when you scroll.

The final thing is that when you look at one of the FDn sheets, make sure that it is scrolled to the top. I spent a bunch of time trying to understand why the FD10 sheet was empty when I was clearly sending data there, and well it turned out that the sheet was not scrolled all the way up! The data was there.

Oh, and the really final thing is that if you find my article useful, please feel free to click its 'thumbs up' button.
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
OMGosh! I’m so impressed! 77?! I’m fast approaching 55, but I totally understand the “use it or lose it” phase! I LOVE excel and work with it everyday, but macros have always been a challenge for me and I’ve had little time to truly get as educated as I need to. Hiwever, I have started a course and hope it gets me up to speed on them. But I won’t get past the learning curve fast enough to complete this project alone. This will help me with another project I’m working on as well though and I learn so much from you all! I will download this one tomorrow from my home computer and test it. Thanks so much, Martin! You give me hope that I will continue to work in Excel for many years to come!
Martin LissOlder than dirtCommented:
Thanks. Are you implying that my fix worked for you?
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
I haven’t downloaded it yet but will tomorrow morning and test it here at home. However, I’m already impressed with what you have done! I will message tomorrow after I test it again. Thanks so much!
Martin LissOlder than dirtCommented:
I may be getting ahead of myself but here's a documented  version of the Distribute sub.

Sub Distribute()
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngSaveCalc As Long
Dim lngDestRow As Long
Dim strSheet As String
Dim lngStartRow As Long
Dim rngVisible As Range

' Turn off the updating of the screen to save processing time
Application.ScreenUpdating = False

' Store the calculation method before we make it manual. This is done
' to save processing time in case the caluation mode is automatic.
' At the end of processing it will be set back to whatever it was.
lngSaveCalc = Application.Calculation
Application.Calculation = xlCalculationManual

With ActiveWorkbook.Worksheets("DATA")
    ' Find the last row of data
    lngLastRow = .Range("A1048576").End(xlUp).Row
     ' Sort the data. While not strictly required in preparation for
     ' filtering the data by district. I sort it because in the
     ' processing I create a range (rngVisible) that as you can probably
     ' surmise refers to the visible lines on the sheet, and if it is
     ' sorted, the range is contiguous and I can simple loop through
     ' it's contents "row" by "row" to look at its data. On the other
     ' hand, unsorted data might not be contiguous and instead would
     ' be stored in the range in separate sub-ranges called Areas, and
     ' to look at the data I would need to do a double loop which would
     ' loop through the areas, and then by row within each area. So,
     ' basically sorted data is easier to work with and the sort is quick.
     ' Clear any pre-existing sort parameters. BTW the code for the sort
     ' came from a recorded macro that I modified with the lngLastRow
     ' value to make it flexible.
    .Sort.SortFields.Add Key:=Range("E1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    With .Sort
        .SetRange Range("A2:J" & lngLastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With

    ' Filter and move the data, formatting and formulas
    lngStartRow = 2
    ' Loop until E2 is blank which would indicate that there's no more data.
    ' "Do" by itself would probably work as well because there is also code
    ' within the loop that ends the process.
    Do Until .Range("E2") = Empty
        ' Code for filtering. This is also based on a recorded macro.
        .Range("$E$1:$E$" & lngLastRow).AutoFilter Field:=1, Criteria1:=.Range("E" & lngStartRow + 1)
        ' Exit if there's no more data
        If .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count - 1 = 0 Then
            Exit Do
        End If
        ' Set up a range of visible cells that makes the data easier to work with
        Set rngVisible = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)
        ' Get the name of the desination sheet from the value in column "E"
        strSheet = .Cells(lngStartRow, "E")
        ' Find the last row on the destination sheet
        lngDestRow = Sheets(strSheet).Range("A1048576").End(xlUp).Row
        If lngDestRow > 6 Then
            ' lngDestRow is the first blank row
            lngDestRow = lngDestRow + 1
            ' There are 6 heading rows on the destination sheet so
            ' if the last line is 6 it contains no data and so we want to
            ' have a blank line in front of the new data
           lngDestRow = 8
        End If
        ' In this line lngStartRow is the row number of the first visible
        ' row and "rngVisible.End(xlDown).Row" is the row number of the last
        ' visible row, and we use those values to copy the rows to the
        ' destination sheet.
        .Range(.Cells(lngStartRow, "A"), .Cells(rngVisible.End(xlDown).Row, "J")).Copy Destination:=ThisWorkbook.Worksheets(strSheet).Cells(lngDestRow, "A")
        ' Copy templates for the destination formats and formulas. The
        ' foumulas could be done with code but this is much easier.
        With ThisWorkbook.Worksheets(strSheet)
            For lngRow = lngDestRow To .Cells(Rows.Count, 1).End(xlUp).Row
                .Cells(lngRow, "L").PasteSpecial xlPasteFormats
                .Cells(lngRow, "L").PasteSpecial xlPasteFormulas
            ' Cause the formulas to fire
        End With
        ' Increment the data starting row by adding to it the number
        ' of currently visible rows
        lngStartRow = lngStartRow + .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count - 1
End With
' Restore the calculation mode
Application.Calculation = lngSaveCalc
' Restore screen updating. This isn't actually required
' since exiting a Sub will do that
Application.ScreenUpdating = True

MsgBox "Distribution completed", vbOKOnly, "Fire Districts"
End Sub

Open in new window

Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
Hi Martin!
So I downloaded it and ran it and I think we are ALMOST there!  It cleared properly and then when I distributed, FD1 was correct, but all other sheets only picked up one record for each FD, but it was the correct info.  It just didn't pick up all of the records.  Otherwise, all looks good.  So its apparently doing something correctly in picking up data for tab FD1 that isn't happening for the other FD's.
Martin LissOlder than dirtCommented:
I can't reproduce that behavior as shown in this picture of my results in (as an example) FD3.
Microsoft_Excel.jpgPlease attach your workbook after getting your results. If I can't reproduce the problem in your workbook then I'll rewrite the code using a different, slower, method.
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
I have attached it so you can review it. :)
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
Oops...Sorry. Here it is.
Martin LissOlder than dirtCommented:
That is freakishly strange; I get correct results. Anyway be back soon.
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
Ok.  Thanks again! I am on my Windows machine at home and using Excel 2013 (if it matters).
Martin LissOlder than dirtCommented:
Keeping my fingers crossed...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
It is working!!! You are THE bomb!!  Thanks SOOOO much, Martin!  Thanks especially for your patience with me!  I will go back to your article and give it the thumbs up and submit this as the solution.  :)  Have a great weekend!
Johnette ConnelleyBudget Analyst / ProjectsAuthor Commented:
I cannot thank you enough for your patience and expertise in working through this macro for me. It will save us a lot of time and I have learned so much from you during this process.  Thanks so much!
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some other articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.