inserting a varying number of rows and pasting with transpose..

[Tim]

Greetings,

I have a database area of my spreadsheet and a data entry
area. The number of columns of data in the data entry
section varies depending on the raw data. Once the data
has been input into the data entry sheet I have set up
a 'submit' button that I want, when clicked to copy the
data from the data entry into the database. The first
time I tun through this it works fine (via record macro),
of course when I try it a second time it copies over the
previous data. (As in it does not find the real end of
the data.) I can't use the inset cells command as I have
to 'paste special' because the cells copied over to the
data base must be transposed and changed to values.

The code of the macro is below.

Sub SubmitToDatabase()
'
' SubmitToDatabase Macro
' Macro recorded 12/15/2003 by Tim McDonald
'

'
Range("B4:B5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Database").Select
Range("A4").Select
Selection.End(xlDown).Select
Range("A28").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Data Entry").Select
Application.CutCopyMode = False
Range("B1:B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Database").Select
Range("C4").Select
Selection.End(xlDown).Select
Range("C28").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Data Entry").Select
Application.CutCopyMode = False
Range("B8:B9").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Database").Select
Range("E4").Select
Selection.End(xlDown).Select
Range("E28").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Data Entry").Select
Application.CutCopyMode = False
Range("B6:B7").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Database").Select
Range("G4").Select
Selection.End(xlDown).Select
Range("G28").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Data Entry").Select
Application.CutCopyMode = False
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Database").Select
Range("I4").Select
Selection.End(xlDown).Select
Range("I28").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Range("A4").Select
Sheets("Data Entry").Select
Application.CutCopyMode = False
Range("B10").Select
End Sub

 

[Bernie Deitrick]

Tim,

Every place where you have

Selection.End(xlDown).Select
Range("?28").Select

Change it to

Selection.End(xlDown)(2).Select

HTH,
Bernie
MS Excel MVP