Solved: Get data from sheet

[gussi]

Hi guys,
I?m having problem with date & time.

I have data sequence in Sheet2.
I?m trying to code a button to get certain date into new sheet. The button is in Sheet1, data in Sheet2 and I need to copy this data from Sheet2 to Sheet3.

Beside the button i have a combo box where i can choose different dates.

I made a Combo box from Forms and in Format Control i?ve chosen all the dates that are available and Cell Link is just a cell in Sheet4.

So this is the problem, i choose a date in my combo box and click the button. Then i have to find a solution so my code will go to sheet2 and get all sequences from column A:N which have this date in Column J.

Can somebody help me?

Regards,
Gussi

[Bob Phillips]

[vba]


Dim rng As Range

With Worksheets("Sheet2")
Set rng = .Columns("A:N")
rng.AutoFilter field:=10, Criteria1:=Format(Worksheets("Sheet4").Range("A1").Value, .Range("J2").NumberFormat)
Range("A:N").SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet3").Range("A1")
rng.AutoFilter
End With
[/vba]

[gussi]

Hi xld,

Thanks for your time.

This dosen?t work, what this does is it takes sheet1 and copies the look of my subject.

I can try to explain a little better.

I have a sequence of handmade dates in Column G in sheet4. When i made the combo box I right clicked on it, format control, control, input range = ..Handmate sequences of dates.. and in Cell link = Cells(2,8)

Now it is available for me to choose date in the combo box.

I choose, lets say 04.08.07 (4th august 2007)

Then I click on a button which i?m trying to code.

So when i click the button, it must know what date is in the combo box. So it can go to sheet2 and copy all the data with this date and put it into another sheet, sheet3.

The date Column which i have to go through in sheet2 is J. And i need to copy all rows with this date from column A:N

Regards, Gussi

[gussi]

Hi xld,

Thanks for your time.

This dosen?t work, what this does is it takes sheet1 and copies the look of my subject.

I can try to explain a little better.

I have a sequence of handmade dates in Column G in sheet4. When i made the combo box I right clicked on it, format control, control, input range = ..Handmate sequences of dates.. and in Cell link = Cells(2,8)

Now it is available for me to choose date in the combo box.

I choose, lets say 04.08.07 (4th august 2007)

Then I click on a button which i?m trying to code.

So when i click the button, it must know what date is in the combo box. So it can go to sheet2 and copy all the data with this date and put it into another sheet, sheet3.

The date Column which i have to go through in sheet2 is J. And i need to copy all rows with this date from column A:N

Regards, Gussi

[Bob Phillips]

My code was only an example. It would need to be adapted to the actual cells.

Post the workbook.

[gussi]

Ok, here it is.

I made an short example from my workbook

So when i choose let?s say 26.6.2007 in my combo box, i?m trying to code behind the button so when i click on that button i go to sheet2 copy all rows with 26.6.2007 into sheet3

I?m doing this for a large collection of data so I would like to get some help with the code, not formula.

If you find a solution, can you paste the code in a reply because i?m having trouble with saving files

[gussi]

Does nobody have a solution to this?

[Bob Phillips]

Two things.

We are volunteers here, we don't get paid. Patienec is the least of theattributes that you should exhibit.

You din't mention it was dates and times

[vba]

Sub ExtractData()
Dim rng As Range
Dim testDate As Long
Dim numRows As Long

With Worksheets("Sheet4")
testDate = .Cells(.Range("H2").Value + 1, "G").Value
End With

With Worksheets("Sheet2")
numRows = Application.Count(.Columns("J:J"))
.Columns("K:K").Insert
.Rows(1).Insert
.Range("K2").Resize(numRows).Formula = "=INT(RC[-1])"
Set rng = .Columns("A:O")
.Range("K2").Resize(numRows).NumberFormat = "General"
rng.AutoFilter field:=11, Criteria1:=testDate
Range("A:N").SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet3").Range("A1")
.Rows(1).Delete
.Columns("K:K").Delete
End With
End Sub

[/vba]

[gussi]

Thx xld, I will try this out..

I?m sorry about how impatient I am.

Thanks for your time and big respect.

[gussi]

Hi xld, i?m having trouble with this.

It works fine until
[VBA]
Range("A:N").SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet3").Range("A1")
[/VBA]

