Thanks Per,
I had to change the posistion of one line myFile = C.Offset(0, 1).Value
and ended up with.
Only done a couple tests but all is Good.
Thanks for your help
Customer = Worksheets("CurrentRecord").Range("F2").Value
With Worksheets("CustomerLogSheet").Columns("A")
Set C = .Columns("A").Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)
End With
If C Is Nothing Then
Application.Run "Copy_To_Workbooks4"
Else
myFile = C.Offset(0, 1).Value
Set DestWB = Workbooks.Open(myFile)
Sheets(1).Unprotect Password:="mypsswrd"
Thankyou
Winnie
"Per Jessen" wrote:
> Hi Winnie
>
> Looking at your code I see you don't set a reference to the With
> statement. Look at this and notice the leading dot in the statement
> "Set C=...". You also miss End With statements.
>
> Customer = Worksheets("CurrentRecord").Range("F2").Value
> With Worksheets("CustomerLogSheet").Columns("A")
> Set C = .Columns("A").Find(what:=Customer, _
> LookIn:=xlValues, lookat:=xlWhole)
> myFile = C.Offset(0, 1).Value
> End With
>
> If C Is Nothing Then
> Application.Run "Copy_To_Workbooks4"
> Else
> Set DestWB = Workbooks.Open(myFile)
> Sheets(1).Unprotect Password:="mypsswrd"
> End If
>
> Regards,
> Per
>
> On 8 Maj, 07:37, winnie123 <winnie...@discussions.microsoft.com>
> wrote:
> > I have tried but failed.
> >
> > I have ended up with this but I can not seem to get it right, can you shed
> > any light on what I am doing wrong please.
> >
> > With Worksheets("CurrentRecord")
> > Customer = Range("F2")
> > With Worksheets("CustomerLogSheet").Columns("A")
> > Set C = Columns("A").Find(what:=Customer, _
> > LookIn:=xlValues, lookat:=xlWhole)
> > myFile = C.Offset(0, 1).Value
> >
> > If C Is Nothing Then
> > Application.Run "Copy_To_Workbooks4"
> >
> > Else
> >
> > Set DestWB = Workbooks.Open(myFile)
> > Sheets(1).Unprotect Password:="mypsswrd"
> >
> > End If
> >
> >
> >
> > "winnie123" wrote:
> > > Thanks Joel,
> >
> > > I will have a play tonight after work to see if I can get it to work.
> >
> > > Winnie
> >
> > > "joel" wrote:
> >
> > > > You have two choices ( or due both).
> >
> > > > 1) check if the file exists using DIR(filename)
> >
> > > > FName = dir(Filename)
> > > > if FName = "" then
> > > > 'file doesn't exist
> > > > else
> > > > 'file does exist
> > > > end if
> >
> > > > 2) Check column A for customer
> > > > Customer = Range("D5")
> > > > set c = Columns("A").Find(what:=Customer, _
> > > > lookin:=xlvalues, lookat:=xlwhole)
> > > > if c is nothing then
> > > > 'customer doesn't exist
> > > > else
> > > > 'customer does exist
> > > > end if
> >
> > > > "winnie123" wrote:
> >
> > > > > Hi
> >
> > > > > If I had a customer name in Cell D5, which has been selected from a
> > > > > validation list is it possible to
> >
> > > > > 1. check a summary sheet to see if the file already exists and if so add
> > > > > data to the next available row. The summary sheet would have the customer
> > > > > name in A4 downwards, B4 downwords would be the hyperlink to the file.
> >
> > > > > 2. use that customer name to create a new file, would need to copy header
> > > > > row as well as data. Then update the summarry sheet to include the new
> > > > > Customer name and file path.
> >
> > > > > At the moment I have a macro from RDB which copies all unique vales into new
> > > > > workbooks and creates this summary page with the hyperlinks.
> > > > > As more records gets added it may mean that the macro is creating 300 files
> > > > > which will take time. So I am looking at just adding the new reord to the
> > > > > existing file or create new.
> >
> > > > > Any help and advise would be appreciated as always.
> >
> > > > > Thankyou
> > > > > Winnie- Skjul tekst i anførselstegn -
> >
> > - Vis tekst i anførselstegn -
>
>
|
Similar Threads
