Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Copy cells from one worksheet and paste to incrementing sheets?

Hi,

I am slightly familiar with excel, macros and visual basic but would
really like a quick fix for this problem. I want to take a range of
cells that look like this:

Ex.1 Ex.2 Ex.3
2007-02-09 1 4 1
2007-02-10 3 3 3
2007-02-11 5 2 4
2007-02-12 4 7 7
2007-02-13 7 9 5
2007-02-14 3 8 9
2007-02-15 2 6 8

I then want to be able to take the first row (i.e. 1,4,1) and copy it
to the next sheet in my workbook but values only and transposed, which
ive already figured out as:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

In the same macro i then want to take the second row (3,3,3) and copy
it to the sheet after the one I just pasted to and carry on down the
list until I hit blank.

Any ideas?


Help much appreciated :)

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default Copy cells from one worksheet and paste to incrementing sheets?

One way:

Public Sub TransposeAndCopy()
Const nCOLS As Long = 4
Dim rCell As Range
Dim i As Long
Dim nMaxSheets As Long


With ActiveSheet
nMaxSheets = .Parent.Worksheets.Count
i = .Index + 1
For Each rCell In .Range("A1:A" & _
.Range("A" & .Rows.Count).End(xlUp).Row)
Worksheets(i).Cells(1).Resize(nCOLS, 1).Value = _
Application.Transpose(rCell.Resize(1, nCOLS).Value)
i = i + 1
If i nMaxSheets Then Exit For
Next rCell
End With
End Sub


In article . com,
wrote:

Hi,

I am slightly familiar with excel, macros and visual basic but would
really like a quick fix for this problem. I want to take a range of
cells that look like this:

Ex.1 Ex.2 Ex.3
2007-02-09 1 4 1
2007-02-10 3 3 3
2007-02-11 5 2 4
2007-02-12 4 7 7
2007-02-13 7 9 5
2007-02-14 3 8 9
2007-02-15 2 6 8

I then want to be able to take the first row (i.e. 1,4,1) and copy it
to the next sheet in my workbook but values only and transposed, which
ive already figured out as:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

In the same macro i then want to take the second row (3,3,3) and copy
it to the sheet after the one I just pasted to and carry on down the
list until I hit blank.

Any ideas?


Help much appreciated :)

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default Copy cells from one worksheet and paste to incrementing sheets?

You could use a formula: asuming your data starts in row 1, column B (column
A is date?) and starting in A1 on sheet2:

=IF(OFFSET(Sheet1!$B$1,INT((ROW()-1)/3),MOD(ROW()-1,3))<"",OFFSET(Sheet1!$B$1,INT((ROW()-1)/3),MOD(ROW()-1,3)),"")

and copy down.

Then highlight data, COPY, Paste Special=Values.

HTH

" wrote:

Hi,

I am slightly familiar with excel, macros and visual basic but would
really like a quick fix for this problem. I want to take a range of
cells that look like this:

Ex.1 Ex.2 Ex.3
2007-02-09 1 4 1
2007-02-10 3 3 3
2007-02-11 5 2 4
2007-02-12 4 7 7
2007-02-13 7 9 5
2007-02-14 3 8 9
2007-02-15 2 6 8

I then want to be able to take the first row (i.e. 1,4,1) and copy it
to the next sheet in my workbook but values only and transposed, which
ive already figured out as:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

In the same macro i then want to take the second row (3,3,3) and copy
it to the sheet after the one I just pasted to and carry on down the
list until I hit blank.

Any ideas?


Help much appreciated :)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
How to stop formulas from incrementing when you copy and paste? Kirkwill Excel Worksheet Functions 2 May 10th 23 07:45 PM
formulae to copy cells from 1 worksheet and paste into another? MikeR-Oz New Users to Excel 8 March 23rd 06 12:40 PM
Auto "copy and paste" individual cells from various sheets into one sheet ?? [email protected] Excel Discussion (Misc queries) 2 March 1st 06 04:19 AM
Copy-Paste sheets - error in the dates Aninha Excel Worksheet Functions 1 February 28th 06 05:04 PM
copy from one sheet and paste into other sheets TUNGANA KURMA RAJU Excel Discussion (Misc queries) 3 December 8th 05 03:49 PM


All times are GMT +1. The time now is 04:43 PM.

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

About Us

"It's about Microsoft Excel"