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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,639
Messages
5,660,016
Members
418,542
Latest member
Sandy3503

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top