VBA to extract data from block info in a long row,and copy on another sheet non adjacent column

[AndyJR]

Hi all,

I know a lil bit excel, and i just started to learn VBA, need some help on creating this report.

In a worksheet "Hist" i have a 36 block of data located in a single row, each block use 12 column of data
block 1 = F4:Q4, (3 blanc column) then Block 2 = U4:AF4, then (3 blanc column) and Block 3.... and so on

what i need to achieve is :
extract block 1 (12 data column) and copy to worksheet "Report" but "non adjacent row", starting on

$B4:$Q4

extract block 2 (12 data column) and copy to worksheet "Report" but "non adjacent row", starting on

$U5:$AF5

extract block 3 (12 data column) and copy to worksheet "Report" but "non adjacent row", starting on

$AJ6:$AU6

extract block 4 (12 data column) and copy to worksheet "Report" but "non adjacent row", starting on

$AY7:$BJ7

And so on till reach the block 36



thank you in advance, Any help is appreciated !


Andyjr

 

[JLGWhiz]

It appears from your illustration that you mean non adjacent columns rather than non adjacent rows.

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, rw As Long, lc As Long
Set sh1 = Sheets("Hist")
Set sh2 = Sheets("Report")
lc = sh1.Cells(4, Columns.Count).End(xlToLeft).Column - 11
    rw = 4
    For i = 2 To lc Step 15
        sh1.Cells(4, i).Resize(1, 12).Copy sh2.Cells(rw, i)
        rw = rw + 1
    Next
End Sub

If this is not what you want, then try to give a better illustration of what you expect to see as a result.

 

[AndyJR]

Hi Mr JLGWhiz,

Yes, you're absolutely right. by mistake i said "'row"
and it was non adjacent Column..

I'm going to try right now!


Thank you !!

 

[AndyJR]

Hi Mr JLGWhiz

it does the copy with format color, contents and the formulas it self.
matter of fact this feature is perfect.


Because i made another mistake, i would like to ask you :
in your code, which line can i modify to copy each data block in "report" sheet as :

$B4:$Q4

$B5:$Q5
$B6:$Q6
$B7:$Q7
and so on... till reach $B40:$Q40

Thank you, and i apologise for my many mistake on short period of time..


AndyJr

 

[AndyJR]

Hi JLGWhiz,

the code it's not copy as "non adjacent Column.."

whenever you an spare time, please let me know the answer from (POST#4)

Thank you

AndyJr

 

[JLGWhiz]

Try this

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, rw As Long, lc As Long
Set sh1 = Sheets("Hist")
Set sh2 = Sheets("Report")
lc = sh1.Cells(4, Columns.Count).End(xlToLeft).Column - 11
    rw = 4
    For i = 2 To lc Step 15
        sh1.Cells(4, i).Resize(1, 12).Copy sh2.Cells(rw, 2)
        rw = rw + 1
    Next
End Sub

 

[JLGWhiz]

Please post any comments related to the code or your objective for your project in this thread so that all members of the forum can get the benefit of the dialogue, if any. Only you and I see the private messages. Regarding the non-adjacent column, I requested an explanation of that before and you have posted two examples of what you want as a result. I have altered the code to produce both as illustrated. If you want to try one more time, then post an explanation or an example of what you want to see as an output and I will try to modify the code to make it happen. Post it here, not in a privatge message.

 

[AndyJR]

Hi mr JLGWhiz,

First, Thank you so much for the code, and at same time I'm so sorry to bothers you.

The code it copy from "Hist" to "Report" perfectly, But the data is not copied as
non adjacent column in the "report"
in order to get the report as non-adjacent column i added a line code
but it wont work.

Range("F4").Offset(RowOffSet:=0, ColumnOffset:=1).Select

I know a lil bit excel, but not <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> and just a week a go i Started studying a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> course
online.


Thank you so much

AndyJr

 

[JLGWhiz]

Apparently, you want the data on sheets("Report") to cascade one column at a time. The term non-adjacent is too vague in definition to describe the output you seem to be after. Give this one a try and see if it displays the data like you want.

Sub copyStuff3()
Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, rw As Long, lc As Long
Set sh1 = Sheets("Hist")
Set sh2 = Sheets("Report")
lc = sh1.Cells(4, Columns.Count).End(xlToLeft).Column - 11
    rw = 4
    For i = 2 To lc Step 15
        sh1.Cells(4, i).Resize(1, 12).Copy sh2.Cells(rw, rw - 2)
        rw = rw + 1
    Next
End Sub

 

[AndyJR]

Hi JLGWhiz,

yes, non adjacent seems vague but is for the following reason :
each of the empty column it's going to have numbers from 01 to 40+ (or text, don't know yet)
so the Report it's going to look like this :

______col___col___col___col___col___col__col
______E_____F____G____H____I____J____K__
row4_ 01, rpt here, 01, rpt here,01, rpt here,01.... etc.etc.etc
row5_ 02, rpt here, 02, rpt here,02, rpt here,02.... etc,etc,etc
row6_ 03, rpt here, 03, rpt here,03, rpt here,03.... etc,etc,etc
etc,
etc,
etc


Thank you !!


AndyJr