Filename Problem In a Macro

[clive.m.orourke]

Hello!

I need to run a macro that will copy a line of data from one workbook into another. The originating workbook will be one of 575 emailed to me. I recorded a macro to do this which works fine. The problem arises (I think) because windows/excel autonames the second workbook I open with a (2) suffix, followed by (3), (4) and so on.

Please could you advise how I amend the code below so I do not have this problem.

Thanks.

Sub AutoCopy()
'
' AutoCopy Macro
'

'
Windows("EK LPM Preferences v1.0.xls").Activate
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Windows("EK LPM Requests v1.3.xls").Activate
Sheets("Selected Data").Select
Rows("2:2").Select
Selection.Copy
Windows("EK LPM Preferences v1.0.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("2:2").EntireRow.AutoFit
Selection.Font.Bold = False
Range("J2").Select
ActiveCell.FormulaR1C1 = "=R[1]C+1"
Range("J2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Windows("EK LPM Requests v1.3.xls").Activate
End Sub

 

[GS]

First off.., I don't see where in your code you open new workbooks. All
I see is you copying row2 from 1 workbook and inserting it at row2 of
another workbook, both which have filenames. When and why are you
opening new workbooks?

Secondly, the recorded macro needs to be cleaned up if you expect to
use it with 575 files that are emailed to you. Here's a revised version
that should be put into a standard module...

Option Explicit

Sub AutoCopy2()
Dim wkbSource As Workbook, wkbTarget As Workbook

Set wkbSource = Workbooks("EK LPM Requests v1.3.xls")
Set wkbTarget = Workbooks("EK LPM Preferences v1.0.xls")

With wkbTarget
.Rows(2).Insert shift:=xlDown
.Range("2:2").Value = _
wkbSource.Sheets("Selected Data").Range("2:2").Value
With .Range("$J$2")
.FormulaR1C1 = "=R[1]C+1": .Value = .Value
End With '.Range("$J$2")
.Save
End With 'wkbTarget
wkbSource.Activate
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[clive.m.orourke]

Thanks Garry, your clean up is much appreciated. I'm new at this so still very much a beginner. With regards the file opening, perhaps I should explain a bit further.

I will be sending out a workbook to 575 people ("EK LPM Requests v1.3.xls").. They will answer questions, with the answers collated in Row 2 of 'Collated Data'. They will then email the workbook back. I then open the returned workbook, click the macro button which will then populate "EK LPM Preferences v1.0.xls".

The macro works for the first email I open, but the following ones are renamed "EK LPM Requests v1.3.xls (2)" and the macro no longer works. This follows for the 3rd email, suffixed (3) etc, etc.

Thanks in advance for your help!

 

[GS]

Thanks Garry, your clean up is much appreciated. I'm new at this so still
very much a beginner. With regards the file opening, perhaps I should explain
a bit further.

I will be sending out a workbook to 575 people ("EK LPM Requests v1.3.xls").
They will answer questions, with the answers collated in Row 2 of 'Collated
Data'. They will then email the workbook back. I then open the returned
workbook, click the macro button which will then populate "EK LPM Preferences
v1.0.xls".

The macro works for the first email I open, but the following ones are
renamed "EK LPM Requests v1.3.xls (2)" and the macro no longer works. This
follows for the 3rd email, suffixed (3) etc, etc.

Thanks in advance for your help!

Ok, then this is because you're not closing the previous file before
opening the next, and so Excel is appending numbers to the filenames.
To escape this happening, you need to close each file *before* opening
the next.

Since you are saving the file you collate the data to, you can close
the source file and not save it by adding the following line before
*End Sub*...

wkbSource.Close SaveChanges:=False

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Complete code...

Option Explicit

Sub AutoCopy2()
Dim wkbSource As Workbook, wkbTarget As Workbook

Set wkbSource = Workbooks("EK LPM Requests v1.3.xls")
Set wkbTarget = Workbooks("EK LPM Preferences v1.0.xls")

With wkbTarget
.Rows(2).Insert shift:=xlDown
.Range("2:2").Value = _
wkbSource.Sheets("Selected Data").Range("2:2").Value
With .Range("$J$2")
.FormulaR1C1 = "=R[1]C+1": .Value = .Value
End With '.Range("$J$2")
.Save
End With 'wkbTarget
wkbSource.Close SaveChanges:=False '//get ready to open next file
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion