Paste data under existing data

[Pam Brenny]

I have a simple form where I enter data into columns. I
need to copy this data and transpose it and paste it under
existing data in a spreadsheet. I can't get my macro to
add this copied data in the next blank row under existing
data - it keeps pasting to the same row and overwriting
data. I would appreciate any assistance with this.

Pam

 

[Don Guillett]

Since you didn't post your macro, maybe .offset(1) or row+1 would help

 

[Pam Brenny]

Sorry - Here's the macro.

Range("B3:C36").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A16").Select
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Range("A18").Select
Sheets("Sheet1").Select
Range("B3:C36").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B3").Select
End Sub

Where would I enter your first suggestion in here?

Thanks - Pam

 

[Don Guillett]

OK. Let's ask a couple of questions. Since you didn't explain exactly what
you are tyring to do.
I assume that you want to MOVE (as opposed to COPY) b3:c36 from sheet1 to
the next available row of col A on sheet2.
However, you also want all of the formatting, formulas, etc. OR, do you just
want the values without fomatting and formulas.
Then, do you want to do the same thing with another range?

 

[Pam Brenny]

I am entering information into cell range B3:C36 on Sheet
1. I then need to copy this information, transpose it
from columns to rows, and paste it into the next blank row
in Column A of Sheet 2. I want to copy and paste the
values and formatting; there are no formulas. Once the
data is pasted, I am then clearing the values from B3:C36
on Sheet 1 so that I can enter new information into the
blank range and repeat this process.

I hope this supplies you with enough information. Thanks
for all of your help.

Pam

 

[Don Guillett]

try this
Sub CopyTranspose()
x = Sheets("sheet2").Cells(Rows.Count, "a").End(xlUp).Row + 1
Range("B3:C36").Copy
Sheets("sheet2").Cells(x, "a").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range("b3:c36").ClearContents
End Sub