Search : in
By :

VBA Excel 2007 Question

Last answer on Jul 8, 2009 3:53:06 am BST thewhistler, on Jul 6, 2009 11:53:57 am BST 
 Report this message to moderators

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

Configuration: Windows XP
Safari 530.5

Best answers for « VBA Excel 2007 Question » in :
[VBA: VB6] Using excel from another application Show[VBA: VB6] Using excel from another application Here is a little routine to call Excel from VB6 or another Office application. Paste in a general module (eg Module1) In VBA>> Insert>> Module and paste in the window ... In VB6>>...
VBA: Finding Hdc in an Excel worksheet or UserForm ShowVBA: Finding Hdc in an Excel worksheet or UserForm Here are two small examples on how to find Hdc in a worksheet: By clicking on Sheet1 the UserForm is displayed. Put the pointer on UF, hold the left mouse button down and drag the...
How to convert Excel into PDF? ShowHow to convert Excel into PDF? Here is a small tips about how to convert your excel files into PDF for your presentation. Step 1 PDF995 is software that gets installed on your computer which enables you to print any sources of document to...
Download Excel Viewer Show
Download Excel Utility Show
Download DBF to Excel Converter ShowDescription The application is designed by WhiteTown Software. DBF to Excel is a tool that allows you to convert your file easily. Simple and easy to use, the application has been awarded from various places. Offering an intuitive interface, the...
Ask your question Reply

1

venkat1926, on Jul 7, 2009 3:29:03 am BST

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

   
Reply to venkat1926

2

thewhistler, on Jul 7, 2009 9:33:39 am BST

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))

   
Reply to thewhistler

3

Excelguru, on Jul 7, 2009 9:43:56 am BST

=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))) Winners are losers who got up and gave it one more try. -Dennis DeYoung
My Interests are financial Modelling and custom excel development.

   
Reply to Excelguru

4

thewhistler, on Jul 7, 2009 1:28:21 pm BST

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

   
Reply to thewhistler

5

 Excelguru, on Jul 8, 2009 3:53:06 am BST

I think you have to change here
What:="Dave",

then try this
What:= activecell.value , Winners are losers who got up and gave it one more try. -Dennis DeYoung
My Interests are financial Modelling and custom excel development.

   
Reply to Excelguru
Ask your question Reply
They need your help