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

AndyJR

Board Regular
Joined
Jun 20, 2015
Messages
90
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
Code:
$B4:$Q4
extract block 2 (12 data column) and copy to worksheet "Report" but "non adjacent row", starting on
Code:
$U5:$AF5
extract block 3 (12 data column) and copy to worksheet "Report" but "non adjacent row", starting on
Code:
$AJ6:$AU6
extract block 4 (12 data column) and copy to worksheet "Report" but "non adjacent row", starting on
Code:
$AY7:$BJ7
And so on till reach the block 36



thank you in advance, Any help is appreciated !


Andyjr
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,309
Office Version
2013
Platform
Windows
It appears from your illustration that you mean non adjacent columns rather than non adjacent rows.
Code:
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

Board Regular
Joined
Jun 20, 2015
Messages
90
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

Board Regular
Joined
Jun 20, 2015
Messages
90
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 :
Rich (BB code):
$B4:$Q4
Rich (BB code):
$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
 
Last edited:

AndyJR

Board Regular
Joined
Jun 20, 2015
Messages
90
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,309
Office Version
2013
Platform
Windows
Try this
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,309
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Jun 20, 2015
Messages
90
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.
Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,309
Office Version
2013
Platform
Windows
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.
Code:
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

Board Regular
Joined
Jun 20, 2015
Messages
90
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

 

Forum statistics

Threads
1,082,549
Messages
5,366,237
Members
400,881
Latest member
aforte

Some videos you may like

This Week's Hot Topics

Top