+ Reply to Thread
Results 1 to 25 of 25

Copy data using macro

  1. #1
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Copy data using macro

    Hi,

    I need a macro code which help me in copy the data from one sheet and past it into another sheet.

    I've two excel (2013) sheet. One is sheet 1 and another is sheet 2.

    In sheet 2 there is data which I want to copy and paste it into the sheet 1.

    I want to create a button and asign a macro code which help me to do this.

    whenever I hit that button macro copy the next available data from the sheet 2, column a and paste it into sheet 1 cell a1.


    Hope I'm clear about my query

  2. #2
    Registered User
    Join Date
    10-14-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2003, 2013
    Posts
    25

    Re: Copy data using macro

    with the next available data, are you wanting to loop through an existing set of data one at a time until you get to the end, or do you just mean the last item in column A of sheet 2.

  3. #3
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    I'm attaching my excel file for which I want that macro.

    My first sheet is for user and second sheet for the data base.

    there is one button which is macro enabled, when I hit that button it generate the continues number (number shows in cell C14 in sheet 1) and copy all the available data in sheet 1 and paste it into the second sheet as per the defined column.(you can see the macro code).

    Now what I want it if you modify that code which help me in, when I hit that button it works same as pervious and also copy the current data available in column K of sheet 2 and paste it into sheet 1 cell C14 as a reference number. Column K in sheet 2 is containing concetenate formula. I want only the value in sheet 1, cell c14.

    Hope I'm clear.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    Currently, this line

    Please Login or Register  to view this content.
    Copies in to C14.
    When you say column K, which row,2,3,4,etc.?

  5. #5
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    currently macro generate number in sheet 1 cell c14 and paste all the data into sheet 2 in continues manner.

    so what I want is when I hit the button macro run as same and additionaly it copy the data from the column k of sheet 2 against the generated number and paste it into cell C15 of sheet 1.

    means if macro generate the number 5 then same time it copy the data from column k of sheet 2 against the generated number 5 and paste it into cell C15.

    same next when I hit the button it generate the number 6 copy the data from column K of sheet 2 against the generated number 6 and paste it into cell C15.

    so in sheet one cell is fixed for both. cell C14 for number generation and C15 for data pasting from sheet 2

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    But which row in column K? You have 16 rows right now. How do you decide which row of the 16 you want to copy to C15?

  7. #7
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    Quote Originally Posted by AB33 View Post
    But which row in column K? You have 16 rows right now. How do you decide which row of the 16 you want to copy to C15?

    when I hit the button it generate the number 1 in sheet 1 cell C 14 and same time it copy all the data from sheet 1 and paste it into sheet 2 row 2.

    when I hit the button again it generate the next number (say 2) in cell C14 and same time it copy all the available data from sheet 1 and paste it into sheet 2 row 3

    again when I hit the button it generate the next number (say 3) in cell C14 and same time it copy all the available data from sheet 1 and paste it into sheet 2 row 4

    so what I want it works same as what it did and additionaly it copy the data from column k against the currently generated number and paste it into sheet 1 cell C15.

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    You still have not answered my question. I understand what the code does.

    Please Login or Register  to view this content.
    You have a fixed cells range in sheet2 and copy them in to sheet1, but if you were to do the same for column K, which row in column K do you want to copy in to? Is it K5, K6, K15, like C5,C6?
    Last edited by AB33; 10-17-2013 at 04:12 AM.

  9. #9
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    with my code number generate in sheet1 cell 14. this is fixed.
    see when i rum my macro, it generate the number in cell 14 (say generate number 1) and copy all the data and paste it into second sheet row 2.

    again when I run my macro it generate the next number (say 2) and copy all the data and paste it into next row (say row 3) and so on.

    this is clear.

    in sheet 2 column K I put one formula concatenate which combine the data(which you can see in sheet 2)

    now wat I want when I run the macro it generate the next number (say 3) in sheet 1 cell 14 copy the available data from sheet 1 and paste it into sheet 2 next row (say row 4)(this is what the present macro do)(now wat I want/modification in present macro) additionaly it copy the data from sheet 2 column k against the generated number (say my new generated number is 3, data pasted into row 4 of sheet 2) "so the desired cell is K4" and paste the value in cell c15 of sheet 1

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    thanx AB33 this code works fine but I'm not getting the desired result.

    when I run the code first time it generate the number "1" in cell C14 of sheet 1 and copy the entire data and paste it into the next sheet row 2 but the problem is it copy the data of K1 which is "Ref. No." and paste it into the cell C15 of Sheet 1.

    again when I run this macro it generate the number "2" copy the entire data and paste it into the next sheet row 3 and also copy the data of K2 which is "x/IT/c/13-14/1" means it copy the previous Cell of K column and paste it into the cell C15 of sheet 1.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    If you only have a header, then the code will skip that row in column K. I have also changed the code to copy in to next empty row in C column.

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    no sir,

    its not like this. C15 is fixed. like whenever I run the macro it generate the number for that the cell is fixed C14.

    what I want is macro copy the data from column k against the new number.

    means if the generated number is 1 then it copy the data from cell K2 of sheet 2 and paste it into cell C15 of sheet 1
    if the generated number is 2 then it copy the dta from cell K3 of sheet 2 and paste it into Cell C15 of sheet 1

    in sheet 1 Cell 14 and cell 15 is fixed.

  14. #14
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    thank you very much AB33 now it works perfectly fine.

  16. #16
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    one more help.

    is it possible for save the second sheet for data deletion.

  17. #17
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    what do you mean by data deletion? Please explain or show me an example.

  18. #18
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    see my second sheet is my databank. so I protect all the data from the deletion so that no one delete the data which is available in sheet 2

  19. #19
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    I do not know why the code works, but when I close and re-open it, I keep getting an error.
    I use the word "Test" as a password.

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    thanks, its working fine. its working as I want, thanks a lot

  21. #21
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    one more help.

    is it possible that when I close this file and reopen. all the data remove from sheet 1 except the heading. so that whenever the new user open this file they got fresh.

  22. #22
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    One more help is leading to another one more help.

    Put this code in this workbook.

    Please Login or Register  to view this content.

  23. #23
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    this code delete all the data, is it possible that we select the cell which we want empty in next open.

  24. #24
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy data using macro

    You said,

    "sheet 1 except the heading"

    May be you want sheet2, so change sheet1 in to sheet2

  25. #25
    Registered User
    Join Date
    03-05-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Copy data using macro

    with this code my sheet is ptotect but not with password. anyone can open it by clicking on the option unprotect in Review tab.

    is it possible that when I run the macro it unprotect the sheet then copy & paste data as what I want and again protect the second sheet with password.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro that can filter excel data and copy the filtered data to another worksheet
    By glide2131 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2013, 02:43 PM
  2. Macro to copy row data into new worksheets and separate delimited data
    By asuguy83 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-25-2012, 08:11 PM
  3. Macro to copy Dyanmic lists then copy sheet and clear the original data
    By twiggywales in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2012, 01:06 PM
  4. [SOLVED] Macro to Copy/Paste Data down to where data in another column ends
    By mclark13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2012, 04:02 PM
  5. [SOLVED] Macro to search cell data and copy data to another workbook
    By ucsutah in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-11-2012, 12:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1