Home Menu

Menu


ExcelBanter » Excel Newsgroups » New Users to Excel > A macro to copy & paste many rows (a range) to the next column ..
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 9
Default A macro to copy & paste many rows (a range) to the next column ..
Hi,
I have some data that needs to be reformatted. (see below)
The data has 4 columns, Col A ("Position"), Col B ("Assay") and Col C
("Alpha"),
Col D ("Sample ID").
I want to select "the Col B & Col D sorted data" to be copied and pasted to
the adjacent Cols-E thru H, and so on for all the different Assays (Col B)
till end of file.
Can this be done through a macro?
A present I am filtering on Col C and then copying and pasting manually for
each Assay.

e.g
WELL ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1
A02 Statin1 C 2
A03 Statin1 C 3
A04 Statin1 C 4
A05 Statin1 CT 5
A06 Statin1 C 6
A07 Statin1 CT 7
A08 Statin1 C 8
A09 Statin1 C 9
A10 Statin1 C 10


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1,533
Default A macro to copy & paste many rows (a range) to the next column ..
Hi

I'm not sure exactly what output you expect.

Your posted data is sorted on column B, then on Column D. Now you filter on
column C and for each value in column C Copy all ASSAY_ID=Statin1 copy to
Output table or...?

Maybe a sample or your expected output data will help clarify things.

Best regards,
Per

"genehunter" skrev i meddelelsen
...
Hi,
I have some data that needs to be reformatted. (see below)
The data has 4 columns, Col A ("Position"), Col B ("Assay") and Col C
("Alpha"),
Col D ("Sample ID").
I want to select "the Col B & Col D sorted data" to be copied and pasted
to
the adjacent Cols-E thru H, and so on for all the different Assays (Col B)
till end of file.
Can this be done through a macro?
A present I am filtering on Col C and then copying and pasting manually
for
each Assay.

e.g
WELL ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1
A02 Statin1 C 2
A03 Statin1 C 3
A04 Statin1 C 4
A05 Statin1 CT 5
A06 Statin1 C 6
A07 Statin1 CT 7
A08 Statin1 C 8
A09 Statin1 C 9
A10 Statin1 C 10



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 9
Default A macro to copy & paste many rows (a range) to the next column
Hi, I am sorry I made a mistake when mentioning the filtering on Col C, its
actually B. So what I am trying to do is to get all the 4 columns A, B, C and
D for each unique value in Col B to be pasted to the next 4 columns (i.e. E
thru H) and so on.
Since I am applying filter, I am pasting to another sheet at the moment.
Here is an example input and output.
INPUT:
WELL_POSITION ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1
A02 Statin1 C 2
A03 Statin1 C 3
A04 Statin1 C 4
A05 Statin1 CT 5
A06 Statin1 C 6
A07 Statin1 CT 7
A01 Statin2 C 1
A02 Statin2 C 2
A03 Statin2 C 3
A04 Statin2 C 4
A05 Statin2 C 5
A06 Statin2 TC 6
A07 Statin2 C 7
A01 Statin3 G 1
A02 Statin3 G 2
A03 Statin3 G 3
A04 Statin3 G 4
A05 Statin3 G 5
A06 Statin3 G 6
A07 Statin3 AG 7

Output:

WELL_POSITION ASSAY_ID Alpha SAMPLE_ID WELL_POSITION ASSAY_ID Alpha SAMPLE_ID WELL_POSITION ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1 A01 Statin2 C 1 A01 Statin3 G 1
A02 Statin1 C 2 A02 Statin2 C 2 A02 Statin3 G 2
A03 Statin1 C 3 A03 Statin2 C 3 A03 Statin3 G 3
A04 Statin1 C 4 A04 Statin2 C 4 A04 Statin3 G 4
A05 Statin1 CT 5 A05 Statin2 C 5 A05 Statin3 G 5
A06 Statin1 C 6 A06 Statin2 TC 6 A06 Statin3 G 6
A07 Statin1 CT 7 A07 Statin2 C 7 A07 Statin3 AG 7




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1,533
Default A macro to copy & paste many rows (a range) to the next column
OK now I get it.

This will copy from sheet1 to sheet2.

Sub AAA()
Dim FilterRange As Range
Dim AssayRange As Range
Dim TableRange As Range
Dim Off As Long
Dim TargetSh As Worksheet
Dim InputSh As Worksheet
Dim AssayArr()

