Marco button, Copy and paste depend

Buiklt / NA
February 18, 2009 at 08:32:09
Specs: Windows XP, E8400/3Gig

I am trying to create a marco button that does the following...

Search a set of data in sheet "DaySin" let's say (A5 - F5 and all the way down), if F5 is greater than 0 then create a new sheet call "Result" then copy the entire row and paste the entire row from "Daysin" to "Result" and goes through all the data till column A is blank. Can someone help me with the coding in excel? Thank you

See More: Marco button, Copy and paste depend

Report •

February 18, 2009 at 22:24:40
tested on office 07
Function test()
    Dim src As Worksheet
    Set src = Worksheets("Daysin")
'    On Error Resume Next
    Sheets.Add.Name = "Result"

    If IsNumeric(src.Range("F5").Value) And src.Range("F5").Value > 0 Then
        x = Array("Daysin", "Result")
        Sheets(x).FillAcrossSheets Intersect(src.Range("A5:F999"), src.UsedRange)
    End If
End Function

this will get you started.
Modify the code to suit your need, F1 will help you. Press F1 for help, particularly on worksheet object, range object, and cell.

Report •

February 19, 2009 at 08:57:08
Hi thanks for replying I am using office 2003, it should pretty much the same. I copied and pasted the code, it when I run it, it copied everything over including the one that are negative. Please help

Report •

February 19, 2009 at 09:51:29
Oh also if I can add a message box after the marco completed it will be great, maybe like "Check result tab for information". THanks

Report •

Related Solutions

February 19, 2009 at 16:31:49
what have you try to modify the code?

Report •

February 20, 2009 at 08:27:51
No I didn't modify the code, even though "Value > 0 Then" should only picks up positive value but its picking up everything. Any idea why?

Report •

February 20, 2009 at 19:52:09
i'm busy at the moment and dont have the time to write the code, guess you should try to make it works.
yeah, the code works as what i intend it to, if cell value at F5 is number and bigger than 0, then copy used range from "A5:F5-downwards" to new sheet.

so you want to evaluate every cell at column "F", then here is a hint:

for each c (cell) in range at column 5
   if c.value is bigger than 0 then
       do identify the row range of this cell and copy the row to new sheet
   end if

search builtin-help (F1) for cell, column, row, and range and looks for available properties and methods that you use.

Report •

Ask Question