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

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

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

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
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 -

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
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 -