Probelme with coding...

[jody.mckinzie]

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

 

[Peter Beach]

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