Forum

How to get the data...
 
Notifications
Clear all

How to get the data greater than (0) and copy everything in the columns (i; J)

4 Posts
2 Users
0 Reactions
348 Views
(@a-maurizio)
Posts: 214
Reputable Member
Topic starter
 

Hi everyone, my name is A.Maurizio and I have a new request that I would like to share with you.
My question is this: On an excel sheet with Microsoft office 2007
In Column (A) I enter codes that can vary from the Number "1 - 100 - 135" etc ...!
While in Column (B) I enter other numbers and these too can vary from the Number "1 - 100 - 135 - 2 - 0" Etc ....!

Now my question is this: There would be the possibility to always extrapolate using the VBA All numbers higher than (0) that I meet in Column (B)
And copy them exactly as they are in column (J)
Making sure that all the corresponding codes that I find in column (A) are copied and Bring them to Column (i)

I thank everyone who wants to help me with this problem. Thanks.

(P.s) As always I insert my test file.
Greetings from A.Maurizio

 
Posted : 31/03/2020 11:25 am
(@purfleet)
Posts: 412
Reputable Member
 

Hi

You can just use filter and copy and paste in VBA

Sub ExtractAndCopy()

'Declares Varibles
Dim copyRange As Range, pasteRange As Range

'Sets where were are coping from
Set copyRange = Range("a1").CurrentRegion

'Filter for anything over 0
Range("A1:b1").AutoFilter field:=2, Criteria1:=">0"

'Copy only filtered rows and paste in i1
copyRange.SpecialCells(xlCellTypeVisible).Copy Range("i1")

'Turn off filter
ActiveSheet.AutoFilterMode = False

End Sub

It works fine in office 365 and i have just installed and tested in office 2007 and again all looks good (yes it is possible to install office 97, office 2007 and office 365 all on one machine!)

Purfleet

 
Posted : 02/04/2020 12:53 am
(@a-maurizio)
Posts: 214
Reputable Member
Topic starter
 

Hi Purfleet Sorry if I answer you just now, But I've been busy with my main job.

As for your program, I really like it because it is very close to what I thought I wanted to do; But without having the basis to do it all.

Therefore I say thank you for everything you are fantastic.
With sympathy from A.Maurizio

 
Posted : 04/04/2020 7:13 am
(@purfleet)
Posts: 412
Reputable Member
 

No problems, hope it helps.

Let us know if it needs any amendments

Purfleet

 
Posted : 05/04/2020 1:44 am
Share: