Copying data from one worksheet to another using macro

[pixel1987]

so im ripping my. hair out here..

i have data which is in column format (data in cell, space, data in cell, space) and trying to copy that into another worksheet in row format using a macro to transfer the data across

ive read and studied and searched all over and just cant get my code to work. (allways the yellow line highlighting the sub or the copy code)

please if someone can spare their time (will to pay for it as im headbutting a wall)

just have some work done and think i swallowed a pill to big.


i know im close but 2 days now and need someone that can help..

soo... whats the code??



sub database_transfer ()

Dim Form As Worksheet
dim database as worksheet

Set Form = ThisWorkbook.range ("e7, e9, e11, e15")

'moving col down row c until next empty row on database.

icurrentrow = form.range ("C").rows.count

'trans form data to database

.Cells(iCurrentRow, 1) = iCurrentRow - 2

.Cells(iCurrentRow, 2) = shForm.Range("H7")

.Cells(iCurrentRow, 3) = shForm.Range("H9")

.Cells(iCurrentRow, 4) = shForm.Range("H11")

.Cells(iCurrentRow, 5) = shForm.Range("H15")


(THEN THE CODE I HAVE TO CLEAR THE FORM AND MESSAGE BOX COME UP FUNNY ENOUGH WORKS)

end sub

 

[Michael M]

Well, you aren't really close at this stage....
1. there is no sheet reference on this ine

ThisWorkbook.Range("e7, e9, e11, e15")

2. You are using a period at the start of the line, but haven't used a With ..End with statement

.Cells(icurrentrow, 1) = icurrentrow - 2

3. What is "shForm" it isn't declated anywhere.....do you mean the sheet named form ??...Form.Range("H7")

 

[pixel1987]

LINK >>

resource >> Submit Button / Copying Fields to Another Sheet

This is essentially what i was copying but trying to tweak it a bit. this guy has made what im after basically buy i dont require the country worksheets . just copying on to one workeet called database( in the pic) i like the idea entering details on a form and all getting pasted onto a table format to which i can do other things with. the plan here is to copy whats entered on the form to the database and clears the form then when another person fills in its it will be pasted onto another new line on the database. .. to start with anyways



soo.. ive taken out
Dim iCurrentRow As Integer

Dim sCountryName As String ... and a few other things.. i did manage to get it work but it didnt save.. kicking my self



Sub Submit_Details()

Dim shCountry As Worksheet
Dim shForm As Worksheet

Dim iCurrentRow As Integer

Dim sCountryName As String

Set shForm = ThisWorkbook.Sheets("Form")

sCountryName = shForm.Range("H11").Value

Set shCountry = ThisWorkbook.Sheets(sCountryName)

iCurrentRow = shCountry.Range("A" & Application.Rows.Count).End(xlUp).Row + 1

With shCountry


.Cells(iCurrentRow, 1) = iCurrentRow - 1

.Cells(iCurrentRow, 2) = shForm.Range("H7")

.Cells(iCurrentRow, 3) = shForm.Range("H9")

.Cells(iCurrentRow, 4) = shForm.Range("H11")

.Cells(iCurrentRow, 5) = shForm.Range("H13")

.Cells(iCurrentRow, 6) = shForm.Range("H15")

.Cells(iCurrentRow, 7) = Application.UserName

.Cells(iCurrentRow, 8) = Format([Now()], "DD-MMM-YYYY HH:MM:SS")


End With

shForm.Range("H7, H9, H11, H13, H15").Value = ""

MsgBox "Data submitted successfully!"

End Sub

thanks for replying back

 

[Michael M]

So, something like this ??...Also, in future, please use code tags when posting code....In the Reply toolbar, Select the VBA icon, then paste the code between the tags that appear in the Reply
Change the sheet names to suit your needs !

Sub Submit_Details()
Dim shDatabase As Worksheet, shForm As Worksheet
Dim iCurrentRow As Integer
Set shDatabase = Sheets("Database")
Set shForm = Sheets("Form")
'shDatabase = shForm.Range("H11").Value
iCurrentRow = shDatabase.Range("A" & Application.Rows.Count).End(xlUp).Row + 1
    With shDatabase
    .Cells(iCurrentRow, 1) = iCurrentRow - 1
    .Cells(iCurrentRow, 2) = shForm.Range("H7")
    .Cells(iCurrentRow, 3) = shForm.Range("H9")
    .Cells(iCurrentRow, 4) = shForm.Range("H11")
    .Cells(iCurrentRow, 5) = shForm.Range("H13")
    .Cells(iCurrentRow, 6) = shForm.Range("H15")
    .Cells(iCurrentRow, 7) = Application.UserName
    .Cells(iCurrentRow, 8) = Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End With
shForm.Range("H7, H9, H11, H13, H15").Value = ""
MsgBox "Data submitted successfully!"
End Sub