Try this macro

Sub test()
Selection.Copy
With Worksheets("sheet2")
.Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial
End With
Application.CutCopyMode = False
End Sub

Hi Venkat,

Wow, it almost feels like magic. But how do I add more sheets? I tried to do it like this:

With Worksheets("sheet2","sheet3","sheet4")

But this doesn't seem to work.

Waiting for your reply,
Trowa

Try this

Sub test()
Dim j As Integer, k As Integer
j = Worksheets.Count
With Worksheets("sheet1")
Selection.Copy
End With
For k = 2 To j

With Sheets(k)
.Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial
End With
line1:
Next k
Application.CutCopyMode = False
End Sub

Many thanks Venkat.

Venkat,

For some reason my Vlookup functions on the destination sheets give the wrong data once I run the macro.

Instead of the right value it gives mostly zeros and a few #N/B's.

Any idea why that is???

Are there any blanks or #N/A s in the relevant reanges for vlookup

If there is still some problem send the sheet with the reference range also.

Hi Venkat,

Sorry for the late reaction but I wasn't at work the past week.

I do have blanks in the range for VLOOKUP, but that is not a problem before I run the macro.

Here is my file:
http://www.speedyshare.com/460252373.html

It's in dutch, so let me know if you need some translations.

On sheet "Verlofoverzicht" I have put backnames in column A, frontnames in column B and their functions in short in column C.
When I need to add some names I input them at the bottom of the list along with their function and select only their back- and frontnames. Then I run your macro, which I called "NamenDoorvoeren".
The macro will put the selected names in 12 sheets, one for each month: "Januari", "Februari", .... , "December".
In each month sheet I have put the functions in column AI. To get these functions I used VLOOKUP (VERT.ZOEKEN in dutch). Since I don't know how many names will be added in time, the VLOOKUP has a range with names and a lot of blanks.

I hope you can help me.

Best regards,
Trowa

Sorry I was not able to retrieve your file. I get following error message
quote
SpeedyShare doesn't know the type of this file. It may be practically everything, compressed archive, video, picture, or something else. Make sure to check the file for viruses, because computer viruses are very dangerous. If you don't have any antivirus program, consider buying one.
unquote
if there is provision in the forum you an send the file as attachment to my email address through te administrator of the forum (I do not know the procedure). Also explain fully what you require because I have not saved your earlier messages.

Strange; it's a excel file with no virus!

I will have to think this through.

I want to try some things, but I'll probably ask you for another VBA code to bypass the VLOOKUP.

Best regards,
Trowa

If you do not want to uses vlookup then use a macro with "find" method . you need not give full excel file. you can just copy the first few rows and columns into your reply and tell me what you want.

Is it enough if I tell you what the vlookup does?

Cell AI8 has the vlookup, which checks the value in cell A8 of the same sheet called "Januari".
The matrix it checks is in sheet "Verlofoverzicht" and has the range A8:C200, the result is in column C.

The vlookup is applied to range AI8:AI200 in 12 sheets: "Januari", "Februari", "Maart", "April", "Mei", "Juni", "Juli",
"Augustus", "September", "Oktober", "November", "December".


Here is my effort of making a copy of how my sheets look like:

Sheet "Verlofoverzicht"
A_________B_______C
Akas______Talat____CH
Behage____Ed______1 M
Belachen___Said____ PB

Sheet "Januari", same as sheets "Februari", "Maart", "April", "Mei", "Juni", "Juli",
"Augustus", "September", "Oktober", "November", "December".
A________B______.....___AI
Akas______Talat__.....___=IF(A8=0,"",Vlookup(A8,Verlofoverzicht!$A$8:$C$200,3)
Behage____Ed____.....___=IF(A9=0,"",Vlookup(A9,Verlofoverzicht!$A$8:$C$200,3)
Belachen___Said__.....___=IF(A10=0,"",Vlookup(A10,Verlofoverzicht!$A$8:$C$200,3)

To avoid getting results I don't want, i have combined VLOOKUP with IF. So if there is no name in range A8:A200 then give a blank result.

What I would like you to do is create a macro instead of the function, because when I run your previously provided macro the function won't work properly anymore.

Many thanks in advance and best regards,
Trowa

Quote
IF(A8=0,"",Vlookup(A8,Verlofoverzicht!$A$8:$C$200,3)
unquote
this formula is written in AI8 in shee Januari. is it ok
in the first argument If A8=0,....
then also you refer to A8 of sheet Januarai and not Verlofoverzicht
this is clear
But you have written
quote
i have combined VLOOKUP with IF. So if there is no name in range A8:A200
then give a blank result.
unquote
you mean A8:C200(typo here)
here you are referring to A8 of the sheet Verlofoverzicht
your message does not show the full formula. then only, one can check whether there is any
mistake in the formula
so the formula appears to be wrong
you can write if you want in AI8 the following formula
=IF(ISNA(VLOOKUP(A8,Verlofoverzicht!$A$8:$C$200,3,0)),"",VLOOKUP(A8,Verlofoverzicht!$A$8:$C$200,3,0))
Modify the macro (particularly the matrix in the second argument in the above vlookup fomaula
at two places as well as the thrid argument column number(3 in my case) . see also dollar signs.
copy AI8 down.

try this and still if you want a macro post back

to type this formula in all the sheets
by keeping pressed the control key if you click all the sheet tabs Januari to december
and type the formula in one sheet it wil be copied in all the sheets. the again click one of the sheets
and selection of all sheets is deselected.

Wow Venkat, you've impressed me once again.

I've put the corrected formula in cell AI8 and copied it down for all twelve sheets (thanks for letting me know the fastest way to do this!, I wasn't aware of that method).

Now when I run the macro, the results of the formula aren't messed up.

Thanks again for your help.

Best regards,
Trowa