Problem with code
 

Hi. Hopefully someone can help me out here. Have a file with 3 sheets. Sheet
1 (In-Store) is where daily information is inputted. At the end of the night
I need to be able to click on "Run Sales Book" and have information from
sheet 1 transferred over to sheets 2 (Y.T.D.) and 3 (Payroll) based on the
date. The first part works. it transfers my data over onto the Y.T.D. sheet
without a problem. The payroll sheet is causing me a headache. It is not
transferring over the information at all. It's not even telling me any
errors. Now the first part searches for a date and inputs the data I need
over on the same row-different column. The payroll one searches for a date
and inputs the data I need below the date 2 rows down (same column-different
row). What am I doing wrong? Below is what I have.


Sub Run_Sales_Book()
' Entering info Y.T.D., Payroll, WIR

Sheets("In-Store").Select
Range("C4").Select

Dim dateSearch As Date
On Error Resume Next


' Check for Correct Date

checkdate = MsgBox("Is the correct date entered in your DSR?", vbYesNo,
C4)

If checkdate = 7 Then
MsgBox ("Enter the Correct Date (e.g. Christmas = 12/25/01)")
Exit Sub
Else
dateSearch = Sheets("In-Store").Range("C4")


Sheets("Y.T.D.").Select
Range("H6").Select
Range(ActiveCell, "H421").Select
Selection.Find(dateSearch).Select
If Selection.Find(dateSearch) = False Then
Range("A1").Select
MsgBox ("Could not find that Date. Ensure that the date is
entered in the proper format (e.g. 12/25/01 = Christmas)")
Exit Sub
End If
End If


' Entering Sales by Shift

' Days
ActiveCell.Offset(0, 2).Select
ActiveCell.Formula = Sheets("In-Store").Range("C11")

' Nights
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = Sheets("In-Store").Range("D11")

'Entering Payroll Information

Sheets("Payroll").Select
Range("C12").Select
Range(ActiveCell, "AF12").Select
Selection.Find(dateSearch).Select
If Selection.Find(dateSearch) = False Then
Range("A1").Select
MsgBox ("Could not find that Date. Ensure that the date is
entered in the proper format (e.g. 12/25/01 = Christmas)")
Exit Sub
End If

' Payrol
ActiveCell.SmallScroll Down:=2
ActiveCell.Formula = Sheets("In-Store").Range("AB30:aB50")

Problem with code
 

hi.
your post is contradictory. first you say it's not transfering to payroll at
all then say that it does tranfer the data only 2 rows down. (wrong place??)
which is it?

the y.t.d code seems near identical to the payroll except for the selects.
one thing i did notice that seemed odd.
under y.t.d, you used.........ActiveCell.Offset(0, 2).Select
under payroll, you used......ActiveCell.SmallScroll Down:=2
why did you use the small scroll command. shouldn't it be the offset command?
i'm guessing but change that and see if it doesn't fix you payroll sheet.

Regards
FSt1

"JeffJ" wrote:

Hi. Hopefully someone can help me out here. Have a file with 3 sheets. Sheet
1 (In-Store) is where daily information is inputted. At the end of the night
I need to be able to click on "Run Sales Book" and have information from
sheet 1 transferred over to sheets 2 (Y.T.D.) and 3 (Payroll) based on the
date. The first part works. it transfers my data over onto the Y.T.D. sheet
without a problem. The payroll sheet is causing me a headache. It is not
transferring over the information at all. It's not even telling me any
errors. Now the first part searches for a date and inputs the data I need
over on the same row-different column. The payroll one searches for a date
and inputs the data I need below the date 2 rows down (same column-different
row). What am I doing wrong? Below is what I have.


Sub Run_Sales_Book()
' Entering info Y.T.D., Payroll, WIR

Sheets("In-Store").Select
Range("C4").Select

Dim dateSearch As Date
On Error Resume Next


' Check for Correct Date

checkdate = MsgBox("Is the correct date entered in your DSR?", vbYesNo,
C4)

If checkdate = 7 Then
MsgBox ("Enter the Correct Date (e.g. Christmas = 12/25/01)")
Exit Sub
Else
dateSearch = Sheets("In-Store").Range("C4")


Sheets("Y.T.D.").Select
Range("H6").Select
Range(ActiveCell, "H421").Select
Selection.Find(dateSearch).Select
If Selection.Find(dateSearch) = False Then
Range("A1").Select
MsgBox ("Could not find that Date. Ensure that the date is
entered in the proper format (e.g. 12/25/01 = Christmas)")
Exit Sub
End If
End If


' Entering Sales by Shift

' Days
ActiveCell.Offset(0, 2).Select
ActiveCell.Formula = Sheets("In-Store").Range("C11")

' Nights
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = Sheets("In-Store").Range("D11")

'Entering Payroll Information

Sheets("Payroll").Select
Range("C12").Select
Range(ActiveCell, "AF12").Select
Selection.Find(dateSearch).Select
If Selection.Find(dateSearch) = False Then
Range("A1").Select
MsgBox ("Could not find that Date. Ensure that the date is
entered in the proper format (e.g. 12/25/01 = Christmas)")
Exit Sub
End If

' Payrol
ActiveCell.SmallScroll Down:=2
ActiveCell.Formula = Sheets("In-Store").Range("AB30:aB50")

Problem with code
 

Jeff, if you are trying to move data from Column AB, Range("AB30:AB50") of
Sheet("In-Store") to Row 12, Range("C12:AF12"), I believe you will have to
use the Copy PasteSpecial with Transpose method.

ActiveCell.Formula = Sheets("In-Store").Range("AB30:AB50") won't do that.
Also,
Fst1's comment about the scroll down is right. It doesn't move the active
cell selection, it only alters the view of the screen.

If you could be a little more specific about what you want to move where, I
am sure someone in this newsgroup can give you some useable code.

"JeffJ" wrote:

Hi. Hopefully someone can help me out here. Have a file with 3 sheets. Sheet
1 (In-Store) is where daily information is inputted. At the end of the night
I need to be able to click on "Run Sales Book" and have information from
sheet 1 transferred over to sheets 2 (Y.T.D.) and 3 (Payroll) based on the
date. The first part works. it transfers my data over onto the Y.T.D. sheet
without a problem. The payroll sheet is causing me a headache. It is not
transferring over the information at all. It's not even telling me any
errors. Now the first part searches for a date and inputs the data I need
over on the same row-different column. The payroll one searches for a date
and inputs the data I need below the date 2 rows down (same column-different
row). What am I doing wrong? Below is what I have.


Sub Run_Sales_Book()
' Entering info Y.T.D., Payroll, WIR

Sheets("In-Store").Select
Range("C4").Select

Dim dateSearch As Date
On Error Resume Next


' Check for Correct Date

checkdate = MsgBox("Is the correct date entered in your DSR?", vbYesNo,
C4)

If checkdate = 7 Then
MsgBox ("Enter the Correct Date (e.g. Christmas = 12/25/01)")
Exit Sub
Else
dateSearch = Sheets("In-Store").Range("C4")


Sheets("Y.T.D.").Select
Range("H6").Select
Range(ActiveCell, "H421").Select
Selection.Find(dateSearch).Select
If Selection.Find(dateSearch) = False Then
Range("A1").Select
MsgBox ("Could not find that Date. Ensure that the date is
entered in the proper format (e.g. 12/25/01 = Christmas)")
Exit Sub
End If
End If


' Entering Sales by Shift

' Days
ActiveCell.Offset(0, 2).Select
ActiveCell.Formula = Sheets("In-Store").Range("C11")

' Nights
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = Sheets("In-Store").Range("D11")

'Entering Payroll Information

Sheets("Payroll").Select
Range("C12").Select
Range(ActiveCell, "AF12").Select
Selection.Find(dateSearch).Select
If Selection.Find(dateSearch) = False Then
Range("A1").Select
MsgBox ("Could not find that Date. Ensure that the date is
entered in the proper format (e.g. 12/25/01 = Christmas)")
Exit Sub
End If

' Payrol
ActiveCell.SmallScroll Down:=2
ActiveCell.Formula = Sheets("In-Store").Range("AB30:aB50")