UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> VBA For Selecting The Last Column Of The Excel Worksheet, Any Version    
 
   
Frank Situmorang
post Jul 10 2017, 04:09 PM
Post#21



Posts: 1,203
Joined: 10-June 10



Larai,

This is my code :

CODE
Sub Frank()
'
' Frank Macro
'

'
    Sheets("summary").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Sheets("Donor").Select
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("summary").Select
    Range("A3").Select
    ActiveSheet.Paste
    Sheets("Interface").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
    Dim wsDonorDetail As Worksheet
    Dim wsInterface As Worksheet
    
    Set wsDonorDetail = Sheets("DonorDetail")
    Set wsInterface = Sheets("Interface")
    wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown), 6).Copy Destination:=wsInterface.[a2]
    Sheets("Receipt").Select
    Application.CutCopyMode = False
End Sub


When I run it it stop at
CODE
wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown), 6).Copy Destination:=wsInterface.[a2]


Thanks for your help again.

Frank
Go to the top of the page
 
larai
post Jul 10 2017, 05:07 PM
Post#22



Posts: 1,114
Joined: 8-February 02
From: California, USA


replace that line with this:
CODE
wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy Destination:=wsInterface.[a2]
Go to the top of the page
 
Frank Situmorang
post Jul 10 2017, 05:27 PM
Post#23



Posts: 1,203
Joined: 10-June 10



Thank you Larai, It works perfectly, you are awesome.
Where can I study all of this. Is there any website that explains each code of Excel VBA and the sample?, If possible shown on you tube?.

Frank
Go to the top of the page
 
larai
post Jul 10 2017, 06:33 PM
Post#24



Posts: 1,114
Joined: 8-February 02
From: California, USA


Hi Frank,

Youtube does have code samples. Googling is probably your best bet, followed by posting on this or other Excel forums. What is difficult is learning how to phrase your request so you get good search results. There's also a number of online sources that offer classes for pay.

What I did to learn was mostly by posting questions and then asking for how/why something worked if I couldn't figure it out myself.

Good luck and glad it worked for you!
Go to the top of the page
 
Frank Situmorang
post Jul 11 2017, 05:27 PM
Post#25



Posts: 1,203
Joined: 10-June 10



Hi larai,

After I put your suggested code, when I open my file, it create a backup file with the name ~!myworksheetname in the folder. It seems that it suggested the recovery file. Is that because of the code of "Resize"?, all the files in my folder if I open it suggests the recovery file.

Do you know what makes it that way?

Thank you,

Frank
Go to the top of the page
 
larai
post Jul 11 2017, 08:11 PM
Post#26



Posts: 1,114
Joined: 8-February 02
From: California, USA


Hi Frank,

Resize is a VBA command used on a range to change it's size. The range in this case was the cell D3. I can't think of anything in the code that would have created a recovery file. That generally doesn't occur unless Excel crashes.
Go to the top of the page
 
Frank Situmorang
post Jul 12 2017, 01:06 AM
Post#27



Posts: 1,203
Joined: 10-June 10



But the problem is only when we opened the file , after we closed we look again to the folder We do not see anymore the crashed file. Only when we run the macro. It created the recovery file. Anyone knows why?

Thanks

Frank
Go to the top of the page
 
larai
post Jul 12 2017, 11:39 AM
Post#28



Posts: 1,114
Joined: 8-February 02
From: California, USA


Is the recovery file being created every time you run the macro? Have you deleted the recovery file?

I looked over all your code and there's nothing in there that saves the file or tries to create a new file.
Go to the top of the page
 
Frank Situmorang
post Jul 12 2017, 11:55 AM
Post#29



Posts: 1,203
Joined: 10-June 10



Yes Larai, every time run macro it created a recovery file with size only 1 KB.

Maybe I will try to run the open and repair?, because this was probably the effect of my previous wrong code?

Thank you.

Frank
Go to the top of the page
 
Frank Situmorang
post Jul 12 2017, 12:13 PM
Post#30



Posts: 1,203
Joined: 10-June 10



Larai,

I found it that even we have not run the macro, as we open the workbook, it will automatically create the recovery file. The new recovery file will be created for any time we open the work book.
Go to the top of the page
 
larai
post Jul 13 2017, 11:38 AM
Post#31



Posts: 1,114
Joined: 8-February 02
From: California, USA


So each time you open the workbook, a new file gets created. Are you deleting the file? What happens when you close the workbook? Does the new file get deleted? If you keep the new file, next time you open the main workbook, does another file get generated? What's the file extension? .xlsx?
Go to the top of the page
 
Frank Situmorang
post Jul 13 2017, 04:34 PM
Post#32



Posts: 1,203
Joined: 10-June 10



Hi Larai,

Let's just forget this. It seems it is automatically generated for the back up system by the Microsoft, because when I open other excel files they always get create new files with the first character of the name of the file will be replaced by "~$". But when we close the files, this name will be automatically disappeared.

I think it is normal, because when I see also my other files with macro, the problem is the same. So I assume it is automatically backing up system.

Thank you again for you support.


Frank
Go to the top of the page
 
larai
post Jul 13 2017, 04:39 PM
Post#33



Posts: 1,114
Joined: 8-February 02
From: California, USA


ok, glad it's not an issue.
Go to the top of the page
 
Frank Situmorang
post Jun 23 2019, 12:47 AM
Post#34



Posts: 1,203
Joined: 10-June 10



Hi Larai,

I have a question on this VBA:
QUOTE


Set wsDonorDetail = Sheets("DonorDetail")
Set wsInterface = Sheets("Interface")
wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy Destination:=wsInterface.[a2]
Sheets("Receipt").Select
Application.CutCopyMode = False
QUOTE


How if the content of the Sheets("DonorDetail") only one record/line, how can we say it on VBA not to use End(xlDown), but instead to go 6 columns from D3.

I tried this but not working
QUOTE


Set wsDonorDetail = Sheets("DonorDetail")
Set wsInterface = Sheets("Interface")
If Len(Range(("DonorDetail")"d4")).Value) = 0 then wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].Row - 2, 6).Copy Destination:=wsInterface.[a2]
wsDonorDetail.[d3].Resize(wsDonorDetail.[d3].End(xlDown).Row - 2, 6).Copy Destination:=wsInterface.[a2]
Sheets("Receipt").Select
Application.CutCopyMode = False
QUOTE


Thank you for your enlightening me.

Frank



--------------------
Frank S
Highland, CA
Go to the top of the page
 
Frank Situmorang
post Jun 25 2019, 12:58 PM
Post#35



Posts: 1,203
Joined: 10-June 10



Hi Debaser, maybe Larai is out of town, could you or anyone help me on my question below (previous post?

Thank you

Fran
This post has been edited by Frank Situmorang: Jun 25 2019, 12:59 PM

--------------------
Frank S
Highland, CA
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2019 - 10:01 AM