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

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

Copy a named range only to sheet names ending with .txt

L

Lymm

Member
Hi I have imported several text files and saved them as worksheets in a workbook which contains other sheets as well. The new sheet names all end with .txt. I would like to copy a named range from another sheet and paste it only into the sheets named ....txt. I have some code which will paste it into every sheet but I need to change it to paste only to the ...txt sheets. Thank for some pointers on changing the code.
 
code will probably need to look something like this:

[pre]
Code: 
For Each ws In ThisWorkbook.Worksheets
If UCase(Right(ws.Name, 4)) = ".TXT" Then
'Do something
End If
Next ws
[/pre]
 
Hi, Thank you Luke. This is what i am working with so far but nothing is getting pasted to the .txt sheets. The code just goes straight to End If after "Then" My "Do something" is not working.


`Dim ws As Worksheet

Application.ScreenUpdating = False

ActiveWorkbook.Sheets("BlankEntries").Activate

Application.Goto Reference:="entryform"

Selection.Copy

For Each ws In ThisWorkbook.Worksheets

If LCase(Right(ws.Name, 4)) = "*.txt" Then

'With ws

'ws.Select

Range("A1").Select

ActiveSheet.Paste


Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Range("A1").Select


'End With

End If

Next ws

'Set ws = Nothing

Application.ScreenUpdating = True

End Sub`


Sorry its a bit messy.
 
Hi, Lymm!


Why did you comment the "ws" related lines? You're always working on ActiveSheet (BlakEntries) as you don't activate none of those "ws" within the For-Next loop.


BTW, do you have a first statement "Option Explicit"? I guess you don't and I strongly recommend you should. If so, you'd have been prompted about the error of using "ws" in the For-Next loop without definition. It's safer and a better practice.


As usual, consider uploading a sample file.


Regards!
 
Hi SirJB7 as you can see I am not very experienced with code and was just trying different options to see if I could get it to work. All I want to do is copy the entry form, named range "entryform" which is on sheet "BlankEntries" to the other sheets with names ending with .txt. I will put in the Option explicit and try again, thanks. Here is a sample workbook.


http://dl.dropbox.com/u/20269099/copyentrytest.xls
 
Hi, Lymm!


I saw, I saw, ...


Here's the uploaded file with the updated macro named CopyToDotTextSheets, which its original code didn't exist in the file you posted, BTW.


I don't know if you want to just copy & paste normally or if you want to paste special as you wrote in the code provided. I left you both options, comment the undesired one.


I deleted as well a lot of named ranges that referred to inexistent ranges (even to my HD folder structure) and left the good ones. I haven't any trouble to find the named range "entryform" but as I didn't know where to paste it, I did it in cell A1 of each .txt ending worksheet name. Adjust it properly as needed.


Regards!
 
Hi, Lymm!

Don't tell me that you want the link too!

http://dl.dropbox.com/u/60558749/Copy%20a%20named%20range%20only%20to%20sheet%20names%20ending%20with%20.txt%20-%20copyentrytest-1%20%28for%20Lymm%20at%20chandoo.org%29.xls

:)

Regards!

PS: sorry, it won't happen again... till next time!
 
You must log in or register to reply here.
Back
Top