VBA code to paste to a new row in the same column ???

Hi

i would greatly appreciate your help with this.

i am setting up a macro to copy names from a column in each of three tables and then paste them all together in one column in the same worksheet.

i have attached my worksheet.

so i want to be able to copy from cell A3 all the way down to end of col A, and paste that to S3.

And then ALSO

copy from cell G3 down to the end ( or it can be from G2 to include the NAME title if its easier ) .

but the thing i am struggling with is now to paste it to the bottom of the column S ( that already has the pasted data that was done earlier ).

so in effect i would have a macro that copies all names in the tables ( no other data from tables required ), and pastes those names into column s. ( to look like column y )

really hope someone can help with this and is very much appreciated

many many thanks

steve r.

There are many ways of accomplishing this. Here's just one:

Please Login or Register  to view this content.

BSB

Add the below line, just before the End Sub line to get rid of the "marching ants" around the cells from the last copy.

Please Login or Register  to view this content.

BSB

THANKS badly speeled buoy,

this works in your example.

i have never used this listobject code before.

the sheet that i provided was a test sheet from my actual sheet ( which i cannot provide due to data protection )

although i will look into the use of the list objects.
to get it going for now and simplify, if its assumed i can copy each column. what would be the code i could use to paste to a new row in an existing column

so if i have already got the code

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

that copies the data.

what would be the code the pastes to a new row in column s ???


many thanks for your help

I would sugest not using .Select in your code if at all possible. 99.9% of the time it is completely unecessary and will cause more trouble than it solves.
But as you understand those three lines of code I'll gloss over that for now.

Once they're copied you can just use the below line from my code to do the pasting.

Please Login or Register  to view this content.

Repeat your lines for copying other columns then mine again to tag the copied values at the end of column S.

For info ListObjects is how you refer to tables in Excel. So my code just loops through each table in Sheet1 and copies the first column "ListColumns(1)" from each in turn.
Thay way if you add more tables you don't have to amend the code.

BSB

Hi, bedlySpelledBouy,

thanks for the info on that - im looking into it regarding listobjects.

i have tried to insert the code
.Cells(Rows.Count, "S").End(xlUp).Offset(1).PasteSpecial xlValues

exactly as it is to my code

Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

so the code is now
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
.Cells(Rows.Count, "S").End(xlUp).Offset(1).PasteSpecial xlValues

but its coming up with an error message
compile error
invalid or unqualified reference

it doesnt highlight where though

can you see anything wrong or as to why its not working, thanks, steve

Excel is all about the learning curve, and it's never ending

In order to adhere to the forum rules you need to edit your posts above to wrap your VBA in code tags. Simply click "Edit post" bottom right of each one then highlight the VBA sections and click the # icon above your post before saving it.
Moderators will tell me off for replying and you off for not sticking to the rules otherwise...

Once you've done that I can tell you the simple reason why you're getting that error message and how to fix it.

BSB

ok no probs, didnt know that. ill keep in mind

Remove the dot before the word Cells in the last line of code.

Please Login or Register  to view this content.

The error was caused by that dot/full stop as Excel was unsure of which sheet you were referring to the Cells of whilst it's there.
I forgot to remove it from my code but it would have worked fine in my proposed solution using a With Block.


Just to add to the learning, remember I mentioned earlier about not using .Select?
Well there's the same code as above but rewritten to not use Select.

Please Login or Register  to view this content.

BSB

Thanks very much BSB,

That does indeed now work. Cant believe it but Excel has thrown me another curveball problem that i didnt see coming and have not seen before.

basically , as i said that all works. but when i come to do the next days data i would need to delete the destination data from the previous day ( my S column ) so just keeping the column header in s1 but deleting all other data in the table in s.

but when i run the macro.

instead of pasting to s2 and so on. ( as the macro intended )

it , for some reason, pastes to the bottom of the PREVIOUS days entries , to s28 ( even though the rows above it i have since deleted , so i have 27 empty rows and then the new data ??? )

for me its a mystery as to why it does that considering there is no data in s column apart from the header in s1.

do you know why this is happening and how to rectify it ?? is it becuase i have converted column s to a table as i need it to form the basis of a power query ??

thanks, any help you can give to this saga is more than appreciated ....

confused ... steve r

I feel you're deleting the values from the new table, but not the table rows. So when you try to stamp in the next blank row Excel is stamping to the next blank row AFTER the table.

Attach an up to date workbook showing all your code so far and I'll help you adjust it so it works as expected.

BSB

Hi BSB,

sure my workbook is attached

and the macro code im using is described in cell AA

but it is

Please Login or Register  to view this content.

so the code is ( was ) working, but its just pasting to the bottom of the table in S instead of in s3 and so on

you must be correct in that even though im deleting the previous days data in col s. its not deleted properly in terms of what the code is seeing.

i dont know how to deal or correct this though and i do need to delete previous days data in col s.

pls let me know if you have anything to rectify, thanks steve.

thanks for the detailed response BSB

and thanks for the time and effort you have put into this.

unfortunately as mentioned the worksheet that i have given is a working sheet that i am using as i cant post origonal due to data protectiona nd gdpr etc.

and so i cant change the way the sheet is coded to include ListObjects, and futhermore all the code you have suggested.

the only code i can write into the document would be without changing it to listobjects, etc

i understand your response will work and is the best way to do it and as said i appreciate the effort you have gone to there. ( i indeed may look into this way when i have time to change how i arrange the worksheet )

in respect of the code

Please Login or Register  to view this content.

would there be anything i could do to that code to enable it to paste into my table ( col s ) , using the offset+1, when i have deleted the rows in said table. ?

what do you think? possible or not really ??

thanks, steve

Originally Posted by steve78

i cant change the way the sheet is coded to include ListObjects

I'm not sure what you mean by this, OR, you've misunderstood what ListObjects are.
You won't have to change anything in your workbook other than the VBA, and you're doing that anyway.
You already have Excel Tables in your workbook and you've added an aditional one, so you are making changes to the workbook.
ListObjects is just how you refer to these tables with VBA, they are exactly the same thing, so I'm a tad confused as to why the suggestions won't work for you.

BSB

Hi BSB,

my origonal document has lots (well 3) more tables in the worksheet that i cannot show due to data gdpr and also the tables that i show are actually results of power queries ( when i tried ealier i dont think listobjects picks up the power queries as tables ) but maybe i am wrong on this, im not sure ??

and so its not ideal that i cannot attach my actual document of course.

i was just wondering if i could add something to the below code to get it to paste in S table in the next row.

Please Login or Register  to view this content.

not sure if there is anything i can do to it, thanks for any help you can give.

just thinking about it. maybe i could delete the table rows manually ( right click delete rows ) instead of just deleting the data in the table. then i can see that code you gave me earlier working ???

Originally Posted by steve78

just thinking about it. maybe i could delete the table rows manually ( right click delete rows ) instead of just deleting the data in the table. then i can see that code you gave me earlier working ???

Hmmm... Not sure that would be an ideal solution. The idea of VBA for this type of thing is 'automation' not 'do a little bit manually and automate the rest'.

Give this one a try:

Please Login or Register  to view this content.

It relies on your actual workbook being laid out as per your example file. If it's not then it may fall over, and goes to demonstrate the importance of representative examples.

BSB

thanks for this BSB, it does indeed work on my sheet. The issue is that i cant attach my exact sheet due to data protections but in sessence this obviosly works , thanks for the help.

No problem. Happy to help and glad we got there in the end

BSB