Search : in
By :

Copying data from one Excel sheet to another.

Last answer on Oct 29, 2009 11:03:47 pm GMT Remedial, on May 8, 2009 9:22:30 pm BST 
 Report this message to moderators

Hello,

Newbie here.

Okay, I have an Excel workbook that houses a form on the first sheet. What I'm trying to do is to automatically copy the data entered into the rows that are housing the inputted data into a separate sheet that will be used as a database. The database will maintain all of the various entries that have been made into the form for the various clients.

I was able to find an answer on Allexperts that basically delineates everything that I'm trying to do.

Here's the link:

http://en.allexperts.com/q/Excel-1059/2009/2/Excel-2153.htm

I don't know how to create a shadow form, but I'm sure that's nothing a quick google search can't solve. I'd also like to create the SUBMIT button macro mentioned on the page.

The only other thing that I would like to do with my form that is not covered on the Allexperts page is to limit data entry to "specific" cells so that those entering data won't enter it into the wrong cells.

Any guidance or assistance in creating any of these items (shadow form, Submit button macro, dynamic row pointer) would be great.

I'll let you guys know what I can find.

One question, though:

Should the database include separate columns with headings for each of the rows within which data will be entered?

Thanks.

Configuration: Windows XP
Firefox 3.0.10

Best answers for « Copying data from one Excel sheet to another. » in :
How to freeze a row in an excel sheet ShowHow to freeze a row in an excel sheet To freeze a line in an excel sheet, for example line 1: Select line 2. Go to menu Window and select Freeze panes.
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Create an Attendance Report with Excel sheet ShowCreate an Attendance Report with Excel sheet Issue Solution Issue In the case you want to create an attendance report with Excel , here below you will find a nice example: Consider that your report will have the following...
Ask your question Reply

1

Excelguru, on May 10, 2009 5:56:40 am BST
  • +10

To create form go to visual basic editor (ALT + F11) >>Insert>> Userform
find how to fill controls (text boxes, labels, buttons) into a userform by searching in google
When userform is created and make a macro to show that userform
on clicking the submit button on the form,
find the lastrow in the database sheet (use google) , get all the field values to each cell in the last row
eg. sheets("Database").cells(lastrow+1,1).value=userform1.textbox1.text
sheets("Database").cells(lastrow+1,2).value=userform1.textbox2.text
etc Winners are losers who got up and gave it one more try. -Dennis DeYoung

   
Reply to Excelguru

2

Antohere, on Aug 12, 2009 4:56:56 am BST
  • +1

Dim total as Long

total = thissheet.range("A" & 1).value + thissheet.range("A" & 1).value + thissheet.range("B" & 1).value + _
thissheet.range("C" & 1).value

Now I would like to copy this "total" to thissheet.range("E" & 1) 'total of A B C to E

Any help on this.

Regards,
Herald A.

   
Reply to Antohere

3

antohere, on Aug 12, 2009 5:08:40 am BST
  • +2

Dim total as Long

total = thissheet.range("A" & 1).value + thissheet.range("A" & 1).value + thissheet.range("B" & 1).value + _
thissheet.range("C" & 1).value

Now I would like to copy this "total" to thissheet.range("E" & 1) 'total of A B C to E

Any help on this.

Regards,
Herald A.

   
Reply to antohere

4

Excelguru, on Aug 12, 2009 6:39:03 am BST

Change thissheet. to activesheet and try Winners are losers who got up and gave it one more try. -Den­nis DeYoung
My Interests are financial Modelling and custom excel develo­pment.

   
Reply to Excelguru

5

dren, on Aug 13, 2009 11:27:36 am BST
  • +1

If possible, could you please share the excel template (final or draft) for this requirement if you already have made one ?
It would be very much appreciated. Thanks very much.

   
Reply to dren

6

AntoHere, on Aug 13, 2009 12:22:37 pm BST
  • +2

Hi

Sure, I can share and explain the requirement in detail, pls give me your email id.

Regards,
herald A.

   
Reply to AntoHere

7

dren, on Aug 13, 2009 12:54:57 pm BST

