How can I skip few column from a range ?

[Akash kothari]

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 ?

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

 

[Asheesh]

Not sure, what are you after. Can you explain it again

 

[Akash kothari]

Not sure, what are you after. Can you explain it again

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

 

[Akash kothari]

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

 

[Asheesh]

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

 

[Luke M]

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.

 

[Akash kothari]

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

 

[Akash kothari]

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 Asheesh,

My requirement is almost same what Luke formula is giving.

Thank you
Akash

 

[Luke M]

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.

 

[Akash kothari]

Thanks Luke,

This Sounds Good, Thanks for all the help.

Akash