Please - take a look at this

apurk45

Board Regular
Joined
Oct 23, 2002
Messages
217
The first section:
On Error Resume....
up to when I go to last empty Row - DOES NOT WORK. Can you tell me what I am doing wrong.
Thanks

Sub Save()

On Error Resume Next
Error (9)
Windows("PictureLog").Activate

If Not WorkbooksOpen("PictureLog.xls") Then
Workbooks.Open Filename:="C:My DocumentsMT PicturesPictureLog.xls"
End If

'Go to lest empty Row in "PictureLog.xls" file
Application.ScreenUpdating = False
Range("a6").Select
Do Until ActiveCell.Value = vbNullString
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True

'Switch to Template Sheet
Windows("PictureTemplate").Activate

'Delete "Save" Button
ActiveSheet.Shapes("Text Box 4").Select
Selection.Delete
Range("E6").Select

'Save data to new xls file (seqential #)
Do Until Dir("C:My DocumentsMT PicturesJPG" & x & ".xls") = ""
x = x + 1
Loop

ActiveWorkbook.SaveAs "C:My DocumentsMT PicturesJPG" & CStr(x) & ".xls"
ActiveWorkbook.Close
End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Howdy apurk45, I use the set with some goto commands to test as to whether a workbook is open or not. Also, using the end method should be a touch faster than the loop:<pre>
Sub Save()
Dim myBk As Workbook
On Error GoTo 1
Set myBk = Workbooks("PictureLog.xls")
2: myBk.activate
On Error GoTo 0
[a65536].End(xlUp)(2).Select
Exit Sub
1: Set myBk = _
Workbooks.Open("C:Documents and Settingsnate" & _
"My DocumentsMy PicturesPictureLog.xls")
GoTo 2
End Sub</pre>

Also, double check your file path (MT), I happen to have a user directory associated with mine... Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-11-04 19:24
 

apurk45

Board Regular
Joined
Oct 23, 2002
Messages
217
Nate

In code you have:

[a65536].End(xlUp)(2).Select

What that means???
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

On 2002-11-04 19:56, apurk45 wrote:
Nate

In code you have:

[a65536].End(xlUp)(2).Select

What that means???

Hello again. It tells the compiler to take a look at range a65536, and perform the equivalent of hitting your end->up cursor key. This takes you to the last filled cell. Since you want the first empty cell, I use a row index of this cell, 1 would be the cell itself, 2 is the next row. There are two parameters (row,column). Then it selects it.

Hope this helps.

Edit: It's shorthand vba. Much like using an offset. Nice to see you asking though! If you have other questions please feel free to ask. I or others would be happy to help.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-04 23:24
 

apurk45

Board Regular
Joined
Oct 23, 2002
Messages
217
Naten
Here is my new code using your idea. Your portion of the code works fine, BUT rest of my code following your code DOES NOT. Any idea why not. any help would be graet.
Thanks.

Sub Save()

'Checks if PicLog.xls is open if not then open it
Dim myBk As Workbook
On Error GoTo 1
Set myBk = Workbooks("PictureLog.xls")
2: myBk.Activate
On Error GoTo 0
[a65536].End(xlUp)(2).Select
Exit Sub
1: Set myBk = _
Workbooks.Open("C:My DocumentsMT PicturesPictureLog.xls")
GoTo 2

'Switch to Template Sheet
Windows("PictureTemplate").Activate

'Delete "Save" Button
ActiveSheet.Shapes("Text Box 4").Select
Selection.Delete
Range("E6").Select

'Save data to new xls file (seqential #)
Do Until Dir("C:My DocumentsMT PicturesJPG" & x & ".xls") = ""
x = x + 1
Loop

ActiveWorkbook.SaveAs "C:My DocumentsMT PicturesJPG" & CStr(x) & ".xls"
ActiveWorkbook.Close

End Sub
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Hello again apurk45, I haven't tested this and should have combined the 2nd part of your code, but you want the error trapping at the end, something like:<pre>
Sub Save()
'Checks if PicLog.xls is open if not then open it
Dim myBk As Workbook
On Error GoTo 1
Set myBk = Workbooks("PictureLog.xls")
2: myBk.Activate
On Error GoTo 0
[a65536].End(xlUp)(2).Select


'Switch to Template Sheet
Windows("PictureTemplate").Activate

'Delete "Save" Button
ActiveSheet.Shapes("Text Box 4").Select
Selection.Delete
Range("E6").Select

'Save data to new xls file (seqential #)
Do Until Dir("C:My DocumentsMT PicturesJPG" & x & ".xls") = ""
x = x + 1
Loop

ActiveWorkbook.SaveAs "C:My DocumentsMT PicturesJPG" & CStr(x) & ".xls"
ActiveWorkbook.Close

Exit Sub
1: Set myBk = _
Workbooks.Open("C:My DocumentsMT PicturesPictureLog.xls")
GoTo 2

End Sub</pre>

I haven't tested this, just firing out what I know to be true... Also, you shouldn't have to select in VBA, it doesn't always have performance issues, but it's a good rule of thumb not to. So:<pre>
ActiveSheet.Shapes("Text Box 4").Select
Selection.Delete</pre><pre></pre>
Should look more like:<pre>
ActiveSheet.Shapes("Text Box 4").Delete</pre><pre></pre>
Hope this helps.

Edit: Fix the backslahes, a known problem with the, otherwise excellent, board.
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-04 23:40
 

apurk45

Board Regular
Joined
Oct 23, 2002
Messages
217
Nate
Thank YOU again. Your way of coding works just fine.

I am trying to learn VBA but the VBA HELP in Excel is worst then jibrish. I just wonder is there a better way to learn it.
Thanks
 

Forum statistics

Threads
1,144,741
Messages
5,726,014
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top