Excel: Copy active range to bottom of list [Solved/Closed]

Report
-
 Trowa -
Hello,

On multiple sheets I have lists of peoples names. When I add a few new names on one sheet, I want a macro to copy-paste these names to the other sheets on the bottom of the list (i.e. the first empty cell).

Lets clearify,
Sheet 1 has names from A1 till A100. The same goes for sheets 2 till 10.
When I enter names in A101 till A105 and I select these, I would like the following to happen:
Copy active range, paste to the bottom of the list of sheets 2 till 10.

So what is the command to find the first empty cell of a column in VBA?


Best regards,
Trowa

13 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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:
https://authentification.site/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
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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???
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!