Save column J only using copy/paste & temporary copy
 

Help. Need help arriving at VBA code to:

After working in an opened workbook called LmbcAcctsPayable.xls shee
named Data,

Save As LmbcAcctsPayableCopy.xls & copy column J, then

Open LmbcAcctsPayable.xls & paste column J above to it's column J
then

Save & exit LmbcAcctsPayable.xls, then

Exit LmbcAcctsPayableCopy.xls without saving, then

Delete file LmbcAcctsPayableCopy.xls

Thanks a million for all your help. mikebur

--
mikebur
-----------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...fo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=54947

Save column J only using copy/paste & temporary copy
 

Ahhh.

You've made changes that you don't want to save to lmbcacctspayable.xls, but the
changes in column J should be saved?

Option Explicit
Sub testme()
Dim myJRng As Range
Dim newWkbk As Workbook

'save the current state of this workbook under a new name
Application.DisplayAlerts = False
ThisWorkbook.SaveAs ThisWorkbook.Path & "\LmbcAcctsPayableCopy.xls"
Application.DisplayAlerts = True


Set myJRng = ThisWorkbook.Worksheets("data").Range("J:j")

'reopen the real workbook
Set newWkbk = Workbooks.Open(ThisWorkbook.Path _
& "\LmbcAcctsPayablebook1.xls")

myJRng.Copy _
Destination:=newWkbk.Worksheets("data").Range("J1" )

Application.DisplayAlerts = False
ThisWorkbook.ChangeFileAccess Mode:=xlReadOnly
Application.DisplayAlerts = True

Kill ThisWorkbook.FullName

ThisWorkbook.Close savechanges:=False
End Sub


Personally, I wouldn't kill the file. If something goes wrong with the code, I
can fix things manually.

If everything works perfectly, I can always delete that copy later.




mikeburg wrote:

Help. Need help arriving at VBA code to:

After working in an opened workbook called LmbcAcctsPayable.xls sheet
named Data,

Save As LmbcAcctsPayableCopy.xls & copy column J, then

Open LmbcAcctsPayable.xls & paste column J above to it's column J,
then

Save & exit LmbcAcctsPayable.xls, then

Exit LmbcAcctsPayableCopy.xls without saving, then

Delete file LmbcAcctsPayableCopy.xls

Thanks a million for all your help. mikeburg

--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=549470

--

Dave Peterson

Save column J only using copy/paste & temporary copy
 

Thanks so very much! mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=549470