Results 1 to 7 of 7

Thread: copy data from workbook1 sheet1 to workbook2 sheet1,sheet2...

  1. #1
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0

    copy data from workbook1 sheet1 to workbook2 sheet1,sheet2...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to copy data from a large spreadsheet to smaller parsed out versions. And I can not seem to get the code to work properly for how I am referring to the cells.

    in this example, newSheetName is the name of the active sheet, in the active workbook.
    wipWorkbook is a predefined string from the user.
    Code:
    'Example
    'copy the header rows from W-I-P
          For cellCount = 1 To 40
             Worksheets(newSheetName).Cells(1, cellCount) = Workbooks(wipWorkbook).Worksheets("W-I-P").Cells(1, cellCount)
          Next cellCount
    'End of Example
    this string works within the same workbook provided there is a W-I-P sheet in the same workbook as newSheetName
    Worksheets(newSheetName).Cells(1, cellCount) = Worksheets("W-I-P").Cells(1, cellCount)

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Sorry for the late reply here.

    Why not just copy/paste it in one go?
    Code:
        Workbooks(wipWorkbook).Worksheets("W-I-P").Range("A1:A40").Copy
        Worksheets(newSheetName).PasteSpecial Paste:=xlPasteAll
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Ken, thank you for the response. I really like the idea of simplifying the code to copy all at once as you have.
    However it is still not working properly.

    I changed your code, however the 2nd line still is not working.
    I get Run-time error '1004':
    Application-defined or object-defined error.

    As a note newSheetName is an integer, so I can increment and move from sheet to sheet and copy information as needed.
    I also tried replacing the Worksheets(newSheetName) with ActiveSheet to no avail.


    Workbooks(wipWorkbook & ".xlsm").Worksheets("W-I-P").Range("A1:A40").Copy Worksheets(newSheetName).PasteSpecial Paste:=xlPasteAll

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    for this example Ken. lets use real names instead of variables, as in I'm copying from Book1.xlsm sheet1 to Book2.xlsm sheet4.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Oh, my bad... I missed a cell reference on the paste. Modify it to this and give it a go:

    Code:
     Workbooks(wipWorkbook).Worksheets("W-I-P").Range("A1:A40").Copy
        Worksheets(newSheetName).Range("A1").PasteSpecial Paste:=xlPasteAll
    Make sure you make a backup copy of your workbook first though, just to make sure you don't overwrite something important.

    If this doesn't work we'll start digging deeper into names and such.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    That seems to have done the trick, adding in the range to start the paste.
    Thank you for your help.
    And thank you for doing this website so people have somewhere nice to go for advice

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    That's what I get for writing code in the browswer and not testing it.

    The site is my pleasure, believe me. Thank you for posting and helping out on some of these threads too. I really appreciate it!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

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