Sheet:Need Help

**alexm999** · 04-21-2005, 07:36 AM

This may or may not be an IF statement. I have a spreadsheet with 2 Worksheets. Worksheet 1 is called Work, Worksheet 2 is called Data.

WORK has cells Q16, R16 and S16 filled with data that is automatically entered through a formula from a different part of the cell.

DATA has just a table of data with no formulas.

What I need is if in the WORK worksheet Q16, R16 and S16 are populated with numbers $500,000 to $$749,000 then I need it to grab from cells B10 and C10 in the DATA worksheet and somehow copy those cells then go back to the WORK worksheet and enter that info into the Y5 and Y6 cells in the WORK worksheet.

Here's where it becomes difficult. If the numbers in Q16, R16 and S16 are $750,000 to $999,999 then I need it to grab from B11 and C11 in the DATA worksheet.

Can anyone help?

**Leith Ross** · 04-21-2005, 08:16 AM

Hello Alexm999,

Here is a macro to do what you asked. I wasn't sure if you capitalized the worksheet names to draw attention to them or if you had named the sheets this way. If you did name the sheets with all capitals, you need to change the sheet names in the code.

Public Sub MyMacro()

Dim SumTotal

'First Test
'Test WORK cells for $500,000 to $749,000
SumTotal= Worksheets("Work").WorksheetFunction.Sum(Range("Q16:S16"))

If SumTotal > 1,499,999 And SumTotal < 2,247,001 Then

'Copy Cells
Worksheets("Data").Range("B10").Value = Worksheets("Work").Range("Y5").Value
Worksheets("Data").Range("C10").Value = Worksheets("Work").Range("Y6").Value
Exit Sub

End If

'Second Test
'Test DATA cells for $750,000 to $999,999

If SumTotal > 2,249,999 And SumTotal < 2,999,998 Then

'Copy Cells
Worksheets("Data").Range("B11").Value = Worksheets("Work").Range("Y5").Value
Worksheets("Data").Range("C11").Value = Worksheets("Work").Range("Y6").Value

End If

End Sub

Hope this helps,
Leith Ross

Sheet:Need Help

LinkBack

Thread Tools

Rate This Thread

Display

Sheet:Need Help

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions