Copying data from one worksheet to another using macro

pixel1987

New Member
Joined
Mar 16, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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
 

Attachments

  • SSSSSS.jpg
    SSSSSS.jpg
    66.7 KB · Views: 2
  • IMG_5770.jpg
    IMG_5770.jpg
    204.2 KB · Views: 2

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,120
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Well, you aren't really close at this stage....
1. there is no sheet reference on this ine
VBA Code:
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
VBA Code:
.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

New Member
Joined
Mar 16, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,120
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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 !
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,511
Messages
5,636,759
Members
416,938
Latest member
sc58963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top