conditionally copy date from one spreadsheet to another

anjali5

New Member
Joined
Aug 5, 2010
Messages
3
hello Everyone,

I need to conditionally copy from one excel worksheet to another. The condition is date and item number so I have two sheets, sheet1 and sheet2. In sheet1, I have dates listed like
Sheet1
7654
08/01/2010 12
08/02/2010
08/03/2010

sheet2 has

dates
7654
1
2
3

if 08/01/2010 matched with 1 and 7654 in sheet1 matches with 7654 in sheet2, I want the number 12 to be displayed right next to 1 in sheet2
I have the whole months date in my sheet1 and numbers are 7654, 7655, 7688, 14 numbers are displayed like this.

Can anyone help me with this.

Thanks
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

anjali5

New Member
Joined
Aug 5, 2010
Messages
3
I kind of figured out what needs to be done, but i am getting one error, the error is
Application-defined or object-defined error

below is my code. I highlighted the line in red where the error is occuring

Private Sub CommandButton1_Click()
Dim ldate As String
Dim lFound As Boolean
Dim lQueue As String
Dim lColumn As Integer
Dim lQueueColumn As Integer
Dim lQueueRow As Integer

Dim lpastedColumn As Integer
Dim lPastedRow As Integer
Dim PastedSheetDateRow As Integer
Dim pastedShetdateColumn As Integer
Dim pastedQueueColumn As Integer
Dim pastedQueueRow As Integer

Dim rangeRow As Integer
Dim rangeColumn As Integer



On Error GoTo Err_Execute



ldate = Sheets("Data Entry Sheet ").Range("A7")

lQueue = Sheets("Data Entry Sheet ").Range("W3")
lQueueColumn = 3
lQueueRow = 11
lpastedColumn = 23
lFound = False
PastedSheetDateRow = 14
pastedShetdateColumn = 1
pastedQueueColumn = 23
pastedQueueRow = 3
rangeRow = 14
lPastedRow = 7
Sheets("Data Entry Sheet ").Activate
Worksheets("Data Entry Sheet ").Activate
While lFound = False
If ldate = Day(Cells(PastedSheetDateRow, pastedShetdateColumn)) Then
If lQueue = Cells(lQueueRow, lQueueColumn) Then
Sheets("Sheet1").Select
Range("F" & rangeRow & ":Y" & rangeRow).Select
Selection.Copy

'Paste on to Data Entry Sheet

Sheets("Data Entry Sheet ").Select
' Range(lPastedRow, lpastedColumn).Select
Range(28, 26).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

lFound = True
MsgBox "The data has been successfully copied."
Else
lQueueRow = lQueueRow + 8
rangeRow = rangeRow + 8

End If
Else 'continue searching
lQueueRow = 11
lpastedColumn = lpastedColumn + 1
PastedSheetDateRow = PastedSheetDateRow + 1
pastedQueueColumn = pastedQueueColumn + 6

End If






Wend

Err_Execute:
MsgBox "An error occurred." & Err.Description & Err.Number




End Sub
 

fedds2

New Member
Joined
Dec 30, 2005
Messages
32
Gday Anjali5,
I think your problem is that after the activate line you need the following to actually use that sheet:
With Sheets("name of your sheet")
your actions

End With

cheers,
Fedds2
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,943
Messages
5,508,270
Members
408,673
Latest member
CELER_

This Week's Hot Topics

Top