Copy and Paste

[longytravel]

Morning,

I'm new to all this VBA stuff! Very interesting!

I am wondering if someone can help?

I have data that is on a 'Data' tab and a 'Reports' tab

I would like to be able to run reports based on data in the 'data' tab that will get updated weekly

Can someone please help me with the code to do search based on date and then pull all the relevant rows accross to the 'reports' tab?

So, if i want all rows between 29/07/2011 and 01/08/2011 i can search using an input box and then move all the information over?

The 'report' will then be bordered and sorted alphabettically?

I've been pulling my hair out so any help greatly received

Thanks

 

[Weaver]

Before you go down this route, have you explored the possibilities offered by filtering, including the advanced filter?

 

[longytravel]

Thanks Weaver - fair comments - it is a long and slow process for me

The best way to learn is to certainly do the research and come up with the solutions yourself

However, this is for a piece of work i am doing to help a team out so a 'quick win' would be great for me and then i can build my learning around that

I have tried plenty of things in an attempt to get it sorted but non of them have quite worked - i cant really spend anymore time on this one problem so if someone can give me something to use i would be grateful

THanks

 

[Weaver]

Okay, so what do you have so far?

I'm still thinking a solution based on filtering is the way to go.

 

[longytravel]

Great! Thanks!

Would it be easier to attach my book? I've had a look but struggling to find out how!

 

[Weaver]

Probably best to just paste your code, following the posting guidelines, of course.

As for picking the ranges, I'd go with a form. If you can design a form with 2 date pickers, a run & a cancel button and a few labels, it will look much more professional than using input boxes.

You might find this useful

http://www.contextures.com/xluserform01.html

 

[Weaver]

Here's an example of code where the dates are in column E

Sub Macro1()
    With Sheets("data")
        lr = .Cells(Rows.Count, "E").End(xlUp).Row
        With .Range("E1")
            .AutoFilter
            .AutoFilter Field:=5, Criteria1:=">=01/01/2010", Operator:=xlAnd _
            , Criteria2:="<=01/01/2011"
        End With
        .Range("E1:E" & lr).EntireRow.Copy Sheets("reports").Range("A1")
    End With
End Sub

This will copy anything on the data tab where the date in column E is greater than or equal to 01/01/2010 and less than or equal to 01/01/2011.

Hopefully you'll be able to see where you can substitute user input for the hard coded dates in the example, whether you're using input boxes or a date picker.

 

[longytravel]

THanks Weaver

That code seems to remove all the detail in the sheet

Here is where i think I have been most successful - close ish

Again, I took this from another site/post somewhere

Effecticly, my 'data' runs from A2 - I2 and then the rows will be added each week - the date colum is B

Happy for the cut and pasted rows to go anywhere on the reports tab i guess B3 ish would be good

Cheers

Sub Button1_Click()
Dim strLastRow As String
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet
Dim rngtest As String
Application.ScreenUpdating = False
Set wSht = Worksheets("Data")
strToFind = InputBox("Enter Search Criteria")
With wSht.Range("C:C")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
strLastRow = Sheets("Reports").Range("C" & Rows.Count).End(xlUp).Row + 1
rngC.EntireRow.Copy Sheets("Reports").Cells(strLastRow, 1)
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With
MsgBox ("Finished")
 
End Sub

 

[Weaver]

I didn't say that code would work unmodified.

Sub Macro1()
    date1 = "01 jan 2010"
    date2 = "01 jan 2011"
    With Sheets("data")
        lr = .Cells(Rows.Count, "B").End(xlUp).Row
        With .Range("B1")
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:=">=" & date1, Operator:=xlAnd _
            , Criteria2:="<=" & date2
        End With
        .Range("B1:B" & lr).EntireRow.Copy Sheets("reports").Range("A1")
    End With
End Sub

Note, I'll leave it to you to substitute the correct dates

 

[longytravel]

Its certainly easy when you know how!

Thank you.

What is the value in the autofilter? I guess i dont need it to autofilter the 'data' page after it has copied it - can that element be removed?

Love the way it carries over the titles with it

If i design a user form for selecting the two dates two dates how would that be written in? Should i be using two text boxes for the dates to be added?

Cheers

M.