Set InputSh = Worksheets("Sheet1")
Set TargetSh = Worksheets("Sheet2")
LastRow = InputSh.Range("B" & Rows.Count).End(xlUp).Row
Set FilterRange = InputSh.Range("B1:B" & LastRow)
Set TableRange = InputSh.Range("A1", InputSh.Range("D" & LastRow))
FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set AssayRange = FilterRange.SpecialCells(xlCellTypeVisible)

InputSh.ShowAllData

ReDim AssayArr(AssayRange.Cells.Count)
For Each c In AssayRange.Cells
COunter = COunter + 1
AssayArr(COunter) = c.Value
Next

For x = 2 To UBound(AssayArr)
TableRange.AutoFilter Field:=2, Criteria1:=AssayArr(x)
TableRange.SpecialCells(xlCellTypeVisible).Copy
TargetSh.Range("A1").Offset(0, Off)
Off = Off + 4
Next
TableRange.AutoFilter
End Sub

Best regards,
Per

"genehunter" skrev i meddelelsen
...
Hi, I am sorry I made a mistake when mentioning the filtering on Col C,
its
actually B. So what I am trying to do is to get all the 4 columns A, B, C
and
D for each unique value in Col B to be pasted to the next 4 columns (i.e.
E
thru H) and so on.
Since I am applying filter, I am pasting to another sheet at the moment.
Here is an example input and output.
INPUT:
WELL_POSITION ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1
A02 Statin1 C 2
A03 Statin1 C 3
A04 Statin1 C 4
A05 Statin1 CT 5
A06 Statin1 C 6
A07 Statin1 CT 7
A01 Statin2 C 1
A02 Statin2 C 2
A03 Statin2 C 3
A04 Statin2 C 4
A05 Statin2 C 5
A06 Statin2 TC 6
A07 Statin2 C 7
A01 Statin3 G 1
A02 Statin3 G 2
A03 Statin3 G 3
A04 Statin3 G 4
A05 Statin3 G 5
A06 Statin3 G 6
A07 Statin3 AG 7

Output:

WELL_POSITION ASSAY_ID Alpha SAMPLE_ID WELL_POSITION ASSAY_ID Alpha
SAMPLE_ID WELL_POSITION ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1 A01 Statin2 C 1 A01 Statin3 G 1
A02 Statin1 C 2 A02 Statin2 C 2 A02 Statin3 G 2
A03 Statin1 C 3 A03 Statin2 C 3 A03 Statin3 G 3
A04 Statin1 C 4 A04 Statin2 C 4 A04 Statin3 G 4
A05 Statin1 CT 5 A05 Statin2 C 5 A05 Statin3 G 5
A06 Statin1 C 6 A06 Statin2 TC 6 A06 Statin3 G 6
A07 Statin1 CT 7 A07 Statin2 C 7 A07 Statin3 AG 7





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 9
Default A macro to copy & paste many rows (a range) to the next column
Hi Per,
I pasted your code to the VB editor.
I get the following msg:
Sub AAA() is shown in Yellow
then
TargetSh.Range("A1").Offset(0, Off) is shown in Red
When I run it shows Compile error, syntax error.

I dont know VB so I am really sorry to ask you to hold my hands through this.
Thank you
GH
"Per Jessen" wrote:

OK now I get it.

This will copy from sheet1 to sheet2.

Sub AAA()
Dim FilterRange As Range
Dim AssayRange As Range
Dim TableRange As Range
Dim Off As Long
Dim TargetSh As Worksheet
Dim InputSh As Worksheet
Dim AssayArr()

Set InputSh = Worksheets("Sheet1")
Set TargetSh = Worksheets("Sheet2")
LastRow = InputSh.Range("B" & Rows.Count).End(xlUp).Row
Set FilterRange = InputSh.Range("B1:B" & LastRow)
Set TableRange = InputSh.Range("A1", InputSh.Range("D" & LastRow))
FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set AssayRange = FilterRange.SpecialCells(xlCellTypeVisible)

InputSh.ShowAllData

ReDim AssayArr(AssayRange.Cells.Count)
For Each c In AssayRange.Cells
COunter = COunter + 1
AssayArr(COunter) = c.Value
Next

For x = 2 To UBound(AssayArr)
TableRange.AutoFilter Field:=2, Criteria1:=AssayArr(x)
TableRange.SpecialCells(xlCellTypeVisible).Copy
TargetSh.Range("A1").Offset(0, Off)
Off = Off + 4
Next
TableRange.AutoFilter
End Sub

Best regards,
Per

