help on VB code to move data from one sheet to another

[johnandbeth]

I tried the solution in the posting "VB Code to move data from one sheet to another" posted 4-16-02.
It works except I need it to look for a date range. Any suggestions?

Instead of the word "complete", I tried to enter "01/**/**" and I tried "01/??/??" hoping one of those wildcards would work, but it didn't.

I'm trying to copy data from "2002 Data" worksheet to the "January" worksheet.

[kkknie]

I've never used the particular function in the post from 4/16, but I believe you can use the left function in some way.

Left(cell,3) = "01/"

I'd have done the whole thing differently, so this may not apply...

K

[johnandbeth]

So, instead of typing ("NOT(ISERROR(SEARCH(""COMPLETE""," & usedcell.Address & ",1)))") as it shows in the 4/16/02 post you would suggest that I type ("NOT(ISERROR(SEARCH(""Left(cell,3) = "01/"
""," & usedcell.Address & ",1)))") ?

I'm pretty new to VB, so I'm sorry if I seem like an idiot.

If this isn't what you mean, can you explain it simply to me?

Also, you said you would have done it completely differently. I'm not locked to this way of doing it, so if you have any other way of reaching the same solution, I'd love to hear it.

And thank you for your help!!

[kkknie]

Maybe...?

I am really not familliar with the method used, so I cannot tell.

I would have done it using a simple loop through the row using the cells array. Post some more information on your exact problem and I will try to work it out over lunch.

K

[johnandbeth]

Thank you for the wonderful offer.

I have a workbook that has 13 worksheets. The first one is the data entry sheet and is titled "2002 Data", then there are 12 worksheets, each with a month title (January, February, etc).

What I want is a macro/vbcode that will copy the data in the "2002 Data" worksheet to each month, to the last row on each monthly worksheet. So if the info came in about a problem in January (even though the call came in April), it would copy that January problem to the January worksheet.
Does this make sense?

The user is very scared of the computer, so the more I can automate the better.

[kkknie]

Questions:

1. Does any new data get entered on the monthly sheets or are they a direct copy of the yearly one?

2. Do you want the monhtly's sorted in any way?

3. About how many rows would be on the yearly sheet by the end of the year?

K

[johnandbeth]

Answers:

1. No, just entered on the yearly sheet. (Does any new data get entered on the monthly sheets or are they a direct copy of the yearly one?)

2. Yes, by the "u/w" field - this is the name of the person entering the info.
(Do you want the monhtly's sorted in any way?)

3. Probably 300-500 rows.
(About how many rows would be on the yearly sheet by the end of the year?)

[kkknie]

I like to call this the "Brute Force" method...

Basically, I delete all of the monthly sheets, copy the main sheet 12 times and rename for months, sift through the month sheets and delete anything not that month.

I ran it on a P3 500 MHz (256MB RAM) machine with 600 rows and it took 19 seconds to complete. I am working on speeding it up, but may not be able to finish (1pm meeting).

It has the following assumptions:

First sheet name is MAIN (yearly data)
Data starts in row 2 (headers in row 1)
Date is in first column
Workbook always has 13 sheets

Questions? I'll be gone from 1pm-3pm EDT.

Sub Move_Months()

Dim i As Long
Dim j As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To 13
Sheets(2).Delete
Next

Sheets("MAIN").Select
Sheets("MAIN").Copy After:=Sheets(1)
Sheets(2).Name = "JAN"
Sheets(2).Copy After:=Sheets(2)
Sheets(3).Name = "FEB"
Sheets(3).Copy After:=Sheets(3)
Sheets(4).Name = "MAR"
Sheets(4).Copy After:=Sheets(4)
Sheets(5).Name = "APR"
Sheets(5).Copy After:=Sheets(5)
Sheets(6).Name = "MAY"
Sheets(6).Copy After:=Sheets(6)
Sheets(7).Name = "JUN"
Sheets(7).Copy After:=Sheets(7)
Sheets(8).Name = "JUL"
Sheets(8).Copy After:=Sheets(8)
Sheets(9).Name = "AUG"
Sheets(9).Copy After:=Sheets(9)
Sheets(10).Name = "SEP"
Sheets(10).Copy After:=Sheets(10)
Sheets(11).Name = "OCT"
Sheets(11).Copy After:=Sheets(11)
Sheets(12).Name = "NOV"
Sheets(12).Copy After:=Sheets(12)
Sheets(13).Name = "DEC"

For i = 2 To 13
Sheets(i).Select
Columns("A:K").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
Next

For i = 2 To 13
Sheets(i).Select
iFound = 0
For j = 2 To 65000
If Cells(j, 1).Value = "" Then Exit For
If Month(Cells(j, 1)) <> i - 1 Then
Cells(j, 1).Select
Selection.EntireRow.Delete
j = j - 1
End If
Next
Next i


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

P.S. Try it out on a temporary worksheet since it does do a lot of deletions...

[ This Message was edited by: kkknie on 2002-05-15 09:40 ]

[johnandbeth]

It didn't really work for me. I know there are more than 4 January entries, but it only copied over 4 total entries to the January worksheet, and one of those was a March entry.