My mail id is gajjs@yahoo.com.
Just to clarify a summary of my requirements,
1. fill out a form for a given customer and press submit button.
2. copy that form data into an adjacent (preferably hidded or pass protected) excel tab (master database) sheet.
3. then re-use the form for the next customer.

Thanks very much Herald.

   
Reply to dren

14

Miss1975, on Aug 31, 2009 10:14:37 am BST

Hello,

Can i please have a copy of this templat, is just what im looking for.
Thanku
M

   
Reply to Miss1975

29

VBnewbie, on Oct 28, 2009 5:09:25 pm GMT

I also would appreciate a copy of the template for this as it is exactly what I've been wracking my brain on for the last 2 months! Thanks much!

   
Reply to VBnewbie

30

 Corrinne, on Oct 29, 2009 8:32:05 pm GMT

Would it be possible for you to send me that same spreadsheet?

Thanks
Corrinne

   
Reply to Corrinne

8

Matt, on Aug 13, 2009 6:21:26 pm BST

I am also searching for this exact template. Please copy me into the email. disaster555pieces666@yahoo.com

   
Reply to Matt

9

binu, on Aug 19, 2009 10:49:53 pm BST

Some body pls send me a copy of the excel file,so that i will be helped
binukalarickan@gmail.com

   
Reply to binu

10

geoffs87, on Aug 23, 2009 4:58:06 pm BST

HI i need to do something like this for a College class and i have no idea how to do it!, please can someone email me a copy of this code to afterpartyshirts@gmail.com

Thanks in advance!
Geoffrey

   
Reply to geoffs87

11

Antohere, on Aug 24, 2009 7:48:30 am BST

Hi Geoffrey,

I'm working on this code, I will share the code ASAP on this blog.

Regards,
Herald A.

   
Reply to Antohere

12

ckdiggy, on Aug 24, 2009 1:02:43 pm BST
  • +1

Hi There

This is exactly what I am looking for to complete a project...

If poss, could you send template/code to ckdiggy@msn.com also.

Many Thanks

Ross

   
Reply to ckdiggy

26

Antohere, on Oct 20, 2009 12:09:43 pm BST
  • +1

Hi Ross,

Sorry for the late reply, appologies that I couldnt reply to any of the emails, coz I was posted to another country on work ( 3~4 months) and high priority work.

I read all your emails, I will post the draft copy of this excel this evening and by end of this month I will upload the completed version of this excel.

Also, can anyone help me how to upload the excel on this web site (i mean steps or procedure).

I would like to do this coz I got initial help from this site, sure this will help others too.

Regards,
Herald A.
antony.herald@gmail.com

   
Reply to Antohere

27

Antohere, on Oct 20, 2009 12:10:53 pm BST

Hi Ross,

Sorry for the late reply, apologies, that I couldnt reply to any of the emails, coz I was posted to another country on work ( 3~4 months) and high priority work.

I read all your emails, I will post the draft copy of this excel this evening and by end of this month I will upload the completed version of this excel.

Also, can anyone help me how to upload the excel on this web site (i mean steps or procedure).

I would like to do this coz I got initial help from this site, sure this will help others too.

Regards,
Herald A.
antony.herald@gmail.com

   
Reply to Antohere

13

jfftilton, on Aug 28, 2009 6:13:26 pm BST

I'm trying to do something similar as well for a gardening project where I need data entered in one page and moved to an array of other pages containing specific data from the page entered, I would love the template and advice if this template could be modified as such.
EX: bed a we harvested 5 lbs squash I need that info to jump to another page just pertaining to bed A.

My email for the template is jfftilton@yahoo.com
Thanks in advance

   
Reply to jfftilton

15

Mikel, on Sep 3, 2009 12:25:35 pm BST

All,

I'm looking for similar type macro. I'm brand new on this.

Scenario: I have set o data with a unique set of data in column A, Also, same coloum A has multiple data set as well. A good example: Manager 1 has 10 employee( first name, last, Address, Phone …..) Manager 2-- the same. I would like to create a macro that can create every manager into their own worksheet or a workbook. I really need help as I have no Idea where to start.
Any help will be appreciated.
Thanks,
Mikel

   
Reply to Mikel

16

crashingin, on Sep 7, 2009 2:53:20 pm BST

