VBA coding issues

[Novicesportsperformer]

Hi All,

Wondering if you could help me out - I am completely brand new to VBA.

I am trying to get specific data to automatically transfer into another worksheet table.
I keep getting a 1004 error on the code

It would be really great if you guys could help me on this one, thanks!

The code is as follows:

Sub add_to_database()
Dim DR As Long 'DR stands for database row - the in the DB where the program will be pasted into
Dim Database As Worksheet
Dim Template As Worksheet
Dim Proname As String
Set Database = Sheet6
Set Template = ActiveSheet
Proname = InputBox("Name this program")
'find the first blank row of the database - DR stands for database row
Database.Select
DR = Cells(Rows.Count, 1).End(x1Up).Offsett(1, 0).Row (where it says the error is)
Database.Cells(DR, 1) = Proname
Database.Cells(DR, 2) = Template.Cells(21, 2).Value 'Category 1
Database.Cells(DR, 3) = Template.Cells(27, 2).Value
Database.Cells(DR, 4) = Template.Cells(33, 2).Value
Database.Cells(DR, 5) = Template.Cells(39, 2).Value
Database.Cells(DR, 6) = Template.Cells(45, 2).Value
Database.Cells(DR, 7) = Template.Cells(51, 2).Value
Database.Cells(DR, 8) = Template.Cells(57, 2).Value
Database.Cells(DR, 9) = Template.Cells(63, 2).Value
Database.Cells(DR, 10) = Template.Cells(23, 2).Value 'SetReps 1
Database.Cells(DR, 11) = Template.Cells(29, 2).Value
Database.Cells(DR, 12) = Template.Cells(35, 2).Value
Database.Cells(DR, 13) = Template.Cells(41, 2).Value
Database.Cells(DR, 14) = Template.Cells(47, 2).Value
Database.Cells(DR, 15) = Template.Cells(53, 2).Value
Database.Cells(DR, 16) = Template.Cells(59, 2).Value
Database.Cells(DR, 17) = Template.Cells(65, 2).Value
Database.Cells(DR, 18) = Template.Cells(21, 2).Value 'Exercise 1
Database.Cells(DR, 19) = Template.Cells(27, 2).Value
Database.Cells(DR, 20) = Template.Cells(33, 2).Value
Database.Cells(DR, 21) = Template.Cells(39, 2).Value
Database.Cells(DR, 22) = Template.Cells(45, 2).Value
Database.Cells(DR, 23) = Template.Cells(51, 2).Value
Database.Cells(DR, 24) = Template.Cells(57, 2).Value
Database.Cells(DR, 25) = Template.Cells(63, 2).Value

 

[Norie]

x1Up should be xlUp.

 

[Novicesportsperformer]

Ah! perfect thank you so much for the help!

 

[Novicesportsperformer]

it now says I have a run time error '438' - it highlights the same area as the issue :/

 

[Norie]

Another typo I think, try this.

DR = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

 

[Novicesportsperformer]

Of course! couldn't even see that thank you so much - it works perfectly now !

 

[wideboydixon]

A couple of suggestions if you're interested. If not, just move on

1. You don't have to switch to the database sheet to get the next available row
2. That's a lot of data transfer but there's a pattern and you could reduce the maintenance

Sub add_to_database()

Dim DR As Long 'DR stands for database row - the in the DB where the program will be pasted into
Dim Database As Worksheet
Dim Template As Worksheet
Dim Proname As String
Dim nextCell As Long

' Set up the sheet references
Set Database = Sheet6
Set Template = ActiveSheet

' Get the program name
Proname = InputBox("Name this program")

' Find the first blank row of the database - DR stands for database row
DR = Database.Cells(Database.Rows.Count, 1).End(xlUp).Row + 1

' Now populate each cell on the database sheet
For nextCell = 1 To 25
    Select Case nextCell
        Case 1
            Database.Cells(DR, nextCell).Value = Proname
        Case Is < 10
            Database.Cells(DR, nextCell).Value = Template.Cells(nextCell * 6 + 9, 2).Value
        Case Is < 18
            Database.Cells(DR, nextCell).Value = Template.Cells(nextCell * 6 - 37, 2).Value
        Case Else
            Database.Cells(DR, nextCell).Value = Template.Cells(nextCell * 6 - 87, 2).Value
    End Select
Next nextCell

' Activate the sheet if necessary?
Database.Activate

End Sub

WBD

 

[Novicesportsperformer]

That's brilliant thank you for that!

 

[wideboydixon]

You're welcome