VBA Excel 2007 Question

[Closed]
Report
Posts
3
Registration date
Sunday July 5, 2009
Status
Member
Last seen
July 7, 2009
-
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
-
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

5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
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
Posts
3
Registration date
Sunday July 5, 2009
Status
Member
Last seen
July 7, 2009

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))
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
=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)))
Posts
3
Registration date
Sunday July 5, 2009
Status
Member
Last seen
July 7, 2009

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
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
I think you have to change here
What:="Dave",

then try this
What:= activecell.value ,