Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to stop formulas from incrementing when you copy and paste? | Excel Worksheet Functions | |||
formulae to copy cells from 1 worksheet and paste into another? | New Users to Excel | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) | |||
Copy-Paste sheets - error in the dates | Excel Worksheet Functions | |||
copy from one sheet and paste into other sheets | Excel Discussion (Misc queries) |