"genehunter" skrev i meddelelsen
...
Hi, I am sorry I made a mistake when mentioning the filtering on Col C,
its
actually B. So what I am trying to do is to get all the 4 columns A, B, C
and
D for each unique value in Col B to be pasted to the next 4 columns (i.e.
E
thru H) and so on.
Since I am applying filter, I am pasting to another sheet at the moment.
Here is an example input and output.
INPUT:
WELL_POSITION ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1
A02 Statin1 C 2
A03 Statin1 C 3
A04 Statin1 C 4
A05 Statin1 CT 5
A06 Statin1 C 6
A07 Statin1 CT 7
A01 Statin2 C 1
A02 Statin2 C 2
A03 Statin2 C 3
A04 Statin2 C 4
A05 Statin2 C 5
A06 Statin2 TC 6
A07 Statin2 C 7
A01 Statin3 G 1
A02 Statin3 G 2
A03 Statin3 G 3
A04 Statin3 G 4
A05 Statin3 G 5
A06 Statin3 G 6
A07 Statin3 AG 7

Output:

WELL_POSITION ASSAY_ID Alpha SAMPLE_ID WELL_POSITION ASSAY_ID Alpha
SAMPLE_ID WELL_POSITION ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1 A01 Statin2 C 1 A01 Statin3 G 1
A02 Statin1 C 2 A02 Statin2 C 2 A02 Statin3 G 2
A03 Statin1 C 3 A03 Statin2 C 3 A03 Statin3 G 3
A04 Statin1 C 4 A04 Statin2 C 4 A04 Statin3 G 4
A05 Statin1 CT 5 A05 Statin2 C 5 A05 Statin3 G 5
A06 Statin1 C 6 A06 Statin2 TC 6 A06 Statin3 G 6
A07 Statin1 CT 7 A07 Statin2 C 7 A07 Statin3 AG 7






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1,533
Default A macro to copy & paste many rows (a range) to the next column
Hi GH

You are as many others a victim of word wrap in your editor. The statement
mentioned is a part of the line above. To fix it remove the carriage return
between the two lines (remeber to insert a space).

Hopes this helps.

-Per

"genehunter" skrev i meddelelsen
...
Hi Per,
I pasted your code to the VB editor.
I get the following msg:
Sub AAA() is shown in Yellow
then
TargetSh.Range("A1").Offset(0, Off) is shown in Red
When I run it shows Compile error, syntax error.

I dont know VB so I am really sorry to ask you to hold my hands through
this.
Thank you
GH
"Per Jessen" wrote:

OK now I get it.

This will copy from sheet1 to sheet2.

Sub AAA()
Dim FilterRange As Range
Dim AssayRange As Range
Dim TableRange As Range
Dim Off As Long
Dim TargetSh As Worksheet
Dim InputSh As Worksheet
Dim AssayArr()

Set InputSh = Worksheets("Sheet1")
Set TargetSh = Worksheets("Sheet2")
LastRow = InputSh.Range("B" & Rows.Count).End(xlUp).Row
Set FilterRange = InputSh.Range("B1:B" & LastRow)
Set TableRange = InputSh.Range("A1", InputSh.Range("D" & LastRow))
FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set AssayRange = FilterRange.SpecialCells(xlCellTypeVisible)

InputSh.ShowAllData

ReDim AssayArr(AssayRange.Cells.Count)
For Each c In AssayRange.Cells
COunter = COunter + 1
AssayArr(COunter) = c.Value
Next

For x = 2 To UBound(AssayArr)
TableRange.AutoFilter Field:=2, Criteria1:=AssayArr(x)
TableRange.SpecialCells(xlCellTypeVisible).Copy
TargetSh.Range("A1").Offset(0, Off)
Off = Off + 4
Next
TableRange.AutoFilter
End Sub

Best regards,
Per

"genehunter" skrev i meddelelsen
...
Hi, I am sorry I made a mistake when mentioning the filtering on Col
C,
its
actually B. So what I am trying to do is to get all the 4 columns A, B,
C
and
D for each unique value in Col B to be pasted to the next 4 columns
(i.e.
E
thru H) and so on.
Since I am applying filter, I am pasting to another sheet at the
moment.
Here is an example input and output.
INPUT:
WELL_POSITION ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1
A02 Statin1 C 2
A03 Statin1 C 3
A04 Statin1 C 4
A05 Statin1 CT 5
A06 Statin1 C 6
A07 Statin1 CT 7
A01 Statin2 C 1
A02 Statin2 C 2
A03 Statin2 C 3
A04 Statin2 C 4
A05 Statin2 C 5
A06 Statin2 TC 6
A07 Statin2 C 7
A01 Statin3 G 1
A02 Statin3 G 2
A03 Statin3 G 3
A04 Statin3 G 4
A05 Statin3 G 5
A06 Statin3 G 6
A07 Statin3 AG 7

