1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Macro for copy part of excel sheet rows and columns and paste in other file

Discussion in 'VBA Macros' started by preethi3290, Jul 19, 2017.

  1. preethi3290

    preethi3290 New Member

    Messages:
    26
    Hi,
    My Task is to import part of data to Access from excel sheets which are in multiple folders.

    I have data in excel sheets which are in multiple folders.
    I want to copy part of excel sheet data from multiple folders and save in a mastersheet. Now I have to Import this mastersheet data into MS Access.
    Please give me the code.
  2. Nebu

    Nebu Excel Ninja

    Messages:
    2,047
    Hi:

    Why can't you link all the excel files to access database and run queries to extract the data you want, rather than doing it outside access. Access is basically designed to do that.

    Thanks
  3. preethi3290

    preethi3290 New Member

    Messages:
    26
    Hi Nebu,
    Thanks for the Reply.
    Can you help me how to link all files and I have to import Part of excel sheet which are in multiple folders
  4. preethi3290

    preethi3290 New Member

    Messages:
    26
    Hi Nebu,
    I have to import to access from A15 of Excel sheet

    Attached Files:

  5. Nebu

    Nebu Excel Ninja

    Messages:
    2,047
  6. preethi3290

    preethi3290 New Member

    Messages:
    26
  7. preethi3290

    preethi3290 New Member

    Messages:
    26
  8. preethi3290

    preethi3290 New Member

    Messages:
    26
    I'm waiting for your reply. Can you please help me on this. It's urgent
  9. Nebu

    Nebu Excel Ninja

    Messages:
    2,047
    Hi:

    I guess with this excel format the linking is not straight forward. You have to manipulate your excel file before linking it with access.

    Thanks
  10. preethi3290

    preethi3290 New Member

    Messages:
    26
    That's why I want to extract necessary fields from all the sheets which are in multiple folders and save in a new master sheet and then import to access
  11. preethi3290

    preethi3290 New Member

    Messages:
    26
    I have tried this macro to extract the data and save in a new file. But every time it is showing below message
    upload_2017-7-19_17-44-48.png

    Macro I used

    Sub copy()

    Dim i As Integer, erow As Integer

    'LastRow = ActiveSheet.Range(“A" & Rows.Count).End(xlUp).Row

    For i = 16 To 36

    Range(Cells(i, 1), Cells(i, 17)).Select
    Selection.copy

    Workbooks.Open Filename:="C:\Users\preet\Desktop\boss data\Customer Invoice Template\ANAHEIM HILLS-DA\mastersheet.xlsx"
    Worksheets("master").Select
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Cells(erow, 1).Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.CutCopyMode = False

    Next i
    End Sub
  12. Nebu

    Nebu Excel Ninja

    Messages:
    2,047
    Try

    Application.DisplayAlters=False ,at the start of your code.

    Thanks
  13. preethi3290

    preethi3290 New Member

    Messages:
    26
    in which line I have to write Application.DisplayAlters=False
  14. Nebu

    Nebu Excel Ninja

    Messages:
    2,047
    Hi:

    Before your DIM statement.

    Thanks
  15. preethi3290

    preethi3290 New Member

    Messages:
    26
    again it is showing the same message
  16. preethi3290

    preethi3290 New Member

    Messages:
    26
    Did you try my code? Are you getting the same message?
  17. preethi3290

    preethi3290 New Member

    Messages:
    26
    I got it. There is small spellingmistake.

    Thanks alot
  18. preethi3290

    preethi3290 New Member

    Messages:
    26
    Hi,
    when I am processing Macro in second excel file it is cpoying the data into main sheet but it is changing the first sheet values(See mastersheet). If I am processing macro for the same sheet twice then the values are not changing(See sheet2). Please help me on this.

    Attached Files:

  19. preethi3290

    preethi3290 New Member

    Messages:
    26
    The values are keepon changing.
  20. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, preethi3290!
    Sorry if arriving late to this conversation makes me post yet answered questions, but I have a few doubts.
    You have the Excel workbook that you uploaded at post 4. In that workbook you have a little hole in range J32:L36. Despite of this which is the exact range that you want to export to Access? And why are you trying to achieve this from Excel and not from Access? Could you also post the Access file? Thank you.
    Regards!
  21. preethi3290

    preethi3290 New Member

    Messages:
    26
    Hi SirJB7,
    I want to import part of excel sheet to Access which are in multiple folders. and In my excel sheet the first row is not table. The table starts at A15. So I want to import data from A15.
    As this not possible directly I thought to Copy the desired dataa from each excel file which are in multiple folders and paste in a master sheet. So that I can direct;y import the master sheet to Access.

    Please see the sample excel sheet in attachments

    Attached Files:

  22. preethi3290

    preethi3290 New Member

    Messages:
    26
    Hi,
    Can you understand my Issue
  23. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    Hi, preethi3290!

    Please tell me:
    a) path of the Access file
    b) paths of the Excel files
    c) exact range start (row, col) thru end (row, col / first blank) of Excel files
    d) Access table structure where ranges will be imported.

    Preferrabily, upload Access file too.

    Regards!
  24. preethi3290

    preethi3290 New Member

    Messages:
    26
    a) Path of Access File(Documents)
    b) Path of Excel file(Desktop)
    c) I want to extract Data from cells(A15, L15) to (A36,L36) condition: if Date column exists then only it has to extract that particular row
    d)Aceess table structure is same as excel sheet structure.

    Please tell me if you need any more details from my side.
    It's very urgent task for me.
    Please try to help

    Attached Files:

  25. preethi3290

    preethi3290 New Member

    Messages:
    26
    Hi,
    Do you understand my Issue?

Share This Page