Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probelme with coding...
ok, here's what i got.
this subscript basically takes data from one sheet and pastes it t another sheet. However, it keeps overwriting the first block in cell in the new shee "A1" How do i get it to drop down after the first loop to b1...the c1...then d1 etc. etc. Thanks...here is my code.. V/R Jody Sub transpose() ' ' 'Loop for Transposing Pivot Table to create spreadsheet for VLOOKUP ActiveSheet.Select Range("A5").Select Do While ActiveCell.Value < "" Selection.Copy Sheets("Sheet3").Select Range("A1").Select ActiveSheet.Paste Sheets("Sheet4").Select ActiveCell.Offset(0, 2).Select Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(11, 0)).Select Selection.Copy Sheets("Sheet3").Select Range("a1").Select 'I know this is where my code is? ActiveCell.Offset(0, 1).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone SkipBlanks:=False _ , transpose:=True Sheets("Sheet4").Select ActiveCell.Offset(0, 1).Select Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(11, 0)).Select Selection.Copy Sheets("Sheet3").Select ActiveCell.Offset(0, 12).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone SkipBlanks:=False _ , transpose:=True Sheets("Sheet4").Select ActiveCell.Offset(0, 1).Select Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(11, 0)).Select Selection.Copy Sheets("Sheet3").Select ActiveCell.Offset(0, 12).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone SkipBlanks:=False _ , transpose:=True ActiveCell.Select ActiveCell.Offset(1, -25).Select Sheets("Sheet4").Select ActiveCell.Offset(12, -4).Select Loo -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Probelme with coding...
Hi Jody,
Try banishing all the selects. You virtually never need to .Select anything in order to use it. This little code snippet copies all non-blank cells from A1:A20 into a contiguous range in another sheet. Perhaps it will give you some ideas: Sub A() Dim rngSrc As Range, rngDest As Range Set rngSrc = ThisWorkbook.Worksheets(1).Range("A1") Set rngDest = ThisWorkbook.Worksheets(2).Range("A1") Do While rngSrc.Row <= 20 If Not IsEmpty(rngSrc.Value) Then rngDest.Value = rngSrc.Value Set rngDest = rngDest.Offset(1, 0) End If Set rngSrc = rngSrc.Offset(1, 0) Loop End Sub HTH Peter Beach "jody.mckinzie " wrote in message ... ok, here's what i got. this subscript basically takes data from one sheet and pastes it to another sheet. However, it keeps overwriting the first block in cell in the new sheet "A1" How do i get it to drop down after the first loop to b1...then c1...then d1 etc. etc. Thanks...here is my code.. V/R Jody Sub transpose() ' ' 'Loop for Transposing Pivot Table to create spreadsheet for VLOOKUP ActiveSheet.Select Range("A5").Select Do While ActiveCell.Value < "" Selection.Copy Sheets("Sheet3").Select Range("A1").Select ActiveSheet.Paste Sheets("Sheet4").Select ActiveCell.Offset(0, 2).Select Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(11, 0)).Select Selection.Copy Sheets("Sheet3").Select Range("a1").Select 'I know this is where my code is? ActiveCell.Offset(0, 1).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False _ , transpose:=True Sheets("Sheet4").Select ActiveCell.Offset(0, 1).Select Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(11, 0)).Select Selection.Copy Sheets("Sheet3").Select ActiveCell.Offset(0, 12).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False _ , transpose:=True Sheets("Sheet4").Select ActiveCell.Offset(0, 1).Select Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(11, 0)).Select Selection.Copy Sheets("Sheet3").Select ActiveCell.Offset(0, 12).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False _ , transpose:=True ActiveCell.Select ActiveCell.Offset(1, -25).Select Sheets("Sheet4").Select ActiveCell.Offset(12, -4).Select Loop --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with coding | New Users to Excel | |||
sum by coding without 0 | New Users to Excel | |||
How to fix the coding? | Excel Worksheet Functions | |||
How to fix the coding? | Excel Worksheet Functions | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming |