School project

[jhncloud]

Hey guys, so I have a problem that no matter how much Google and other sources I've checked, I can't seem to find a solution. So long story short I have a project for a business class and we have to make use of VBA in Excel so I choose to make a Hotel Reservation System.

My problem is that I want to save whatever is written on the Interface Sheet to the Data Record Sheet with a button. I tried multiple things but what's happening is that the data is only copied to the first row, Row 6. And when I go back to the Interface Sheet to input new data of a "new guest", it overwrites the first one. I want the new data that I just entered in the next row.

 

[Joe4]

Welcome to the Board!

Please post your code that transfers the data from your form to your data sheet, and we can give you some tips on how to get it to keep adding it to the bottom of the list, instead of on row 6 every time.

 

[jhncloud]

At first I was using this:

Sub TransposeData()
Range("Cells in Interface Sheet").Copy
Range("Cells in Data Record Sheet").PasteSpecial Transpose:=True
End Sub

Here's what I'm using now as a reference. It was from my buddy's project from another class. It didn't work for him either but I thought I could make it work for me, but still no luck.

Sub AddMe()
'declare the variables
Dim Bws As Worksheet
Dim Fws As Worksheet
Dim Dt As Range
Dim Rm As Range
Dim ID As Range
Dim CK As Range
Dim orange As Range
Dim lastrow As Long
Dim nextrow As Range
'turn off screen updating
Application.ScreenUpdating = False
'variables
Set Bws = Sheet2
Set Fws = Sheet4
Set Dt = Bws.Range("V4")
Set Rm = Bws.Range("V3")
Set ID = Fws.Range("B5")
Set CK = Fws.Range("BM6")

'find the next free row
Set nextrow = Fws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
With nextrow
.Offset(0,-1).Value = ID.Value + 1
.Value = Bws.Range("V3").Value
.Offset(0, 1).Value = Bws.Range("V4").Value
.Offset(0, 2).Value = Bws.Range("V5").Value
.Offset(0, 3).Value = Bws.Range("V6").Value
.Offset(0, 4).Value = Bws.Range("V7").Value
.Offset(0, 5).Value = Bws.Range("AE3").Value
.Offset(0, 6).Value = Bws.Range("AE4").Value
.Offset(0, 7).Value = Bws.Range("AE5").Value
.Offset(0, 8).Value = Bws.Range("AE7").Value
.Offset(0, 9).Value = Bws.Range("AN3").Value
.Offset(0, 10).Value = Bws.Range("AN4").Value
.Offset(0, 11).Value = Bws.Range("AN5").Value
.Offset(0, 12).Value = Bws.Range("AN6").Value
.Offset(0, 13).Value = Bws.Range("AN7").Value
.Offset(0, 14).Value = Bws.Range("AZ3").Value
.Offset(0, 15).Value = Bws.Range("BD3").Value
.Offset(0, 16).Value = Bws.Range("AZ4").Value
.Offset(0, 17).Value = Bws.Range("BD4").Value
.Offset(0, 18).Value = Bws.Range("AZ5").Value
.Offset(0, 19).Value = Bws.Range("BD5").Value
.Offset(0, 20).Value = Bws.Range("AZ6").Value
.Offset(0, 21).Value = Bws.Range("BD6").Value
.Offset(0, 22).Value = Bws.Range("AZ7").Value
.Offset(0, 23).Value = Bws.Range("BD7").Value
End With
'run the filter to limit data
FilterRng
'select the bookings sheet
Bws.Select
'run the macro to add the bookings
Bookings
'clear the values
Clearme
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub

 

[Joe4]

When you run it the first time, can you show us what the data record looks like with the first line of data in it?

 

[jhncloud]

It says this:

 

[Joe4]

I thought you said it was working, but only copying over the first line of data:

I tried multiple things but what's happening is that the data is only copied to the first row, Row 6.

When you run it the first time, can you show us what the data record looks like with the first line of data in it?

But now you say you are getting an error?
So, which one is it? Is it working only on the first line, or giving you an error?

If it is only working on the first line, please attach an image showing what the the first line looks like when pasted to your output results, like I asked in the previous post.

If you are getting that error, if you hit the "Debug" button, which line of code does it highlght in yellow?