VBA Excel 2007 Question Closed

Question

Hi there,

I have a slight problem I am currently dealing with making up time sheets on the one sheet for a number of members of staff.

The problem is that we do not know how many staff will be there and I am trying to set up a macro button to do the following:

Take the active cell or user input the cell to start from
if active cell move down 25 places and then
paste the blank time table from sheet 2 into the cell
hide sheet 2

I know how to hide the sheets, paste the sheets, and off set the mouse

but cant seem to make it so the user can input the cell number and it will use that cell to paste into.

Any help would be greatly appreciated.

Current coding is:

    ActiveCell.Offset(25, 0).Select
    Sheets("Sheet2").Visible = True
    Range("A1:AB28").Select
    Selection.Copy
    Sheets("Sheet2").Visible = False
    ActiveCell.PasteSpecial
    ActiveWindow.SmallScroll Down:=27

But it keeps pasting over the current blank sheet rather than moving down cells.

Cheers

venkat1926 · Answer

your requirement is not clear

you want to copy the activecell for e.g. A5 amd paste it 25 cells down that is A20.


the code should be somethinglike this 

activecell.copy
activecell.offset(25,0).pastespecial

thewhistler · Answer

Its fine I have managed to fix it myself :)

Although another problem has developed and cant seem to see the problem.

I am using the index and match combination as follows:

=INDEX(Sheet6!$A$2:$H$81,MATCH(Sheet5!A8,Sheet6!$A$2:$A$81,0),MATCH(Sheet5!$E$7,Sheet6!$A$1:$H$1,0))

yet when the indexed cells are blank it produce N/A# rather than blank.

I have been trying to use the If ISNA in conjunction with the index formula but excel errors saying there are too many arguements. Think I am missing a bracket somewhere but cant see it.

=if(isna(INDEX(Sheet6!$A$2:$H$81,MATCH(Sheet5!A22,Sheet6!$A$2:$A$81,0),MATCH(Sheet5!$D$5,Sheet6!$A$1:$H$1,0)),"",INDEX(Sheet6!$A$2:$H$81,MATCH(Sheet5!A22,Sheet6!$A$2:$A$81,0),MATCH(Sheet5!$D$5,Sheet6!$A$1:$H$1,0))

Excelguru · Answer

=IF(ISNA(INDEX(Sheet6!$A$2:$H$81,MATCH(Sheet5!A22,Sheet6!$A$2:$A$81,0),MATCH(Sheet5!$D$5,Sheet6!$A$1:$H$1,0))),"",INDEX(Sheet6!$A$2:$H$81,MATCH(Sheet5!A22,Sheet6!$A$2:$A$81,0),MATCH(Sheet5!$D$5,Sheet6!$A$1:$H$1,0)))

thewhistler · Answer

Works perfectly knew there was a bracket or 2 missing thanks very much.

Is there any way to copy what is in an active cell then paste it into the find function?

Currently it only seems to work with placing a value within the find function.

Current code is:

    ActiveCell.Offset(-27, 0).Range("A1").Select
    Selection.Copy
    Sheets("Payroll").Select
    Cells.Find(What:="Dave", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Sheets("Sheet1").Select
    ActiveCell.Offset(26, 5).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Payroll").Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select

Excelguru · Answer

I think you have to change here
What:="Dave", 

then try this
What:= activecell.value  ,

VBA Excel 2007 Question

5 responses

Similar discussions

Subscribe To Our Newsletter!