+ Reply to Thread
Results 1 to 19 of 19

Macro failing to copy range

  1. #1
    Registered User
    Join Date
    09-06-2012
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    20

    Macro failing to copy range

    I'm having two problems with code below. First it's not working, second I can't see how to find the problem.

    I got some help from Richard Buttrey a few days ago with the code, and one thing I've not understood - and this is where the code is breaking - is the Sheet2 method. Is it a method? I can't find it documented.

    So, how do I go about understand the code better, and what's not working? It fails at the line wsInitialSheet.Columns("A:C").Copy Sheet2.Range("A1")

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: Macro failing to copy range

    Duncan

    How does it fail?

    Are you getting errors?
    If posting code please use code tags, see here.

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

    Re: Macro failing to copy range

    wsInitialSheet.Columns("A:C").Copy Sheet2.Range("A1")
    I have the feeling that<< Sheet2>> not existed
    If not you should add it to the workbook before run the macro
    And remember That sheet2 is the code mane not the sheet name
    Last edited by mohadin; 11-04-2020 at 04:13 AM.

  4. #4
    Forum Contributor
    Join Date
    10-14-2020
    Location
    Shanghai
    MS-Off Ver
    O365
    Posts
    154

    Re: Macro failing to copy range

    hi,
    in View - Project Explorer you can see all your sheets. Sheet1(sheetname in xls) you can see all sheet number from VBA and the name shown in XLS in brackets. If you don't see a sheet2 over there, then you don't have one and the code will cause an error.

    Beside this, I tested the code in a new blank workbook, added a second sheet and run the code. Works fine

  5. #5
    Registered User
    Join Date
    09-06-2012
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    20

    Re: Macro failing to copy range

    Quote Originally Posted by Norie View Post
    Are you getting errors?
    Sorry I didn't mention the error message, it says

    Run-time error '424': Object Required
    Thanks all for help so far

    Here are a few things I have tried

    * I had already tried to add extra sheets to make sure there was a second sheet. that hasn't helped me. I need people to be able to add and remove sheets as they wish. In Project Explorer I can see that I have no (Sheet2) and since it's important for me that only the one sheet should be a requirement for this to work, I would rather work around that problem than fix it.
    * I tried changing Sheet2 to Sheet1 and it then goes a bit further and creates the temporary workbook but apparently can't save it and says: Run-time error '1004': Cannot access Read-only document 'Bygningsdelsjournal_texting.txt'. This makes no sense to me and it says this whether the file is already there or not. I check and I can manually save to that folder without any problem, so I can't see how it can be a rights problem.

    Now that I have permission to upload the file I hope I managed to attach it.
    Attached Files Attached Files
    Last edited by Duncan Lithgow; 11-04-2020 at 09:53 AM.

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

    Re: Macro failing to copy range

    Why is red snippet here...
    wsInitialSheet is sheet1...Why are you copying data over itself?

    Please Login or Register  to view this content.
    What is it you actually are wanting to achieve...Explain in detail so that the correct code can be supplied...

    If you are wanting to save active sheet as a txt file then this should work...
    Please Login or Register  to view this content.
    Last edited by sintek; 11-04-2020 at 12:22 PM.
    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!!!

  7. #7
    Registered User
    Join Date
    09-06-2012
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    20

    Re: Macro failing to copy range

    Thanks sintek

    This macro is to save the first three columns of the active sheet to a new tab separated txt file (and close the txt file). That file is then read into a different software package (Autodesk Revit) as keynotes (text descriptions) for building project data. The Sheet1.Copy is from https://www.excelforum.com/excel-pro...-txt-file.html

    Nice compact code. I'm still learning about VBA and OOP so anything you feel like explaining would be great. The only thing I need to change is that I only want this part saved to txt:

    .Columns("A:C")

    I've tried inserting that into your code without any luck. I thought this would work, but it doesn't.

    ActiveSheet.Columns("A:C").Copy

    I'll stop trying and see how you do it. I've really been trying to look things up and not yet getting anywhere. Is this right: Application.ActiveSheet is what gives us our object, Copy is a method we're using on that object. So I thought ActiveSheet.Columns("A:C").Copy would just be a more specific object, obviously I'm somehow wrong.
    Last edited by Duncan Lithgow; 11-04-2020 at 04:31 PM.

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

    Re: Macro failing to copy range

    This macro is to save the first three columns of the active sheet to a new tab separated txt file (and close the txt file).
    Post 6 does this

    The Active sheet has only 3 columns of data...No need to specify...
    Please Login or Register  to view this content.
    If you only wanted A:B then simply add delete code after copy...No need to copy to Temp sheet and then save...
    Please Login or Register  to view this content.
    Last edited by sintek; 11-05-2020 at 01:35 AM.

  9. #9
    Registered User
    Join Date
    09-06-2012
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    20

    Re: Macro failing to copy range

    Thanks simtek, I do need to specify because the convention here is to use the columns from D onwards for other information. This file is a template that people will fill with all sorts of things where only those three columns on the active sheet are interesting for the macro.

    I've made a simple file with your latest code. It is exporting fine, but it is taking everything *other than* column C. Can you help me learn how to look these things up? So when I look at https://docs.microsoft.com/en-us/off...uage-reference I'm just getting confused. I'm struggling to find time to go through a proper course but if I can find a good site for looking things up and seeing some examples that will help me a lot. I will tryand go through https://www.excelforum.com/excel-pro...materials.html when I get a chance.
    Attached Files Attached Files

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

    Re: Macro failing to copy range

    Have a look at this forum...There are multiple links to learning materials...

    Only Columns 1 to 3 then ...

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-06-2012
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    20

    Re: Macro failing to copy range

    Thanks sintek. That seems to work, the code is getting harder and harder for me to understand with each version ... I'll spend some time going away and investigating your code. Is there a way in this forum system for me to catch your attention weeks/months later? I assume PMs are impolite.

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

    Re: Macro failing to copy range

    There are always many members willing to assist...that's how we all learn...happy coding...Tx for rep +

  13. #13
    Registered User
    Join Date
    09-06-2012
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    20

    Re: Macro failing to copy range

    ... actually I was too quick. I've populated the sheet with a snippet of real data from a project. Now it only takes the first column. If I add a set of words or numbers across the top line it seems to work. Is there a more human readable way to specify selection of the first three columns? I can't at all get my head around the code you've used for that. In a recorded macro it just writes Columns("A:C"). Attached a file with some real data.
    Attached Files Attached Files

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

    Re: Macro failing to copy range

    You said 3 columns?...this sample 2 columns with headers?
    Just replace lc code with this...
    Please Login or Register  to view this content.
    So what is actual file...How many columns?

    Code works...If wanting other solution then stipulate...Sure someone will hop on board and offer an alternative...

    For future posts, please be so kind and upload a sample file depicting your ACTUAL file setup...99% of the time users are not able to edit code to suite actual requirements...
    Last edited by sintek; 11-05-2020 at 09:55 AM.

  15. #15
    Registered User
    Join Date
    09-06-2012
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    20

    Re: Macro failing to copy range

    The code is not having success in catching the whole of the first three columns. Is there really not a more human readable way to specify the first three columns? I've been trying to search for the answer and getting confused. I've now attached a real world file with real data. with the latest code.

    I've also attached a txt file showing a full version of how the output should be. First three columns, tab seperated, line break, next row.
    Attached Files Attached Files

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

    Re: Macro failing to copy range

    Code does work...
    You omitted the most important snippet...
    Please Login or Register  to view this content.
    See attached result...
    Attached Files Attached Files
    Last edited by sintek; 11-06-2020 at 05:01 AM.

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

    Re: Macro failing to copy range

    Is there really not a more human readable way to specify the first three columns
    Seeing as you are insisting on a specific Col A:C copy...
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    09-06-2012
    Location
    Denmark
    MS-Off Ver
    365 ProPlus
    Posts
    20

    Re: Macro failing to copy range

    Thanks ... this is now in testing, interestingly it adds some speech marks around some cell content that a manual txt export doesn't. We'll see if that creates a problem. I'm enjoying diving in to your code to understand it.

    If I wanted to add a checkbox where users can chose if they will suppress DisplayAlerts or not where should I start? Maybe you have a link or phrase I can search with for this type of functionality? I want to slowly start looking into some configuration options, like manual naming of the output file and file path.

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

    Re: Macro failing to copy range

    interestingly it adds some speech marks
    Replace red snippet below with xlTextPrinter
    Please Login or Register  to view this content.
    If I wanted to add a checkbox where users can chose if they will suppress DisplayAlerts or not where should I start? Maybe you have a link or phrase I can search with for this type of functionality? I want to slowly start looking into some configuration options, like manual naming of the output file and file path.
    Time for another thread as this does not pertain to current...
    Last edited by sintek; 11-06-2020 at 07:37 AM.

+ 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. [SOLVED] Failing to trigger a message box when cell value in a range is less than 0
    By BillyGoat123 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-31-2020, 04:47 AM
  2. [SOLVED] Why is my copy/paste macro failing?
    By taylorsm in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-07-2017, 06:07 PM
  3. Trying to copy URL into Excel using VBA (And failing miserably)
    By Silkbeast in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2014, 11:58 AM
  4. Adding cells to a named range failing
    By laterdaysluke in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-22-2010, 03:13 AM
  5. [SOLVED] copy method of object failing
    By vj2india in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-19-2006, 09:25 PM
  6. Not finding value, range method failing
    By davegb in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-06-2006, 11:45 AM
  7. [SOLVED] Excel Copy Method Failing.
    By johnd@arm.co.uk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-11-2005, 01:06 PM

Tags for this Thread

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