Excel: Copy active range to bottom of listSolved/Closed

Question

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

venkat1926 · Accepted Answer

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

Trowa · Answer

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

venkat1926 · Answer

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

Trowa · Answer

Many thanks Venkat.

Trowa · Answer

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

venkat1926 · Answer

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.

Trowa · Answer

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

venkat1926 · Answer

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.

Trowa · Answer

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

venkat1926 · Answer

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.

Trowa · Answer

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

venkat1926 · Answer

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.

Trowa · Answer

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

Excel: Copy active range to bottom of list

13 responses

Similar discussions

Subscribe To Our Newsletter!