Filter on date and paste to value destination

Closed
Jan - Apr 23, 2010 at 11:51 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 28, 2010 at 12:33 PM
Help please, I have a spreadsheet with rows of data by date and category.
Date being in column c and categories in column v. I have code that currently filters on date and pastes into the desination spreadsheet just by selecting the last row of the destination spreadsheet. But...........What I need it to do is to find the matching category on the destination spreadsheet and paste the values under that category.
Here is my code. I was thinking vlookup or match somehow. I was using another spreadsheet that found the row number of the category and then counted all the rows that were being used in the category to find the last row to paste into the category but I am not sure if that would be of any help or if there might just be a simpler way. But here is what I have:

Sub Copybydate()

'Update categories by check date
Dim DestSheet As Worksheet
Dim NewRow As Integer

Set DestSheet = Worksheets("Itemized Costs2")
NewRow = Worksheets("Itemized Costs2").Range("C65536").End(xlUp).Row


Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim MyArr As Variant


'Dim strsearch As String

MyArr = Array(UserForm2.ComboBox1.Value)


For I = LBound(MyArr) To UBound(MyArr)

For sRow = 1 To Range("C65536").End(xlUp).Row
If Cells(sRow, "C") Like MyArr(I) Then
dRow = NewRow + 1
NewRow = NewRow + 1

DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "C")
DestSheet.Cells(dRow, "D") = Cells(sRow, "D")
DestSheet.Cells(dRow, "E") = Cells(sRow, "E")
DestSheet.Cells(dRow, "F") = Cells(sRow, "F")
DestSheet.Cells(dRow, "G") = Cells(sRow, "G")
DestSheet.Cells(dRow, "H") = Cells(sRow, "H")
DestSheet.Cells(dRow, "I") = Cells(sRow, "I")
DestSheet.Cells(dRow, "J") = Cells(sRow, "J")
DestSheet.Cells(dRow, "K") = Cells(sRow, "K")
DestSheet.Cells(dRow, "L") = Cells(sRow, "L")

SumL = SumL + Cells(sRow, "L")

End If
Next sRow
Next I
End Sub


Related:

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 23, 2010 at 01:03 PM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
0
I tried upload a picture but it was too large.
The categories spreadsheet is basically a list of categories with the data underneath containing check date, check number, vendor name, invoice date and amount.
Like so:
#201 Sheetrock
4/15/10 1234 ABC Vending 10410 $3,000
4/16/10 1235 SomeCo 243999 $100
#202 Permits
3/9/10 1239 Permits Inc 48846 $2500
#203 Light Fixtures
4/25/10 1243 LightsRUs 595752 $600

The data sheet that it comes from is in this format:
Check date checknumer Vendor invoice number Amount Categorie#
4/15/10 1234 ABC Vending 10410 $3,000 201
4/16/10 1235 SomeCo 243999 $100 201
3/9/10 1239 Permits Inc 48846 $2500 202
4/25/10 1243 LightsRUs 595752 $600 203

I want to be able to select a check date by a user form and then have it put the data into the category it belongs under in another spreadsheet.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 28, 2010 at 12:33 PM
How big of a file is this ? Try to zip and then upload and then post the link.
0