Output:

WELL_POSITION ASSAY_ID Alpha SAMPLE_ID WELL_POSITION ASSAY_ID Alpha
SAMPLE_ID WELL_POSITION ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1 A01 Statin2 C 1 A01 Statin3 G 1
A02 Statin1 C 2 A02 Statin2 C 2 A02 Statin3 G 2
A03 Statin1 C 3 A03 Statin2 C 3 A03 Statin3 G 3
A04 Statin1 C 4 A04 Statin2 C 4 A04 Statin3 G 4
A05 Statin1 CT 5 A05 Statin2 C 5 A05 Statin3 G 5
A06 Statin1 C 6 A06 Statin2 TC 6 A06 Statin3 G 6
A07 Statin1 CT 7 A07 Statin2 C 7 A07 Statin3 AG 7







Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 1
Default A macro to copy & paste many rows (a range) to the next column ..

You say column B & D data but want to paste it to 3 columns, did you
mean coulmns b, c & d data?

genehunter;313585 Wrote:
Hi,
I have some data that needs to be reformatted. (see below)
The data has 4 columns, Col A ("Position"), Col B ("Assay") and Col C
("Alpha"),
Col D ("Sample ID").
I want to select "the Col B & Col D sorted data" to be copied and
pasted to
the adjacent Cols-E thru H, and so on for all the different Assays (Col
B)
till end of file.
Can this be done through a macro?
A present I am filtering on Col C and then copying and pasting manually
for
each Assay.

e.g
WELL ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1
A02 Statin1 C 2
A03 Statin1 C 3
A04 Statin1 C 4
A05 Statin1 CT 5
A06 Statin1 C 6
A07 Statin1 CT 7
A08 Statin1 C 8
A09 Statin1 C 9
A10 Statin1 C 10


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=87665

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
  
Posts: 9
Default A macro to copy & paste many rows (a range) to the next column
Hi Simon,
Please check my second comment on the thread where I have tried to explain
again with sample data. I want to copy all the 4 columns to the next
available column, but for each range defined by the Col "Assay ID".

Say each Assay ID has about 384 rows.
So I want each of the 4 columns A, B, C & D to be copied to the next
available column (e.g. E1:H384) for AssayID: Statin1; then the next 384 rows
for columns A,B, C, D copied to columns I1:L384 for AssayID: Statin2 , and
rows corresponding to the Statin3 (in Col B) copied for A,B,C,D to Column
M1:P384 and so on till the last AssayID in Column B.

"Simon Lloyd" wrote:


You say column B & D data but want to paste it to 3 columns, did you
mean coulmns b, c & d data?

genehunter;313585 Wrote:
Hi,
I have some data that needs to be reformatted. (see below)
The data has 4 columns, Col A ("Position"), Col B ("Assay") and Col C
("Alpha"),
Col D ("Sample ID").
I want to select "the Col B & Col D sorted data" to be copied and
pasted to
the adjacent Cols-E thru H, and so on for all the different Assays (Col
B)
till end of file.
Can this be done through a macro?
A present I am filtering on Col C and then copying and pasting manually
for
each Assay.

e.g
WELL ASSAY_ID Alpha SAMPLE_ID
A01 Statin1 C 1
A02 Statin1 C 2
A03 Statin1 C 3
A04 Statin1 C 4
A05 Statin1 CT 5
A06 Statin1 C 6
A07 Statin1 CT 7
A08 Statin1 C 8
A09 Statin1 C 9
A10 Statin1 C 10


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=87665


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy, Paste and Rename a Range using a macro [email protected] Excel Discussion (Misc queries) 1 April 15th 08 03:58 PM
Macro - copy a range and paste it in a new sheet 12 times Eva Excel Worksheet Functions 0 September 26th 07 07:20 PM
How to copy&paste a variable range rows and colums IK Excel Discussion (Misc queries) 1 August 30th 06 12:06 AM
Macro to copy, paste in a range and then delete Garry Excel Discussion (Misc queries) 0 March 23rd 06 07:37 PM
Copy/Paste using a macro that identifies occuoied range JorgeAE Setting up and Configuration of Excel 1 March 13th 06 12:51 AM


All times are GMT +1. The time now is 03:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement