Nearly there!

[longytravel]

I have the following code
I would really like it to paste into 'B2' on the 'reports' tab
I have changed the 'A1' bit to 'B2' but without success!

Any ideas would be great!

Cheers!

Private Sub CommandButton1_Click()
    Date1 = Me.Date10.Text
    Date2 = Me.Date20.Text
    With Sheets("data")
        lr = .Cells(Rows.Count, "B").End(xlUp).Row
        With .Range("B1")
        End With
        .Range("B1:B" & lr).EntireRow.Copy Sheets("reports").Range("A1")
    End With
End Sub
Private Sub Date10_AfterUpdate()
If Not IsDate(Me.Date10.Value) Then
    Me.Date10.Text = ""
    MsgBox "please type a valid date!"
    Exit Sub
End If
End Sub
Private Sub Date20_AfterUpdate()
If Not IsDate(Me.Date20.Value) Then
    Me.Date20.Text = ""
    MsgBox "please type a valid date!"
    Exit Sub
End If
End Sub

 

[daverunt]

Step through the code with a change here and the relevant sheet in view.

.Range("B1:B" & lr).EntireRow.Copy Sheets("reports").Range("A1")

to .Range("B1:B" & lr).EntireRow.Select

What is the top left cell of the selected range?

 

[longytravel]

I would like it to cut from A1.
Row A is the where the headers are
The detail follows from row B down
How would io get your code into mine - just remove and replace with yours?

Thanks for looking at this for me

 

[daverunt]

Sorry I wasn't clear.

I wanted you to temporarily replace the line of your code

.Range("B1:B" & lr).EntireRow.Copy Sheets("reports").Range("A1")

for the line of my code

.Range("B1:B" & lr).EntireRow.Select

and for you to step through the code in the VBEditor using F8 and see what the top left cell of the selected range is when you get to that line of code.

I suspected it might be "A1", which you just confirmed

If it is you are trying to Copy an EntireRow from column A to ZZZ (or whatever Excels last column is) and trying to then paste that into Column B to ZZZ - so it won't fit.

You maybe need to look at copying the range to the last filled column on the right rather than use EntireRow.

 

[longytravel]

Thank you - that sounds like something else i don't understand!

It was only from a aesthetic point of view

While i have you could you look at something that is more important in terms of functionality

From the code you will see i am trying to use a userform to search for info between two dates. That functionality doesn't work. When it pastes it over it just pastes all entries

Any ideas?

Also, i also want to paste over data based on the 'project name'. The user selects a project from the list of projects via combobox . I would like it to paste over all entries that have been inputted in relation to that certain project.

I guess a filter is the sensible way to go but i think is just looks slicker and i am enjoying learning about VBA doing it this way

Any code for either or both to help would be amazing!

Cheers

Private Sub CommandButton1_Click()
    Date1 = Me.Date10.Text
    Date2 = Me.Date20.Text
    With Sheets("data")
        lr = .Cells(Rows.Count, "B").End(xlUp).Row
        With .Range("B1")
        End With
        .Range("B1:B" & lr).EntireRow.Copy Sheets("reports").Range("A1")
    End With
End Sub
Private Sub CommandButton3_Click()
    ComboBox1 = Me.ComboBox1.Value
    With Sheets("data")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        With .Range("A1")
        End With
        .Range("A1:A" & lr).EntireRow.Copy Sheets("reports").Range("A1")
    End With
End Sub
Private Sub Date10_AfterUpdate()
If Not IsDate(Me.Date10.Value) Then
    Me.Date10.Text = ""
    MsgBox "please type a valid date!"
    Exit Sub
End If
End Sub
Private Sub Date20_AfterUpdate()
If Not IsDate(Me.Date20.Value) Then
    Me.Date20.Text = ""
    MsgBox "please type a valid date!"
    Exit Sub
End If
End Sub

 

[daverunt]

Hi,

I'm afraid i can't help with ComboBox code never used it.
Perhaps someone else will jump in.

Try this code for the previous problem.

It copies from A1 down to the last row in B and last column to the right and pastes it in B2 next sheet.

With Sheets("data")
         lastCol = .Range("A1").End(xlToRight).Column
         Lr = .Cells(Rows.Count, "B").End(xlUp).Row
        .Range("A1:A" & Lr, .Cells(Lr, lastCol)).Copy Sheets("reports").Range("B2")
    End With

 

[longytravel]

Wow!!

Great stuff. THanks

Be great if someone can help me with the date funcionality and my combo box problem!

 

[daverunt]

Personally I would re-post it as a new thread so it more likely to be looked at.
Be more specific about what you expect the code to do and what it is doing as well as where it fails. Because reading the last post I don't understand what you are trying to achieve.