creating database help please!

  • Thread starter Thread starter deemo85
  • Start date Start date
D

deemo85

Hi!

I would like to create an entry form, where the user inputs data into cells
in a column format, lets say from F5 to F8 in sheet1, and then these get
copied to sheet2 into a row format from lets say A1 to D1 respectively. So
where F5 gets copied to A1 then F6 to B1 etc etc. And then the next time I
enter data in sheet1 and run the macro I want the data to fill the next row
in sheet2, for example, from F5 to A2, F6 to B2 etc etc

THANK YOU!
 
This macro should do the trick for you.

To put the macro into your workbook: open the workbook, press [Alt]+[F11]
and then choose Insert --> Module and copy and paste the code below into the
module and close the VB Editor.

You may want to attach the macro to a button or shape for easier use, and
there are several sites that will tell you how to do that, as:
http://www.ozgrid.com/Excel/assign-macros.htm
http://www.mrexcel.com/tip068.shtml

Sub CopyAndTranspose()
Dim destWS As Worksheet
Dim destRange As Range
Dim copyRange As Range

Set copyRange = Sheets("Sheet1").Range("F5:F8")
Set destWS = Sheets("Sheet2")
Set destRange = destWS.Range("A" & Rows.Count) _
.End(xlUp)
If Not IsEmpty(destRange) Then
Set destRange = destRange.Offset(1, 0)
End If
copyRange.Copy
destRange.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub
 
THANK YOU SO MUCH!!!! IT IS PERFECT!

JLatham said:
This macro should do the trick for you.

To put the macro into your workbook: open the workbook, press [Alt]+[F11]
and then choose Insert --> Module and copy and paste the code below into the
module and close the VB Editor.

You may want to attach the macro to a button or shape for easier use, and
there are several sites that will tell you how to do that, as:
http://www.ozgrid.com/Excel/assign-macros.htm
http://www.mrexcel.com/tip068.shtml

Sub CopyAndTranspose()
Dim destWS As Worksheet
Dim destRange As Range
Dim copyRange As Range

Set copyRange = Sheets("Sheet1").Range("F5:F8")
Set destWS = Sheets("Sheet2")
Set destRange = destWS.Range("A" & Rows.Count) _
.End(xlUp)
If Not IsEmpty(destRange) Then
Set destRange = destRange.Offset(1, 0)
End If
copyRange.Copy
destRange.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub


deemo85 said:
Hi!

I would like to create an entry form, where the user inputs data into cells
in a column format, lets say from F5 to F8 in sheet1, and then these get
copied to sheet2 into a row format from lets say A1 to D1 respectively. So
where F5 gets copied to A1 then F6 to B1 etc etc. And then the next time I
enter data in sheet1 and run the macro I want the data to fill the next row
in sheet2, for example, from F5 to A2, F6 to B2 etc etc

THANK YOU!
Click to expand...
Click to expand...
 
Glad I could help.

deemo85 said:
THANK YOU SO MUCH!!!! IT IS PERFECT!

JLatham said:
This macro should do the trick for you.

To put the macro into your workbook: open the workbook, press [Alt]+[F11]
and then choose Insert --> Module and copy and paste the code below into the
module and close the VB Editor.

You may want to attach the macro to a button or shape for easier use, and
there are several sites that will tell you how to do that, as:
http://www.ozgrid.com/Excel/assign-macros.htm
http://www.mrexcel.com/tip068.shtml

Sub CopyAndTranspose()
Dim destWS As Worksheet
Dim destRange As Range
Dim copyRange As Range

Set copyRange = Sheets("Sheet1").Range("F5:F8")
Set destWS = Sheets("Sheet2")
Set destRange = destWS.Range("A" & Rows.Count) _
.End(xlUp)
If Not IsEmpty(destRange) Then
Set destRange = destRange.Offset(1, 0)
End If
copyRange.Copy
destRange.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub


deemo85 said:
Hi!

I would like to create an entry form, where the user inputs data into cells
in a column format, lets say from F5 to F8 in sheet1, and then these get
copied to sheet2 into a row format from lets say A1 to D1 respectively. So
where F5 gets copied to A1 then F6 to B1 etc etc. And then the next time I
enter data in sheet1 and run the macro I want the data to fill the next row
in sheet2, for example, from F5 to A2, F6 to B2 etc etc

THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top