When i go through this line it paste's the look from sheet1, so i dont get no rows i only get the colour of columns in sheet1 and the combo boxes.

Did it work for you, when you pick out a date in the combo box, pressed the button were you then avaible to get these rows with this certain date in sheet3

Regards,
Gussi

[Bob Phillips]

That line should be

[vba]

.Range("A:N").SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet3").Range("A1")
[/vba]

It works if you have the right (wrong?) sheet active, but not if you drive it from the button.

[Charlize]

For me it works with the button. But column K needs to be formatted as a date or you'll see the number of the date.

[gussi]

Hi guys,

This is just not working, If I choose some date in the Combo box and press the button this dosen't work.

Also if i go through the code in vba and choose some date, the value changes ([VBA]testDate = .Cells(.Range("H2").Value + 1, "G").Value[/VBA]) and this line should be reading that value to know what date it should get from the autofilter.

And when i drive the code through it only copies the first line(from sheet2) to sheet3.

Got the same problem?

Regards, Gussi

[Bob Phillips]

As it works fine for me, when corrected as shown in post #11, I guess the answer is no.

[gussi]

I?ve changed the Code to:

[VBA] Dim rng As Range
Dim testDate As Long
Dim numRows As Long

With Worksheets("Sheet4")
testDate = .Cells(.Range("H2").Value + 1, "G").Value
End With

With Worksheets("Sheet2")
numRows = Application.Count(.Columns("J:J"))

Set rng = .Columns("A:N")

.Range("J1").Resize(numRows).NumberFormat = "m/d/yyyy"
rng.AutoFilter field:=11, Criteria1:=testDate
.Range("A:N").SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet3").Range("A1")

End With[/VBA]

This should work?

My Value for the combo box is in Cells(2, 8) and the testdates are in Cells("G") in sheet4 isn?t that the same as in the .xls i sent you..

Sorry about this, i?m new and i?m not finding out where the problem is..

Regards, Gussi

[Charlize]

put this in the commandbutton_Click event[vba]Dim rng As Range
Dim testDate As Long
Dim numRows As Long

With Worksheets("Sheet4")
'Itemno in combobox + 1 to get correct value
'of date in testdate
'(if you select item 1 in combobox,
'you really need the datevalue of row 2
'because you start of in row 2 with the dates)
testDate = .Cells(.Range("H2").Value + 1, "G").Value
End With
'has all the data
With Worksheets("Sheet2")
numRows = Application.Count(.Columns("J:J"))
'we need the datevalue apart from the timevalue
.Columns("K:K").Insert
.Rows(1).Insert
.Range("K2").Resize(numRows).Formula = "=INT(RC[-1])"
Set rng = .Columns("A:O")
'autofilter on numbervalue of date
.Range("K2").Resize(numRows).NumberFormat = "General"
rng.AutoFilter field:=11, Criteria1:=testDate
'all filtered cells are copied to sheet3
.Range("A:O").SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet3").Range("A1")
Worksheets("Sheet3").Columns("K:K").Delete
'remove autofilterline
.Rows(1).Delete
'remove extracted date
.Columns("K:K").Delete
End With[/vba]Correct me if my notes aren't completely correct.

[Bob Phillips]

I?ve changed the Code to:

[VBA] Dim rng As Range
Dim testDate As Long
Dim numRows As Long

With Worksheets("Sheet4")
testDate = .Cells(.Range("H2").Value + 1, "G").Value
End With

With Worksheets("Sheet2")
numRows = Application.Count(.Columns("J:J"))

Set rng = .Columns("A:N")

.Range("J1").Resize(numRows).NumberFormat = "m/d/yyyy"
rng.AutoFilter field:=11, Criteria1:=testDate
.Range("A:N").SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet3").Range("A1")

End With[/VBA]

This should work?

My Value for the combo box is in Cells(2, 8) and the testdates are in Cells("G") in sheet4 isn?t that the same as in the .xls i sent you..

Sorry about this, i?m new and i?m not finding out where the problem is..

Regards, Gussi

Filtering dates is prblemmatic in VBA, that is why I did it the way that I did.

[gussi]

thx guys,

I figured out one stupid problem with your help.

Thanks alot for time, keep it up.

Regards,
Gussi

[Bob Phillips]

And that was?

[gussi]

The Field was wrong when i change the code