Alternative method to COPY & PASTE

[mali90]

Hi everyone,

I'm relatively new to vba so please bear with me!

Basically:

there are approximately 7-8 sheets from which i need data. to identify the lines that i need to copy across, it is simply whether or not all the columns (C-P) have values in them. IF they do have values in them, i need to copy them into another sheet in the same workbook.

i'm assuming it's some sort of If statement but i cannot figure out how to code it.

if you look at sheets "CT17_2" and "CT17_1", there are some lines that are empty in columns G and I. what i want to do is write a macro that picks up when there is data in all the columns C-P, when there is data (so for example it shouldn't pick up G20, 21, 25, 27, 32), and then copy that data into sheet "CT17".


ANY help would be greatly appreciated.

many thanks,

mali <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[GlennUK]

I can't look at anything that you've mentioned ... maybe it's blocked on my work machine. Can you describe what you are on about?

 

[mali90]

Hi,

I can't seem to attach anything to this thread. If you're comfortable, maybe I can email it to you? If not then I'll try now to describe it...

There are 7 tabs in a workbook, with various bits of information in columns C-P. In between these columns, in G and I, there will sometimes be blank spaces because there is no information to go with the information in the other columns. What I need is to have a macro that can scroll through these 7 (or however many tabs there are), detect when there is information in ALL the columns C-P, and then copy those lines into a new template which will be in a separate tab.

I can do it by recording a macro, but then obviously it will be for specific lines only-I need to know how to manipulate that code to make it so that it does it for any random case and not a specific one, if that makes sense.

Hope this helps, thanks so much!

 

[GlennUK]

Do you want to test the columns G:I or columns C:P ... it doesn't seem clear to me?

A good starting point may be to use AutoFilter to show the records that you want ... then copy and paste the visible cells. If you macro record while doing the correct AutoFilter, what code do you get?

 

[mali90]

I can't use autofilter to filter out the data I want...unless there is a way to autofilter so that it filters out all the rows/columns with values in them? It's quite difficult to explain what I mean without being able to show you what I mean, is there no way to attach on this forum?

 

[GlennUK]

Switch on AutoFilter, and for column G select non-blanks, and then do the same for column H and for column I. Is the resulting subset the one you want?

 

[mali90]

Yes it is, that's perfect-annoying how it was such a simple solution and I couldn't figure it out! Presumably if I just add in 'cells.paste' it will paste them wherever I specify. For some reason when I try to manually copy and paste data within that same workbook, the copy feature becomes disabled...I assume this happens as a result of something else in the code, but hopefully I can manually code it to work.

Many many thanks again!

 

[GlennUK]

Have an experiment with code for pasting the cells. If you have any problems post back here, and someone will help you.