• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I skip few column from a range ?

A

Akash kothari

Member
from range B1:K50 sometime may be I want to skip 3 column or 4 column , So suppose if in sheet 2 cell C1 has value "2" then starting from range B1:K50 in sheet1 it should skip 2 column if its given 3 it should skip 3 columns and so on.

How can we do this ?

Code: 
Sub testing()

Sheets("Data").Range(Sheets("Invoice").Range("B6") & ":" & Sheets("Invoice").Range("C6")).copy
Sheets("Invoice").Select
Range("B9").PasteSpecial xlPasteValues

End Sub

Attached sample file


Thank you
Akash
 

Attachments

Asheesh said:
Not sure, what are you after. Can you explain it again
Click to expand...

Hi Asheesh,

In the attached file from Sheet1 I want to copy only actual hours for range mentioned in sheet2 cell B6 and AJ18 (*in sheet2 incorrectly mentioned as T18) like that for all the other divisions (2000 & 9000) and paste it in sheet2 from under column H9

Thanks
Akash
 
Hi Asheesh,

Is it possible ?

If you can let me know how we can skip column in above copy code which i have created I can work on rest.

Thank you
Akash
 
Hi Akash,

I am sorry I have not been able to interpret your exact requirement..however, can you just put the result on "Invoice" tab manually..

Meanwhile, I see @Luke M online, let me invite him, he might be able to interpret your requirement..
 
Hi all,

I think trying to do this with the current setup of data and a macro will not be easy. I'd suggest using some formulas to pull over the desired information.

Akash, in sheet Invoice, cell H9, put this array formula:

=IFERROR(INDEX(Data!16:16,SMALL(IF(Data!$Z$14:$AO$14=H$8,COLUMN(Data!$Z$14:$AO$14)),COLUMNS($A$1:A$1))),"")

Copy to the right and down as needed.
 

Attachments

Last edited:
Hi Luke,

Thank you so much, This formula works very good, Is there a better way you think we can use to this task using Macro ?
My only requirement is to pull Actual hours and Resource details in cell range (R16:T18).

Thannk you
Akash
 
Asheesh said:
Hi Akash,

I am sorry I have not been able to interpret your exact requirement..however, can you just put the result on "Invoice" tab manually..

Meanwhile, I see @Luke M online, let me invite him, he might be able to interpret your requirement..
Click to expand...

Hi Asheesh,

My requirement is almost same what Luke formula is giving. :)

Thank you
Akash
 
Hi Akash,

My first though would be to 'cheat'; have the macro write the above formula into desired range, and then have it do a copy, paste values. A little slower than having the macro build an array variable and transfer the data, but not by much.
 
You must log in or register to reply here.
Back
Top