Hi
I would be grateful if you could also email me this code. It is exactly what I am looking for! I am a complete VB novice. jhnwdrw@googlemail.com

Many thanks

   
Reply to crashingin

17

lostinexcel, on Sep 9, 2009 7:45:48 pm BST

Similarly, does anyone know how to take the data from one cell, and transfer across different sheets?

For example: copy name, address, phone number to multiple to different sheets and differnent cells in those sheets?

   
Reply to lostinexcel

18

Mikel, on Sep 15, 2009 3:07:31 pm BST

Still need help on this... any expert out there? I'm able to group data. but not able to copy into indivudual spreadsheets and/or Workbooks. Please help

Thanks,

   
Reply to Mikel

19

geoffs87, on Sep 17, 2009 12:51:35 am BST

Ok so after many weeks of fiddling about i finally developed a macro that you tag to a click button (from forms) right click and assign macro. Then paste this into the VB field:

Sub Button1_Click()
Worksheets("Analysis").Range("I4").Value = Worksheets("Amortization").Range("D4")
Worksheets("Amort_Data").Range("N2").Value = Worksheets("Amortization").Range("L9")
Worksheets("Analysis").Range("I6").Value = Worksheets("Amortization").Range("D14")
Worksheets("Amort_Data").Range("P2").Value = Worksheets("Amortization").Range("L10")
End Sub


Using the second line as an example, once the button is clicked it will copy data in cell D$ from my "Amortization" sheet to cell I4 on my "Analysis" sheet. ie if you haven't changed sheet names you would be copying from sheet 1 to sheet 2. For each different cell that you wish to copy to another sheet, you must enter a new line. In my program i have created i have 1 click button that copies 18 variables from one sheet to another. these all have to be entered manually. This works perfectly for me as i have a Loan Amortization on sheet 1 and i wish to copy data outputs from sheet 1 to sheet 2. I have 8 different buttons on my first sheet that perform different actions.

If you need more help please reply to my post here!

Hope this helps
Geoffrey

   
Reply to geoffs87

25

shyam, on Oct 17, 2009 9:46:45 am BST

Hi
I also need exactly this kind of program made in excel.
I have my cash cards selling shop that deals with about 100 customers. I want to create a data entry form in one excel sheet and store the data as databse in same or another sheet.
Could u please send me a wizard or a model excel file for this.
I would be greatful to you.
Thank you

   
Reply to shyam

20

Anshu, on Sep 25, 2009 4:13:04 pm BST

Do you have a sample of the above discussion, if yes can u plz email me at ansh.goyal@gmail.com

   
Reply to Anshu

21

Geoffs87, on Sep 27, 2009 11:54:25 pm BST
  • +1

Sorry mate... this program i constructed was alot of work and not for distribution. i can help with creating your own if you need.

   
Reply to Geoffs87

22

LaughingOutLoudBroham, on Sep 30, 2009 1:29:50 am BST

Alot of work? You are kidding me right? That's basic VB. Grow up and stop trying to hustle people into paying for your B$ excel work. A lot of work..

i'm still laughing.

   
Reply to LaughingOutLoudBroham

23

Geoffs87, on Oct 8, 2009 3:16:10 pm BST

Im so glad your laughing... yes the one code was not much work "broham" however the other 11 sheets, 32 pieces of code and well i think its 1600 cells of inputed data i had to manually enter... yeah not much work for someone who never used excel before...

Grow up and learn not to bag someone for things they are TRYING to learn themselves, WITHOUT first knowing what they went to to get there.

Oh and i don't sell things people can get for free buddy. It's a University assignment, and i don't much feel like distributing something freely after ive spent about 3 months of sleepless nights on.

Go play World of warcraft if your such a nerd and can do this in your sleep. keep "loling" mate.

Stay Tough

Geoffrey

   
Reply to Geoffs87

28

GrievousAngel, on Oct 24, 2009 7:28:50 pm BST

I have a similar need: map one db so it aligns with the template db. the fields are similar but in different order.

if you have teh code that does this please link me to it.

thanks,
billy

   
Reply to GrievousAngel
30 answers 12 Next
Ask your question Reply
They need your help