Macro failing to copy range

**Duncan Lithgow** · 11-04-2020, 03:43 AM

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.

**Norie** · 11-04-2020, 03:52 AM

Duncan

How does it fail?

Are you getting errors?

**mohadin** · 11-04-2020, 04:06 AM

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

**hansolu** · 11-04-2020, 04:25 AM

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

**Duncan Lithgow** · 11-04-2020, 09:16 AM

Originally Posted by Norie

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.

**Sintek** · 11-04-2020, 12:01 PM

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.

**Duncan Lithgow** · 11-04-2020, 02:07 PM

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.

**Sintek** · 11-05-2020, 01:32 AM

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.

**Duncan Lithgow** · 11-05-2020, 05:16 AM

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.

**Sintek** · 11-05-2020, 05:58 AM

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.

**Duncan Lithgow** · 11-05-2020, 08:16 AM

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.

**Sintek** · 11-05-2020, 08:19 AM

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

**Duncan Lithgow** · 11-05-2020, 09:40 AM

... 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.

**Sintek** · 11-05-2020, 09:45 AM

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...

**Duncan Lithgow** · 11-06-2020, 04:41 AM

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.

**Sintek** · 11-06-2020, 04:50 AM

Code does work...
You omitted the most important snippet...

Please Login or Register  to view this content.

See attached result...

**Sintek** · 11-06-2020, 05:32 AM

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.

**Duncan Lithgow** · 11-06-2020, 07:21 AM

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.

**Sintek** · 11-06-2020, 07:28 AM

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...

Macro failing to copy range

LinkBack

Thread Tools

Rate This Thread

Display

Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Re: Macro failing to copy range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Failing to trigger a message box when cell value in a range is less than 0

[SOLVED] Why is my copy/paste macro failing?

Trying to copy URL into Excel using VBA (And failing miserably)

Adding cells to a named range failing

copy method of object failing

Not finding value, range method failing

Excel Copy Method Failing.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions