+ Reply to Thread
Results 1 to 16 of 16

Open Multi Files and Copy and Paste Special

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,273

    Open Multi Files and Copy and Paste Special

    I have code to open multiple file and paste the data below once another

    1) I cannot get the section of code to work to paste the data as values
    2) I also want to find "Total" in Col D and clear data 10 rows down and two columns to the left of "Total" and one col to the right of total

    Please Login or Register  to view this content.

    Please Login or Register  to view this content.

    I have attached sample source data (I have valued the formula's , except the sum formulas)

    Your assistance is most appreciated
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,872

    Re: Open Multi Files and Copy and Paste Special

    Besides having an extra line between your line break using PasteSpecial requires two lines

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,273

    Re: Open Multi Files and Copy and Paste Special

    Hi Dave

    I added this code below


    Please Login or Register  to view this content.

    I get a run time error "wrong number or invalid property assignment

    I have attached sample source data


    Kindly test & amend
    Attached Files Attached Files

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,029

    Re: Open Multi Files and Copy and Paste Special

    Nope...red snippet denotes same line...as dave said...seperate lines...
    Please Login or Register  to view this content.

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Valued Forum Contributor
    Join Date
    06-27-2010
    Location
    sYRIA
    MS-Off Ver
    Excel 2013
    Posts
    427

    Re: Open Multi Files and Copy and Paste Special

    Try
    But not tested
    at least the method is ok
    Please Login or Register  to view this content.

    BTW:
    Please Login or Register  to view this content.
    When Open a workbook then thisworkbook refer to the newly oppend book! Am I right?

  6. #6
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,273

    Re: Open Multi Files and Copy and Paste Special

    Thanks Sintek

    I now get subscript out of range and code below is highlighted

    Please Login or Register  to view this content.

  7. #7
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,872

    Re: Open Multi Files and Copy and Paste Special

    Quote Originally Posted by mohadin View Post

    When Open a workbook then thisworkbook refer to the newly oppend book! Am I right?
    ThisWorkbook is the workbook running the code.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,029

    Re: Open Multi Files and Copy and Paste Special

    If you want it pasted into original workbook then set a reference to it...something like this...untested
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,273

    Re: Open Multi Files and Copy and Paste Special

    Thanks for the help, Mohadin-code works perfectly


    With regard to point 2 in Post #1, I need to find "Total" in Col D and clear data 10 rows down and two columns to the left of "Total" and one col to the right of total


    It would be appreciated if you can provide me with the code to do this

  10. #10
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,273

    Re: Open Multi Files and Copy and Paste Special

    Hi Sintek & Dave

    My apologies. I had the incorrect sheet name on my live data. It was referring to the source data sheet and not the active workbook sheet

    I just picked this up just now

    Please Login or Register  to view this content.

    Many thanks for your input and help

  11. #11
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,273

    Re: Open Multi Files and Copy and Paste Special

    I have tried to write code to find Total and clear the data to the right and left of it and 10 rows below it, but nothing happens


    I have highlighted the cells in yellow that must be cleared


    It would be appreciated if you could kindly amend my code
    Attached Files Attached Files

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,029

    Re: Open Multi Files and Copy and Paste Special

    You say 10 rows below, yet only highlighted total of 6 rows?
    Please Login or Register  to view this content.
    Last edited by sintek; 12-31-2020 at 04:00 PM.

  13. #13
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2016
    Posts
    1,273

    Re: Open Multi Files and Copy and Paste Special

    Many Thanks Sintek


    I mentioned 10 rows below just as an example in case there is perhaps some data up to 10 rows below

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,029

    Re: Open Multi Files and Copy and Paste Special

    Understood...Glad you got it sorted...Tx for rep +

  15. #15
    Valued Forum Contributor
    Join Date
    06-27-2010
    Location
    sYRIA
    MS-Off Ver
    Excel 2013
    Posts
    427

    Re: Open Multi Files and Copy and Paste Special

    Great
    And thank you
    Sorry I forget about Point 2
    Be happy

  16. #16
    Valued Forum Contributor
    Join Date
    06-27-2010
    Location
    sYRIA
    MS-Off Ver
    Excel 2013
    Posts
    427

    Re: Open Multi Files and Copy and Paste Special

    Quote Originally Posted by Howardc1001 View Post
    Thanks for the help, Mohadin-code works perfectly


    With regard to point 2 in Post #1, I need to find "Total" in Col D and clear data 10 rows down and two columns to the left of "Total" and one col to the right of total


    It would be appreciated if you can provide me with the code to do this
    Great
    And thank you
    Sorry I forget about Point 2
    Be happy

+ 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. VBA to Open, Copy & Paste across multiple files
    By excelhelpneeded1123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2016, 04:14 PM
  2. VBA code to open new workbook and copy and paste special values
    By oddinho2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2013, 11:24 PM
  3. Replies: 0
    Last Post: 04-13-2013, 10:45 AM
  4. Open/close files and copy and paste problem
    By kb9nvh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-12-2011, 03:30 PM
  5. Macro - open files, copy & paste values
    By aron.silan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-17-2010, 09:24 AM
  6. Copy/Paste special form one sheet to another into first open colum
    By GeorgeD in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-28-2010, 05:50 PM

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