Ron de Bruin

[steve]

I'm sorry for singling you out, but you've always given me great solutions.
This piece of code will be very crucial in my application.

On a spreadsheet, I have colored yellow every range that i consider to be
User Input. I have also re-named each of these ranges.

I want a button to create a new workbook. I want to copy the names of the
ranges that are user input. I also want to copy their values. This is so
later, I can call on this file and re-use this user input.

One of the big issues I am encountering is that some of the Named Ranges are
larger than just a single cell. I have 3 particular ranges that are 10 cells
X 10 cells.

Later, if the user wants to call on one of these files containing the user
input, I want to cycle through the names of the ranges that we saved, and
copy them back to the yellow cells of the same name. If for some reason,
this yellow cell has changed names or been deleted, I want to just skip that
and move to the next.

Thanks for any help!
Steve

[Ron de Bruin]

How many cells are there in all range names together

--
Regards Ron de Bruin
http://www.rondebruin.nl



"steve" <[email protected]> wrote in message news:[email protected]...
> I'm sorry for singling you out, but you've always given me great solutions.
> This piece of code will be very crucial in my application.
>
> On a spreadsheet, I have colored yellow every range that i consider to be
> User Input. I have also re-named each of these ranges.
>
> I want a button to create a new workbook. I want to copy the names of the
> ranges that are user input. I also want to copy their values. This is so
> later, I can call on this file and re-use this user input.
>
> One of the big issues I am encountering is that some of the Named Ranges are
> larger than just a single cell. I have 3 particular ranges that are 10 cells
> X 10 cells.
>
> Later, if the user wants to call on one of these files containing the user
> input, I want to cycle through the names of the ranges that we saved, and
> copy them back to the yellow cells of the same name. If for some reason,
> this yellow cell has changed names or been deleted, I want to just skip that
> and move to the next.
>
> Thanks for any help!
> Steve

[steve]

there about 30 user input cells that I have named. (these are individual cells)

then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
yellow for userinput.

so in all, there are about 35 names that will be copied. down the road,
this may increase or decrease.

Thanks,
Steve

"Ron de Bruin" wrote:

> How many cells are there in all range names together
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "steve" <[email protected]> wrote in message news:[email protected]...
> > I'm sorry for singling you out, but you've always given me great solutions.
> > This piece of code will be very crucial in my application.
> >
> > On a spreadsheet, I have colored yellow every range that i consider to be
> > User Input. I have also re-named each of these ranges.
> >
> > I want a button to create a new workbook. I want to copy the names of the
> > ranges that are user input. I also want to copy their values. This is so
> > later, I can call on this file and re-use this user input.
> >
> > One of the big issues I am encountering is that some of the Named Ranges are
> > larger than just a single cell. I have 3 particular ranges that are 10 cells
> > X 10 cells.
> >
> > Later, if the user wants to call on one of these files containing the user
> > input, I want to cycle through the names of the ranges that we saved, and
> > copy them back to the yellow cells of the same name. If for some reason,
> > this yellow cell has changed names or been deleted, I want to just skip that
> > and move to the next.
> >
> > Thanks for any help!
> > Steve
>
>
>

[Ron de Bruin]

OK, I look at it after dinner


--
Regards Ron de Bruin
http://www.rondebruin.nl



"steve" <[email protected]> wrote in message news:[email protected]...
> there about 30 user input cells that I have named. (these are individual cells)
>
> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
> yellow for userinput.
>
> so in all, there are about 35 names that will be copied. down the road,
> this may increase or decrease.
>
> Thanks,
> Steve
>
> "Ron de Bruin" wrote:
>
>> How many cells are there in all range names together
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "steve" <[email protected]> wrote in message news:[email protected]...
>> > I'm sorry for singling you out, but you've always given me great solutions.
>> > This piece of code will be very crucial in my application.
>> >
>> > On a spreadsheet, I have colored yellow every range that i consider to be
>> > User Input. I have also re-named each of these ranges.
>> >
>> > I want a button to create a new workbook. I want to copy the names of the
>> > ranges that are user input. I also want to copy their values. This is so
>> > later, I can call on this file and re-use this user input.
>> >
>> > One of the big issues I am encountering is that some of the Named Ranges are
>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>> > X 10 cells.
>> >
>> > Later, if the user wants to call on one of these files containing the user
>> > input, I want to cycle through the names of the ranges that we saved, and
>> > copy them back to the yellow cells of the same name. If for some reason,
>> > this yellow cell has changed names or been deleted, I want to just skip that
>> > and move to the next.
>> >
>> > Thanks for any help!
>> > Steve
>>
>>
>>

[Ron de Bruin]

Hi Steve

Not much time on this moment (my birthday today)

But this is my idea to save your data to a new file in the same cell locations
It not save the names but we not need that because the data is in the same cells.

The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp

Try it and let me know if this is a good start

Sub Copy_named_ranges()
Dim ws As Worksheet
Dim wb As Workbook
Dim Nwb As Workbook
Dim Nme As name

Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
Set Nwb = Workbooks.Add(xlWBATWorksheet)
wb.Activate

For Each Nme In wb.Names
If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
With ws.Range(Nme.name)
.Copy Destination:=Nwb.Sheets(1).Range(.Address)
End With
End If
Next

Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
Nwb.Close False
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> OK, I look at it after dinner
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "steve" <[email protected]> wrote in message news:[email protected]...
>> there about 30 user input cells that I have named. (these are individual cells)
>>
>> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
>> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
>> yellow for userinput.
>>
>> so in all, there are about 35 names that will be copied. down the road,
>> this may increase or decrease.
>>
>> Thanks,
>> Steve
>>
>> "Ron de Bruin" wrote:
>>
>>> How many cells are there in all range names together
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>>
>>> "steve" <[email protected]> wrote in message news:[email protected]...
>>> > I'm sorry for singling you out, but you've always given me great solutions.
>>> > This piece of code will be very crucial in my application.
>>> >
>>> > On a spreadsheet, I have colored yellow every range that i consider to be
>>> > User Input. I have also re-named each of these ranges.
>>> >
>>> > I want a button to create a new workbook. I want to copy the names of the
>>> > ranges that are user input. I also want to copy their values. This is so
>>> > later, I can call on this file and re-use this user input.
>>> >
>>> > One of the big issues I am encountering is that some of the Named Ranges are
>>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>>> > X 10 cells.
>>> >
>>> > Later, if the user wants to call on one of these files containing the user
>>> > input, I want to cycle through the names of the ranges that we saved, and
>>> > copy them back to the yellow cells of the same name. If for some reason,
>>> > this yellow cell has changed names or been deleted, I want to just skip that
>>> > and move to the next.
>>> >
>>> > Thanks for any help!
>>> > Steve
>>>
>>>
>>>
>
>

[Casey]

Happy Birthday Ron

[Ron de Bruin]

Thanks Casey

40 today, I am an old men now <g>


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Casey" <[email protected]> wrote in message
news:[email protected]...
>
> Happy Birthday Ron
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=570728
>

[Dave Peterson]

Happy Birthday!!

40 is young. Well, compared to lots of us <bg>.

Ron de Bruin wrote:
>
> Thanks Casey
>
> 40 today, I am an old men now <g>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
> "Casey" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Happy Birthday Ron
> >
> >
> > --
> > Casey
> >
> >
> > ------------------------------------------------------------------------
> > Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> > View this thread: http://www.excelforum.com/showthread...hreadid=570728
> >

--

Dave Peterson

[Ron de Bruin]

Thanks Dave

> 40 is young. Well, compared to lots of us <bg>.
I hope when I am as old as you I have also the same brains.



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Dave Peterson" <[email protected]> wrote in message news:[email protected]...
> Happy Birthday!!
>
> 40 is young. Well, compared to lots of us <bg>.
>
> Ron de Bruin wrote:
>>
>> Thanks Casey
>>
>> 40 today, I am an old men now <g>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>> "Casey" <[email protected]> wrote in message
>> news:[email protected]...
>> >
>> > Happy Birthday Ron
>> >
>> >
>> > --
>> > Casey
>> >
>> >
>> > ------------------------------------------------------------------------
>> > Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
>> > View this thread: http://www.excelforum.com/showthread...hreadid=570728
>> >
>
> --
>
> Dave Peterson

[Dave Peterson]

I'll take that as a compliment <vbg>.



Ron de Bruin wrote:
>
> Thanks Dave
>
> > 40 is young. Well, compared to lots of us <bg>.
> I hope when I am as old as you I have also the same brains.
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
> "Dave Peterson" <[email protected]> wrote in message news:[email protected]...
> > Happy Birthday!!
> >
> > 40 is young. Well, compared to lots of us <bg>.
> >
> > Ron de Bruin wrote:
> >>
> >> Thanks Casey
> >>
> >> 40 today, I am an old men now <g>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >> "Casey" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >
> >> > Happy Birthday Ron
> >> >
> >> >
> >> > --
> >> > Casey
> >> >
> >> >
> >> > ------------------------------------------------------------------------
> >> > Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> >> > View this thread: http://www.excelforum.com/showthread...hreadid=570728
> >> >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[steve]

Ron,

Happy Belated B-day!

Thanks for the start, but I think I did a bad job explaining exactly what I
need.

I am using a workbook to bring in data from other workbooks (we'll call it
"Book1") . Book1 will prompt the user to choose a file ("Book2"). When
Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
Each time this Named Range also exists in Book1, I want to copy the value
of the Named Range from Book2 to Book1.

This seems a bit confusing. This is a program that Creates Quotes.
Sometimes, we want to Re-Quote a project. Rather than type everythign in
again, I give the user the option to "Import data" from another file. The
reason i have to do it like this is that the data is not static enough to put
into a structured database. In other words, my boss changes the way we price
things (variables are added, deleted, renamed)

By importing data like this, I can bring in all of the data that is still
relevant, and ignore anything else that doesn't match up.

Thanks!
Steve

"Ron de Bruin" wrote:

> Hi Steve
>
> Not much time on this moment (my birthday today)
>
> But this is my idea to save your data to a new file in the same cell locations
> It not save the names but we not need that because the data is in the same cells.
>
> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
>
> Try it and let me know if this is a good start
>
> Sub Copy_named_ranges()
> Dim ws As Worksheet
> Dim wb As Workbook
> Dim Nwb As Workbook
> Dim Nme As name
>
> Set wb = ThisWorkbook
> Set ws = wb.Sheets("Sheet1")
> Set Nwb = Workbooks.Add(xlWBATWorksheet)
> wb.Activate
>
> For Each Nme In wb.Names
> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
> With ws.Range(Nme.name)
> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
> End With
> End If
> Next
>
> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
> Nwb.Close False
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> > OK, I look at it after dinner
> >
> >
> > --
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> >
> > "steve" <[email protected]> wrote in message news:[email protected]...
> >> there about 30 user input cells that I have named. (these are individual cells)
> >>
> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
> >> yellow for userinput.
> >>
> >> so in all, there are about 35 names that will be copied. down the road,
> >> this may increase or decrease.
> >>
> >> Thanks,
> >> Steve
> >>
> >> "Ron de Bruin" wrote:
> >>
> >>> How many cells are there in all range names together
> >>>
> >>> --
> >>> Regards Ron de Bruin
> >>> http://www.rondebruin.nl
> >>>
> >>>
> >>>
> >>> "steve" <[email protected]> wrote in message news:[email protected]...
> >>> > I'm sorry for singling you out, but you've always given me great solutions.
> >>> > This piece of code will be very crucial in my application.
> >>> >
> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
> >>> > User Input. I have also re-named each of these ranges.
> >>> >
> >>> > I want a button to create a new workbook. I want to copy the names of the
> >>> > ranges that are user input. I also want to copy their values. This is so
> >>> > later, I can call on this file and re-use this user input.
> >>> >
> >>> > One of the big issues I am encountering is that some of the Named Ranges are
> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
> >>> > X 10 cells.
> >>> >
> >>> > Later, if the user wants to call on one of these files containing the user
> >>> > input, I want to cycle through the names of the ranges that we saved, and
> >>> > copy them back to the yellow cells of the same name. If for some reason,
> >>> > this yellow cell has changed names or been deleted, I want to just skip that
> >>> > and move to the next.
> >>> >
> >>> > Thanks for any help!
> >>> > Steve
> >>>
> >>>
> >>>
> >
> >
>
>
>

[Ron de Bruin]

Hi Steve

I am confused

>I want a button to create a new workbook. I want to copy the names of the
>ranges that are user input. I also want to copy their values. This is so
>later, I can call on this file and re-use this user input.

Then

> Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> Each time this Named Range also exists in Book1, I want to copy the value
> of the Named Range from Book2 to Book1.

All named ranges are in both books then ?
Do you want to test if the named range have values, and copy if there are values. ?



--
Regards Ron de Bruin
http://www.rondebruin.nl



"steve" <[email protected]> wrote in message news:[email protected]...
> Ron,
>
> Happy Belated B-day!
>
> Thanks for the start, but I think I did a bad job explaining exactly what I
> need.
>
> I am using a workbook to bring in data from other workbooks (we'll call it
> "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
> Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> Each time this Named Range also exists in Book1, I want to copy the value
> of the Named Range from Book2 to Book1.
>
> This seems a bit confusing. This is a program that Creates Quotes.
> Sometimes, we want to Re-Quote a project. Rather than type everythign in
> again, I give the user the option to "Import data" from another file. The
> reason i have to do it like this is that the data is not static enough to put
> into a structured database. In other words, my boss changes the way we price
> things (variables are added, deleted, renamed)
>
> By importing data like this, I can bring in all of the data that is still
> relevant, and ignore anything else that doesn't match up.
>
> Thanks!
> Steve
>
> "Ron de Bruin" wrote:
>
>> Hi Steve
>>
>> Not much time on this moment (my birthday today)
>>
>> But this is my idea to save your data to a new file in the same cell locations
>> It not save the names but we not need that because the data is in the same cells.
>>
>> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
>>
>> Try it and let me know if this is a good start
>>
>> Sub Copy_named_ranges()
>> Dim ws As Worksheet
>> Dim wb As Workbook
>> Dim Nwb As Workbook
>> Dim Nme As name
>>
>> Set wb = ThisWorkbook
>> Set ws = wb.Sheets("Sheet1")
>> Set Nwb = Workbooks.Add(xlWBATWorksheet)
>> wb.Activate
>>
>> For Each Nme In wb.Names
>> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
>> With ws.Range(Nme.name)
>> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
>> End With
>> End If
>> Next
>>
>> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
>> Nwb.Close False
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> > OK, I look at it after dinner
>> >
>> >
>> > --
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> >
>> > "steve" <[email protected]> wrote in message news:[email protected]...
>> >> there about 30 user input cells that I have named. (these are individual cells)
>> >>
>> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
>> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
>> >> yellow for userinput.
>> >>
>> >> so in all, there are about 35 names that will be copied. down the road,
>> >> this may increase or decrease.
>> >>
>> >> Thanks,
>> >> Steve
>> >>
>> >> "Ron de Bruin" wrote:
>> >>
>> >>> How many cells are there in all range names together
>> >>>
>> >>> --
>> >>> Regards Ron de Bruin
>> >>> http://www.rondebruin.nl
>> >>>
>> >>>
>> >>>
>> >>> "steve" <[email protected]> wrote in message news:[email protected]...
>> >>> > I'm sorry for singling you out, but you've always given me great solutions.
>> >>> > This piece of code will be very crucial in my application.
>> >>> >
>> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
>> >>> > User Input. I have also re-named each of these ranges.
>> >>> >
>> >>> > I want a button to create a new workbook. I want to copy the names of the
>> >>> > ranges that are user input. I also want to copy their values. This is so
>> >>> > later, I can call on this file and re-use this user input.
>> >>> >
>> >>> > One of the big issues I am encountering is that some of the Named Ranges are
>> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>> >>> > X 10 cells.
>> >>> >
>> >>> > Later, if the user wants to call on one of these files containing the user
>> >>> > input, I want to cycle through the names of the ranges that we saved, and
>> >>> > copy them back to the yellow cells of the same name. If for some reason,
>> >>> > this yellow cell has changed names or been deleted, I want to just skip that
>> >>> > and move to the next.
>> >>> >
>> >>> > Thanks for any help!
>> >>> > Steve
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>

[steve]

sorry, this is confusing.

There are named ranges in both books. When the same name appears in both
books, i want to copy the value of this named range from Book2 to Book1.

so if the NamedRange "Customer" appears in both books:

Copy the value of Book2!Customer to Book1!Customer.

if a name in Book2 doesn't appear in Book1, skip it and check the next name.

"Ron de Bruin" wrote:

> Hi Steve
>
> I am confused
>
> >I want a button to create a new workbook. I want to copy the names of the
> >ranges that are user input. I also want to copy their values. This is so
> >later, I can call on this file and re-use this user input.
>
> Then
>
> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> > Each time this Named Range also exists in Book1, I want to copy the value
> > of the Named Range from Book2 to Book1.
>
> All named ranges are in both books then ?
> Do you want to test if the named range have values, and copy if there are values. ?
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "steve" <[email protected]> wrote in message news:[email protected]...
> > Ron,
> >
> > Happy Belated B-day!
> >
> > Thanks for the start, but I think I did a bad job explaining exactly what I
> > need.
> >
> > I am using a workbook to bring in data from other workbooks (we'll call it
> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> > Each time this Named Range also exists in Book1, I want to copy the value
> > of the Named Range from Book2 to Book1.
> >
> > This seems a bit confusing. This is a program that Creates Quotes.
> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
> > again, I give the user the option to "Import data" from another file. The
> > reason i have to do it like this is that the data is not static enough to put
> > into a structured database. In other words, my boss changes the way we price
> > things (variables are added, deleted, renamed)
> >
> > By importing data like this, I can bring in all of the data that is still
> > relevant, and ignore anything else that doesn't match up.
> >
> > Thanks!
> > Steve
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Steve
> >>
> >> Not much time on this moment (my birthday today)
> >>
> >> But this is my idea to save your data to a new file in the same cell locations
> >> It not save the names but we not need that because the data is in the same cells.
> >>
> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
> >>
> >> Try it and let me know if this is a good start
> >>
> >> Sub Copy_named_ranges()
> >> Dim ws As Worksheet
> >> Dim wb As Workbook
> >> Dim Nwb As Workbook
> >> Dim Nme As name
> >>
> >> Set wb = ThisWorkbook
> >> Set ws = wb.Sheets("Sheet1")
> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
> >> wb.Activate
> >>
> >> For Each Nme In wb.Names
> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
> >> With ws.Range(Nme.name)
> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
> >> End With
> >> End If
> >> Next
> >>
> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
> >> Nwb.Close False
> >> End Sub
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> >> > OK, I look at it after dinner
> >> >
> >> >
> >> > --
> >> > Regards Ron de Bruin
> >> > http://www.rondebruin.nl
> >> >
> >> >
> >> >
> >> > "steve" <[email protected]> wrote in message news:[email protected]...
> >> >> there about 30 user input cells that I have named. (these are individual cells)
> >> >>
> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
> >> >> yellow for userinput.
> >> >>
> >> >> so in all, there are about 35 names that will be copied. down the road,
> >> >> this may increase or decrease.
> >> >>
> >> >> Thanks,
> >> >> Steve
> >> >>
> >> >> "Ron de Bruin" wrote:
> >> >>
> >> >>> How many cells are there in all range names together
> >> >>>
> >> >>> --
> >> >>> Regards Ron de Bruin
> >> >>> http://www.rondebruin.nl
> >> >>>
> >> >>>
> >> >>>
> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
> >> >>> > This piece of code will be very crucial in my application.
> >> >>> >
> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
> >> >>> > User Input. I have also re-named each of these ranges.
> >> >>> >
> >> >>> > I want a button to create a new workbook. I want to copy the names of the
> >> >>> > ranges that are user input. I also want to copy their values. This is so
> >> >>> > later, I can call on this file and re-use this user input.
> >> >>> >
> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
> >> >>> > X 10 cells.
> >> >>> >
> >> >>> > Later, if the user wants to call on one of these files containing the user
> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
> >> >>> > and move to the next.
> >> >>> >
> >> >>> > Thanks for any help!
> >> >>> > Steve
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

[Ron de Bruin]

Hi Steve

Have both workbooks one sheet ?
If not are the sheet names or position the same ?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"steve" <[email protected]> wrote in message news:[email protected]...
> sorry, this is confusing.
>
> There are named ranges in both books. When the same name appears in both
> books, i want to copy the value of this named range from Book2 to Book1.
>
> so if the NamedRange "Customer" appears in both books:
>
> Copy the value of Book2!Customer to Book1!Customer.
>
> if a name in Book2 doesn't appear in Book1, skip it and check the next name.
>
> "Ron de Bruin" wrote:
>
>> Hi Steve
>>
>> I am confused
>>
>> >I want a button to create a new workbook. I want to copy the names of the
>> >ranges that are user input. I also want to copy their values. This is so
>> >later, I can call on this file and re-use this user input.
>>
>> Then
>>
>> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>> > Each time this Named Range also exists in Book1, I want to copy the value
>> > of the Named Range from Book2 to Book1.
>>
>> All named ranges are in both books then ?
>> Do you want to test if the named range have values, and copy if there are values. ?
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "steve" <[email protected]> wrote in message news:[email protected]...
>> > Ron,
>> >
>> > Happy Belated B-day!
>> >
>> > Thanks for the start, but I think I did a bad job explaining exactly what I
>> > need.
>> >
>> > I am using a workbook to bring in data from other workbooks (we'll call it
>> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
>> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>> > Each time this Named Range also exists in Book1, I want to copy the value
>> > of the Named Range from Book2 to Book1.
>> >
>> > This seems a bit confusing. This is a program that Creates Quotes.
>> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
>> > again, I give the user the option to "Import data" from another file. The
>> > reason i have to do it like this is that the data is not static enough to put
>> > into a structured database. In other words, my boss changes the way we price
>> > things (variables are added, deleted, renamed)
>> >
>> > By importing data like this, I can bring in all of the data that is still
>> > relevant, and ignore anything else that doesn't match up.
>> >
>> > Thanks!
>> > Steve
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi Steve
>> >>
>> >> Not much time on this moment (my birthday today)
>> >>
>> >> But this is my idea to save your data to a new file in the same cell locations
>> >> It not save the names but we not need that because the data is in the same cells.
>> >>
>> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
>> >>
>> >> Try it and let me know if this is a good start
>> >>
>> >> Sub Copy_named_ranges()
>> >> Dim ws As Worksheet
>> >> Dim wb As Workbook
>> >> Dim Nwb As Workbook
>> >> Dim Nme As name
>> >>
>> >> Set wb = ThisWorkbook
>> >> Set ws = wb.Sheets("Sheet1")
>> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
>> >> wb.Activate
>> >>
>> >> For Each Nme In wb.Names
>> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
>> >> With ws.Range(Nme.name)
>> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
>> >> End With
>> >> End If
>> >> Next
>> >>
>> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
>> >> Nwb.Close False
>> >> End Sub
>> >>
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >>
>> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> >> > OK, I look at it after dinner
>> >> >
>> >> >
>> >> > --
>> >> > Regards Ron de Bruin
>> >> > http://www.rondebruin.nl
>> >> >
>> >> >
>> >> >
>> >> > "steve" <[email protected]> wrote in message news:[email protected]...
>> >> >> there about 30 user input cells that I have named. (these are individual cells)
>> >> >>
>> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
>> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
>> >> >> yellow for userinput.
>> >> >>
>> >> >> so in all, there are about 35 names that will be copied. down the road,
>> >> >> this may increase or decrease.
>> >> >>
>> >> >> Thanks,
>> >> >> Steve
>> >> >>
>> >> >> "Ron de Bruin" wrote:
>> >> >>
>> >> >>> How many cells are there in all range names together
>> >> >>>
>> >> >>> --
>> >> >>> Regards Ron de Bruin
>> >> >>> http://www.rondebruin.nl
>> >> >>>
>> >> >>>
>> >> >>>
>> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
>> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
>> >> >>> > This piece of code will be very crucial in my application.
>> >> >>> >
>> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
>> >> >>> > User Input. I have also re-named each of these ranges.
>> >> >>> >
>> >> >>> > I want a button to create a new workbook. I want to copy the names of the
>> >> >>> > ranges that are user input. I also want to copy their values. This is so
>> >> >>> > later, I can call on this file and re-use this user input.
>> >> >>> >
>> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
>> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>> >> >>> > X 10 cells.
>> >> >>> >
>> >> >>> > Later, if the user wants to call on one of these files containing the user
>> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
>> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
>> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
>> >> >>> > and move to the next.
>> >> >>> >
>> >> >>> > Thanks for any help!
>> >> >>> > Steve
>> >> >>>
>> >> >>>
>> >> >>>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[steve]

Both books have more than one sheet.

The sheet in Book1 is "Caclulations"
The sheet in Book2 is "User Input"

let me know if the Sheets need to be the same to make the code smoother.

"Ron de Bruin" wrote:

> Hi Steve
>
> Have both workbooks one sheet ?
> If not are the sheet names or position the same ?
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "steve" <[email protected]> wrote in message news:[email protected]...
> > sorry, this is confusing.
> >
> > There are named ranges in both books. When the same name appears in both
> > books, i want to copy the value of this named range from Book2 to Book1.
> >
> > so if the NamedRange "Customer" appears in both books:
> >
> > Copy the value of Book2!Customer to Book1!Customer.
> >
> > if a name in Book2 doesn't appear in Book1, skip it and check the next name.
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Steve
> >>
> >> I am confused
> >>
> >> >I want a button to create a new workbook. I want to copy the names of the
> >> >ranges that are user input. I also want to copy their values. This is so
> >> >later, I can call on this file and re-use this user input.
> >>
> >> Then
> >>
> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> >> > Each time this Named Range also exists in Book1, I want to copy the value
> >> > of the Named Range from Book2 to Book1.
> >>
> >> All named ranges are in both books then ?
> >> Do you want to test if the named range have values, and copy if there are values. ?
> >>
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "steve" <[email protected]> wrote in message news:[email protected]...
> >> > Ron,
> >> >
> >> > Happy Belated B-day!
> >> >
> >> > Thanks for the start, but I think I did a bad job explaining exactly what I
> >> > need.
> >> >
> >> > I am using a workbook to bring in data from other workbooks (we'll call it
> >> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> >> > Each time this Named Range also exists in Book1, I want to copy the value
> >> > of the Named Range from Book2 to Book1.
> >> >
> >> > This seems a bit confusing. This is a program that Creates Quotes.
> >> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
> >> > again, I give the user the option to "Import data" from another file. The
> >> > reason i have to do it like this is that the data is not static enough to put
> >> > into a structured database. In other words, my boss changes the way we price
> >> > things (variables are added, deleted, renamed)
> >> >
> >> > By importing data like this, I can bring in all of the data that is still
> >> > relevant, and ignore anything else that doesn't match up.
> >> >
> >> > Thanks!
> >> > Steve
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Hi Steve
> >> >>
> >> >> Not much time on this moment (my birthday today)
> >> >>
> >> >> But this is my idea to save your data to a new file in the same cell locations
> >> >> It not save the names but we not need that because the data is in the same cells.
> >> >>
> >> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
> >> >>
> >> >> Try it and let me know if this is a good start
> >> >>
> >> >> Sub Copy_named_ranges()
> >> >> Dim ws As Worksheet
> >> >> Dim wb As Workbook
> >> >> Dim Nwb As Workbook
> >> >> Dim Nme As name
> >> >>
> >> >> Set wb = ThisWorkbook
> >> >> Set ws = wb.Sheets("Sheet1")
> >> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
> >> >> wb.Activate
> >> >>
> >> >> For Each Nme In wb.Names
> >> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
> >> >> With ws.Range(Nme.name)
> >> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
> >> >> End With
> >> >> End If
> >> >> Next
> >> >>
> >> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
> >> >> Nwb.Close False
> >> >> End Sub
> >> >>
> >> >>
> >> >> --
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl
> >> >>
> >> >>
> >> >>
> >> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> >> >> > OK, I look at it after dinner
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Regards Ron de Bruin
> >> >> > http://www.rondebruin.nl
> >> >> >
> >> >> >
> >> >> >
> >> >> > "steve" <[email protected]> wrote in message news:[email protected]...
> >> >> >> there about 30 user input cells that I have named. (these are individual cells)
> >> >> >>
> >> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
> >> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
> >> >> >> yellow for userinput.
> >> >> >>
> >> >> >> so in all, there are about 35 names that will be copied. down the road,
> >> >> >> this may increase or decrease.
> >> >> >>
> >> >> >> Thanks,
> >> >> >> Steve
> >> >> >>
> >> >> >> "Ron de Bruin" wrote:
> >> >> >>
> >> >> >>> How many cells are there in all range names together
> >> >> >>>
> >> >> >>> --
> >> >> >>> Regards Ron de Bruin
> >> >> >>> http://www.rondebruin.nl
> >> >> >>>
> >> >> >>>
> >> >> >>>
> >> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
> >> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
> >> >> >>> > This piece of code will be very crucial in my application.
> >> >> >>> >
> >> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
> >> >> >>> > User Input. I have also re-named each of these ranges.
> >> >> >>> >
> >> >> >>> > I want a button to create a new workbook. I want to copy the names of the
> >> >> >>> > ranges that are user input. I also want to copy their values. This is so
> >> >> >>> > later, I can call on this file and re-use this user input.
> >> >> >>> >
> >> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
> >> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
> >> >> >>> > X 10 cells.
> >> >> >>> >
> >> >> >>> > Later, if the user wants to call on one of these files containing the user
> >> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
> >> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
> >> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
> >> >> >>> > and move to the next.
> >> >> >>> >
> >> >> >>> > Thanks for any help!
> >> >> >>> > Steve
> >> >> >>>
> >> >> >>>
> >> >> >>>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Ron de Bruin]

I post a example this evening Steve

--
Regards Ron de Bruin
http://www.rondebruin.nl



"steve" <[email protected]> wrote in message news:[email protected]...
> Both books have more than one sheet.
>
> The sheet in Book1 is "Caclulations"
> The sheet in Book2 is "User Input"
>
> let me know if the Sheets need to be the same to make the code smoother.
>
> "Ron de Bruin" wrote:
>
>> Hi Steve
>>
>> Have both workbooks one sheet ?
>> If not are the sheet names or position the same ?
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "steve" <[email protected]> wrote in message news:[email protected]...
>> > sorry, this is confusing.
>> >
>> > There are named ranges in both books. When the same name appears in both
>> > books, i want to copy the value of this named range from Book2 to Book1.
>> >
>> > so if the NamedRange "Customer" appears in both books:
>> >
>> > Copy the value of Book2!Customer to Book1!Customer.
>> >
>> > if a name in Book2 doesn't appear in Book1, skip it and check the next name.
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi Steve
>> >>
>> >> I am confused
>> >>
>> >> >I want a button to create a new workbook. I want to copy the names of the
>> >> >ranges that are user input. I also want to copy their values. This is so
>> >> >later, I can call on this file and re-use this user input.
>> >>
>> >> Then
>> >>
>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>> >> > of the Named Range from Book2 to Book1.
>> >>
>> >> All named ranges are in both books then ?
>> >> Do you want to test if the named range have values, and copy if there are values. ?
>> >>
>> >>
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >>
>> >> "steve" <[email protected]> wrote in message news:[email protected]...
>> >> > Ron,
>> >> >
>> >> > Happy Belated B-day!
>> >> >
>> >> > Thanks for the start, but I think I did a bad job explaining exactly what I
>> >> > need.
>> >> >
>> >> > I am using a workbook to bring in data from other workbooks (we'll call it
>> >> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>> >> > of the Named Range from Book2 to Book1.
>> >> >
>> >> > This seems a bit confusing. This is a program that Creates Quotes.
>> >> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
>> >> > again, I give the user the option to "Import data" from another file. The
>> >> > reason i have to do it like this is that the data is not static enough to put
>> >> > into a structured database. In other words, my boss changes the way we price
>> >> > things (variables are added, deleted, renamed)
>> >> >
>> >> > By importing data like this, I can bring in all of the data that is still
>> >> > relevant, and ignore anything else that doesn't match up.
>> >> >
>> >> > Thanks!
>> >> > Steve
>> >> >
>> >> > "Ron de Bruin" wrote:
>> >> >
>> >> >> Hi Steve
>> >> >>
>> >> >> Not much time on this moment (my birthday today)
>> >> >>
>> >> >> But this is my idea to save your data to a new file in the same cell locations
>> >> >> It not save the names but we not need that because the data is in the same cells.
>> >> >>
>> >> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
>> >> >>
>> >> >> Try it and let me know if this is a good start
>> >> >>
>> >> >> Sub Copy_named_ranges()
>> >> >> Dim ws As Worksheet
>> >> >> Dim wb As Workbook
>> >> >> Dim Nwb As Workbook
>> >> >> Dim Nme As name
>> >> >>
>> >> >> Set wb = ThisWorkbook
>> >> >> Set ws = wb.Sheets("Sheet1")
>> >> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
>> >> >> wb.Activate
>> >> >>
>> >> >> For Each Nme In wb.Names
>> >> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
>> >> >> With ws.Range(Nme.name)
>> >> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
>> >> >> End With
>> >> >> End If
>> >> >> Next
>> >> >>
>> >> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
>> >> >> Nwb.Close False
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> >> >> > OK, I look at it after dinner
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > Regards Ron de Bruin
>> >> >> > http://www.rondebruin.nl
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "steve" <[email protected]> wrote in message news:[email protected]...
>> >> >> >> there about 30 user input cells that I have named. (these are individual cells)
>> >> >> >>
>> >> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
>> >> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
>> >> >> >> yellow for userinput.
>> >> >> >>
>> >> >> >> so in all, there are about 35 names that will be copied. down the road,
>> >> >> >> this may increase or decrease.
>> >> >> >>
>> >> >> >> Thanks,
>> >> >> >> Steve
>> >> >> >>
>> >> >> >> "Ron de Bruin" wrote:
>> >> >> >>
>> >> >> >>> How many cells are there in all range names together
>> >> >> >>>
>> >> >> >>> --
>> >> >> >>> Regards Ron de Bruin
>> >> >> >>> http://www.rondebruin.nl
>> >> >> >>>
>> >> >> >>>
>> >> >> >>>
>> >> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
>> >> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
>> >> >> >>> > This piece of code will be very crucial in my application.
>> >> >> >>> >
>> >> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
>> >> >> >>> > User Input. I have also re-named each of these ranges.
>> >> >> >>> >
>> >> >> >>> > I want a button to create a new workbook. I want to copy the names of the
>> >> >> >>> > ranges that are user input. I also want to copy their values. This is so
>> >> >> >>> > later, I can call on this file and re-use this user input.
>> >> >> >>> >
>> >> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
>> >> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>> >> >> >>> > X 10 cells.
>> >> >> >>> >
>> >> >> >>> > Later, if the user wants to call on one of these files containing the user
>> >> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
>> >> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
>> >> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
>> >> >> >>> > and move to the next.
>> >> >> >>> >
>> >> >> >>> > Thanks for any help!
>> >> >> >>> > Steve
>> >> >> >>>
>> >> >> >>>
>> >> >> >>>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

[Ron de Bruin]

Hi Steve

Test this one
Run the code in Book1


Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim Basebook As Workbook
Dim MyPath As String
Dim SaveDriveDir As String
Dim myName As name
Dim Nme As name
Dim RefStr As String

SaveDriveDir = CurDir
Set Basebook = ThisWorkbook

MyPath = "C:\"
ChDrive MyPath
ChDir MyPath

Application.ScreenUpdating = False

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
Set wb = Workbooks.Open(FName)

For Each Nme In Basebook.Names
Set myName = Nothing
On Error Resume Next
Set myName = wb.Names(Nme.name)
On Error GoTo 0

If myName Is Nothing Then
'do nothing
Else
Range(myName).Copy Destination:=Basebook.Sheets("Caclulations").Range(Nme.name).Cells(1)
End If
Next

wb.Close False

End If

Application.ScreenUpdating = True

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>I post a example this evening Steve
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "steve" <[email protected]> wrote in message news:[email protected]...
>> Both books have more than one sheet.
>>
>> The sheet in Book1 is "Caclulations"
>> The sheet in Book2 is "User Input"
>>
>> let me know if the Sheets need to be the same to make the code smoother.
>>
>> "Ron de Bruin" wrote:
>>
>>> Hi Steve
>>>
>>> Have both workbooks one sheet ?
>>> If not are the sheet names or position the same ?
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>>
>>> "steve" <[email protected]> wrote in message news:[email protected]...
>>> > sorry, this is confusing.
>>> >
>>> > There are named ranges in both books. When the same name appears in both
>>> > books, i want to copy the value of this named range from Book2 to Book1.
>>> >
>>> > so if the NamedRange "Customer" appears in both books:
>>> >
>>> > Copy the value of Book2!Customer to Book1!Customer.
>>> >
>>> > if a name in Book2 doesn't appear in Book1, skip it and check the next name.
>>> >
>>> > "Ron de Bruin" wrote:
>>> >
>>> >> Hi Steve
>>> >>
>>> >> I am confused
>>> >>
>>> >> >I want a button to create a new workbook. I want to copy the names of the
>>> >> >ranges that are user input. I also want to copy their values. This is so
>>> >> >later, I can call on this file and re-use this user input.
>>> >>
>>> >> Then
>>> >>
>>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>>> >> > of the Named Range from Book2 to Book1.
>>> >>
>>> >> All named ranges are in both books then ?
>>> >> Do you want to test if the named range have values, and copy if there are values. ?
>>> >>
>>> >>
>>> >>
>>> >> --
>>> >> Regards Ron de Bruin
>>> >> http://www.rondebruin.nl
>>> >>
>>> >>
>>> >>
>>> >> "steve" <[email protected]> wrote in message news:[email protected]...
>>> >> > Ron,
>>> >> >
>>> >> > Happy Belated B-day!
>>> >> >
>>> >> > Thanks for the start, but I think I did a bad job explaining exactly what I
>>> >> > need.
>>> >> >
>>> >> > I am using a workbook to bring in data from other workbooks (we'll call it
>>> >> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
>>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>>> >> > of the Named Range from Book2 to Book1.
>>> >> >
>>> >> > This seems a bit confusing. This is a program that Creates Quotes.
>>> >> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
>>> >> > again, I give the user the option to "Import data" from another file. The
>>> >> > reason i have to do it like this is that the data is not static enough to put
>>> >> > into a structured database. In other words, my boss changes the way we price
>>> >> > things (variables are added, deleted, renamed)
>>> >> >
>>> >> > By importing data like this, I can bring in all of the data that is still
>>> >> > relevant, and ignore anything else that doesn't match up.
>>> >> >
>>> >> > Thanks!
>>> >> > Steve
>>> >> >
>>> >> > "Ron de Bruin" wrote:
>>> >> >
>>> >> >> Hi Steve
>>> >> >>
>>> >> >> Not much time on this moment (my birthday today)
>>> >> >>
>>> >> >> But this is my idea to save your data to a new file in the same cell locations
>>> >> >> It not save the names but we not need that because the data is in the same cells.
>>> >> >>
>>> >> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
>>> >> >>
>>> >> >> Try it and let me know if this is a good start
>>> >> >>
>>> >> >> Sub Copy_named_ranges()
>>> >> >> Dim ws As Worksheet
>>> >> >> Dim wb As Workbook
>>> >> >> Dim Nwb As Workbook
>>> >> >> Dim Nme As name
>>> >> >>
>>> >> >> Set wb = ThisWorkbook
>>> >> >> Set ws = wb.Sheets("Sheet1")
>>> >> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
>>> >> >> wb.Activate
>>> >> >>
>>> >> >> For Each Nme In wb.Names
>>> >> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
>>> >> >> With ws.Range(Nme.name)
>>> >> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
>>> >> >> End With
>>> >> >> End If
>>> >> >> Next
>>> >> >>
>>> >> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
>>> >> >> Nwb.Close False
>>> >> >> End Sub
>>> >> >>
>>> >> >>
>>> >> >> --
>>> >> >> Regards Ron de Bruin
>>> >> >> http://www.rondebruin.nl
>>> >> >>
>>> >> >>
>>> >> >>
>>> >> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>> >> >> > OK, I look at it after dinner
>>> >> >> >
>>> >> >> >
>>> >> >> > --
>>> >> >> > Regards Ron de Bruin
>>> >> >> > http://www.rondebruin.nl
>>> >> >> >
>>> >> >> >
>>> >> >> >
>>> >> >> > "steve" <[email protected]> wrote in message news:[email protected]...
>>> >> >> >> there about 30 user input cells that I have named. (these are individual cells)
>>> >> >> >>
>>> >> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
>>> >> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
>>> >> >> >> yellow for userinput.
>>> >> >> >>
>>> >> >> >> so in all, there are about 35 names that will be copied. down the road,
>>> >> >> >> this may increase or decrease.
>>> >> >> >>
>>> >> >> >> Thanks,
>>> >> >> >> Steve
>>> >> >> >>
>>> >> >> >> "Ron de Bruin" wrote:
>>> >> >> >>
>>> >> >> >>> How many cells are there in all range names together
>>> >> >> >>>
>>> >> >> >>> --
>>> >> >> >>> Regards Ron de Bruin
>>> >> >> >>> http://www.rondebruin.nl
>>> >> >> >>>
>>> >> >> >>>
>>> >> >> >>>
>>> >> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
>>> >> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
>>> >> >> >>> > This piece of code will be very crucial in my application.
>>> >> >> >>> >
>>> >> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
>>> >> >> >>> > User Input. I have also re-named each of these ranges.
>>> >> >> >>> >
>>> >> >> >>> > I want a button to create a new workbook. I want to copy the names of the
>>> >> >> >>> > ranges that are user input. I also want to copy their values. This is so
>>> >> >> >>> > later, I can call on this file and re-use this user input.
>>> >> >> >>> >
>>> >> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
>>> >> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>>> >> >> >>> > X 10 cells.
>>> >> >> >>> >
>>> >> >> >>> > Later, if the user wants to call on one of these files containing the user
>>> >> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
>>> >> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
>>> >> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
>>> >> >> >>> > and move to the next.
>>> >> >> >>> >
>>> >> >> >>> > Thanks for any help!
>>> >> >> >>> > Steve
>>> >> >> >>>
>>> >> >> >>>
>>> >> >> >>>
>>> >> >> >
>>> >> >> >
>>> >> >>
>>> >> >>
>>> >> >>
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>

[steve]

ok Ron, we are on the same page now!

however, I am getting the following error at this line:

Range(myName).Copy
Destination:=Basebook.Sheets("Calculations").Range(Nme.Name).Cells(1)

Error: Method 'Range' of object '_Worksheet' failed




"Ron de Bruin" wrote:

> Hi Steve
>
> Test this one
> Run the code in Book1
>
>
> Sub test()
> Dim FName As Variant
> Dim wb As Workbook
> Dim Basebook As Workbook
> Dim MyPath As String
> Dim SaveDriveDir As String
> Dim myName As name
> Dim Nme As name
> Dim RefStr As String
>
> SaveDriveDir = CurDir
> Set Basebook = ThisWorkbook
>
> MyPath = "C:\"
> ChDrive MyPath
> ChDir MyPath
>
> Application.ScreenUpdating = False
>
> FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
> If FName <> False Then
> Set wb = Workbooks.Open(FName)
>
> For Each Nme In Basebook.Names
> Set myName = Nothing
> On Error Resume Next
> Set myName = wb.Names(Nme.name)
> On Error GoTo 0
>
> If myName Is Nothing Then
> 'do nothing
> Else
> Range(myName).Copy Destination:=Basebook.Sheets("Caclulations").Range(Nme.name).Cells(1)
> End If
> Next
>
> wb.Close False
>
> End If
>
> Application.ScreenUpdating = True
>
> ChDrive SaveDriveDir
> ChDir SaveDriveDir
>
> End Sub
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> >I post a example this evening Steve
> >
> > --
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> >
> > "steve" <[email protected]> wrote in message news:[email protected]...
> >> Both books have more than one sheet.
> >>
> >> The sheet in Book1 is "Caclulations"
> >> The sheet in Book2 is "User Input"
> >>
> >> let me know if the Sheets need to be the same to make the code smoother.
> >>
> >> "Ron de Bruin" wrote:
> >>
> >>> Hi Steve
> >>>
> >>> Have both workbooks one sheet ?
> >>> If not are the sheet names or position the same ?
> >>>
> >>> --
> >>> Regards Ron de Bruin
> >>> http://www.rondebruin.nl
> >>>
> >>>
> >>>
> >>> "steve" <[email protected]> wrote in message news:[email protected]...
> >>> > sorry, this is confusing.
> >>> >
> >>> > There are named ranges in both books. When the same name appears in both
> >>> > books, i want to copy the value of this named range from Book2 to Book1.
> >>> >
> >>> > so if the NamedRange "Customer" appears in both books:
> >>> >
> >>> > Copy the value of Book2!Customer to Book1!Customer.
> >>> >
> >>> > if a name in Book2 doesn't appear in Book1, skip it and check the next name.
> >>> >
> >>> > "Ron de Bruin" wrote:
> >>> >
> >>> >> Hi Steve
> >>> >>
> >>> >> I am confused
> >>> >>
> >>> >> >I want a button to create a new workbook. I want to copy the names of the
> >>> >> >ranges that are user input. I also want to copy their values. This is so
> >>> >> >later, I can call on this file and re-use this user input.
> >>> >>
> >>> >> Then
> >>> >>
> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
> >>> >> > of the Named Range from Book2 to Book1.
> >>> >>
> >>> >> All named ranges are in both books then ?
> >>> >> Do you want to test if the named range have values, and copy if there are values. ?
> >>> >>
> >>> >>
> >>> >>
> >>> >> --
> >>> >> Regards Ron de Bruin
> >>> >> http://www.rondebruin.nl
> >>> >>
> >>> >>
> >>> >>
> >>> >> "steve" <[email protected]> wrote in message news:[email protected]...
> >>> >> > Ron,
> >>> >> >
> >>> >> > Happy Belated B-day!
> >>> >> >
> >>> >> > Thanks for the start, but I think I did a bad job explaining exactly what I
> >>> >> > need.
> >>> >> >
> >>> >> > I am using a workbook to bring in data from other workbooks (we'll call it
> >>> >> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
> >>> >> > of the Named Range from Book2 to Book1.
> >>> >> >
> >>> >> > This seems a bit confusing. This is a program that Creates Quotes.
> >>> >> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
> >>> >> > again, I give the user the option to "Import data" from another file. The
> >>> >> > reason i have to do it like this is that the data is not static enough to put
> >>> >> > into a structured database. In other words, my boss changes the way we price
> >>> >> > things (variables are added, deleted, renamed)
> >>> >> >
> >>> >> > By importing data like this, I can bring in all of the data that is still
> >>> >> > relevant, and ignore anything else that doesn't match up.
> >>> >> >
> >>> >> > Thanks!
> >>> >> > Steve
> >>> >> >
> >>> >> > "Ron de Bruin" wrote:
> >>> >> >
> >>> >> >> Hi Steve
> >>> >> >>
> >>> >> >> Not much time on this moment (my birthday today)
> >>> >> >>
> >>> >> >> But this is my idea to save your data to a new file in the same cell locations
> >>> >> >> It not save the names but we not need that because the data is in the same cells.
> >>> >> >>
> >>> >> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
> >>> >> >>
> >>> >> >> Try it and let me know if this is a good start
> >>> >> >>
> >>> >> >> Sub Copy_named_ranges()
> >>> >> >> Dim ws As Worksheet
> >>> >> >> Dim wb As Workbook
> >>> >> >> Dim Nwb As Workbook
> >>> >> >> Dim Nme As name
> >>> >> >>
> >>> >> >> Set wb = ThisWorkbook
> >>> >> >> Set ws = wb.Sheets("Sheet1")
> >>> >> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
> >>> >> >> wb.Activate
> >>> >> >>
> >>> >> >> For Each Nme In wb.Names
> >>> >> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
> >>> >> >> With ws.Range(Nme.name)
> >>> >> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
> >>> >> >> End With
> >>> >> >> End If
> >>> >> >> Next
> >>> >> >>
> >>> >> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
> >>> >> >> Nwb.Close False
> >>> >> >> End Sub
> >>> >> >>
> >>> >> >>
> >>> >> >> --
> >>> >> >> Regards Ron de Bruin
> >>> >> >> http://www.rondebruin.nl
> >>> >> >>
> >>> >> >>
> >>> >> >>
> >>> >> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> >>> >> >> > OK, I look at it after dinner
> >>> >> >> >
> >>> >> >> >
> >>> >> >> > --
> >>> >> >> > Regards Ron de Bruin
> >>> >> >> > http://www.rondebruin.nl
> >>> >> >> >
> >>> >> >> >
> >>> >> >> >
> >>> >> >> > "steve" <[email protected]> wrote in message news:[email protected]...
> >>> >> >> >> there about 30 user input cells that I have named. (these are individual cells)
> >>> >> >> >>
> >>> >> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
> >>> >> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
> >>> >> >> >> yellow for userinput.
> >>> >> >> >>
> >>> >> >> >> so in all, there are about 35 names that will be copied. down the road,
> >>> >> >> >> this may increase or decrease.
> >>> >> >> >>
> >>> >> >> >> Thanks,
> >>> >> >> >> Steve
> >>> >> >> >>
> >>> >> >> >> "Ron de Bruin" wrote:
> >>> >> >> >>
> >>> >> >> >>> How many cells are there in all range names together
> >>> >> >> >>>
> >>> >> >> >>> --
> >>> >> >> >>> Regards Ron de Bruin
> >>> >> >> >>> http://www.rondebruin.nl
> >>> >> >> >>>
> >>> >> >> >>>
> >>> >> >> >>>
> >>> >> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
> >>> >> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
> >>> >> >> >>> > This piece of code will be very crucial in my application.
> >>> >> >> >>> >
> >>> >> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
> >>> >> >> >>> > User Input. I have also re-named each of these ranges.
> >>> >> >> >>> >
> >>> >> >> >>> > I want a button to create a new workbook. I want to copy the names of the
> >>> >> >> >>> > ranges that are user input. I also want to copy their values. This is so
> >>> >> >> >>> > later, I can call on this file and re-use this user input.
> >>> >> >> >>> >
> >>> >> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
> >>> >> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
> >>> >> >> >>> > X 10 cells.
> >>> >> >> >>> >
> >>> >> >> >>> > Later, if the user wants to call on one of these files containing the user
> >>> >> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
> >>> >> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
> >>> >> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
> >>> >> >> >>> > and move to the next.
> >>> >> >> >>> >
> >>> >> >> >>> > Thanks for any help!
> >>> >> >> >>> > Steve
> >>> >> >> >>>
> >>> >> >> >>>
> >>> >> >> >>>
> >>> >> >> >
> >>> >> >> >
> >>> >> >>
> >>> >> >>
> >>> >> >>
> >>> >>
> >>> >>
> >>> >>
> >>>
> >>>
> >>>
> >
> >
>
>
>

[Ron de Bruin]

Can you send me your workbook private Steve


--
Regards Ron de Bruin
http://www.rondebruin.nl



"steve" <[email protected]> wrote in message news:[email protected]...
> ok Ron, we are on the same page now!
>
> however, I am getting the following error at this line:
>
> Range(myName).Copy
> Destination:=Basebook.Sheets("Calculations").Range(Nme.Name).Cells(1)
>
> Error: Method 'Range' of object '_Worksheet' failed
>
>
>
>
> "Ron de Bruin" wrote:
>
>> Hi Steve
>>
>> Test this one
>> Run the code in Book1
>>
>>
>> Sub test()
>> Dim FName As Variant
>> Dim wb As Workbook
>> Dim Basebook As Workbook
>> Dim MyPath As String
>> Dim SaveDriveDir As String
>> Dim myName As name
>> Dim Nme As name
>> Dim RefStr As String
>>
>> SaveDriveDir = CurDir
>> Set Basebook = ThisWorkbook
>>
>> MyPath = "C:\"
>> ChDrive MyPath
>> ChDir MyPath
>>
>> Application.ScreenUpdating = False
>>
>> FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
>> If FName <> False Then
>> Set wb = Workbooks.Open(FName)
>>
>> For Each Nme In Basebook.Names
>> Set myName = Nothing
>> On Error Resume Next
>> Set myName = wb.Names(Nme.name)
>> On Error GoTo 0
>>
>> If myName Is Nothing Then
>> 'do nothing
>> Else
>> Range(myName).Copy Destination:=Basebook.Sheets("Caclulations").Range(Nme.name).Cells(1)
>> End If
>> Next
>>
>> wb.Close False
>>
>> End If
>>
>> Application.ScreenUpdating = True
>>
>> ChDrive SaveDriveDir
>> ChDir SaveDriveDir
>>
>> End Sub
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> >I post a example this evening Steve
>> >
>> > --
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> >
>> > "steve" <[email protected]> wrote in message news:[email protected]...
>> >> Both books have more than one sheet.
>> >>
>> >> The sheet in Book1 is "Caclulations"
>> >> The sheet in Book2 is "User Input"
>> >>
>> >> let me know if the Sheets need to be the same to make the code smoother.
>> >>
>> >> "Ron de Bruin" wrote:
>> >>
>> >>> Hi Steve
>> >>>
>> >>> Have both workbooks one sheet ?
>> >>> If not are the sheet names or position the same ?
>> >>>
>> >>> --
>> >>> Regards Ron de Bruin
>> >>> http://www.rondebruin.nl
>> >>>
>> >>>
>> >>>
>> >>> "steve" <[email protected]> wrote in message news:[email protected]...
>> >>> > sorry, this is confusing.
>> >>> >
>> >>> > There are named ranges in both books. When the same name appears in both
>> >>> > books, i want to copy the value of this named range from Book2 to Book1.
>> >>> >
>> >>> > so if the NamedRange "Customer" appears in both books:
>> >>> >
>> >>> > Copy the value of Book2!Customer to Book1!Customer.
>> >>> >
>> >>> > if a name in Book2 doesn't appear in Book1, skip it and check the next name.
>> >>> >
>> >>> > "Ron de Bruin" wrote:
>> >>> >
>> >>> >> Hi Steve
>> >>> >>
>> >>> >> I am confused
>> >>> >>
>> >>> >> >I want a button to create a new workbook. I want to copy the names of the
>> >>> >> >ranges that are user input. I also want to copy their values. This is so
>> >>> >> >later, I can call on this file and re-use this user input.
>> >>> >>
>> >>> >> Then
>> >>> >>
>> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>> >>> >> > of the Named Range from Book2 to Book1.
>> >>> >>
>> >>> >> All named ranges are in both books then ?
>> >>> >> Do you want to test if the named range have values, and copy if there are values. ?
>> >>> >>
>> >>> >>
>> >>> >>
>> >>> >> --
>> >>> >> Regards Ron de Bruin
>> >>> >> http://www.rondebruin.nl
>> >>> >>
>> >>> >>
>> >>> >>
>> >>> >> "steve" <[email protected]> wrote in message news:[email protected]...
>> >>> >> > Ron,
>> >>> >> >
>> >>> >> > Happy Belated B-day!
>> >>> >> >
>> >>> >> > Thanks for the start, but I think I did a bad job explaining exactly what I
>> >>> >> > need.
>> >>> >> >
>> >>> >> > I am using a workbook to bring in data from other workbooks (we'll call it
>> >>> >> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
>> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>> >>> >> > of the Named Range from Book2 to Book1.
>> >>> >> >
>> >>> >> > This seems a bit confusing. This is a program that Creates Quotes.
>> >>> >> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
>> >>> >> > again, I give the user the option to "Import data" from another file. The
>> >>> >> > reason i have to do it like this is that the data is not static enough to put
>> >>> >> > into a structured database. In other words, my boss changes the way we price
>> >>> >> > things (variables are added, deleted, renamed)
>> >>> >> >
>> >>> >> > By importing data like this, I can bring in all of the data that is still
>> >>> >> > relevant, and ignore anything else that doesn't match up.
>> >>> >> >
>> >>> >> > Thanks!
>> >>> >> > Steve
>> >>> >> >
>> >>> >> > "Ron de Bruin" wrote:
>> >>> >> >
>> >>> >> >> Hi Steve
>> >>> >> >>
>> >>> >> >> Not much time on this moment (my birthday today)
>> >>> >> >>
>> >>> >> >> But this is my idea to save your data to a new file in the same cell locations
>> >>> >> >> It not save the names but we not need that because the data is in the same cells.
>> >>> >> >>
>> >>> >> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
>> >>> >> >>
>> >>> >> >> Try it and let me know if this is a good start
>> >>> >> >>
>> >>> >> >> Sub Copy_named_ranges()
>> >>> >> >> Dim ws As Worksheet
>> >>> >> >> Dim wb As Workbook
>> >>> >> >> Dim Nwb As Workbook
>> >>> >> >> Dim Nme As name
>> >>> >> >>
>> >>> >> >> Set wb = ThisWorkbook
>> >>> >> >> Set ws = wb.Sheets("Sheet1")
>> >>> >> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
>> >>> >> >> wb.Activate
>> >>> >> >>
>> >>> >> >> For Each Nme In wb.Names
>> >>> >> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
>> >>> >> >> With ws.Range(Nme.name)
>> >>> >> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
>> >>> >> >> End With
>> >>> >> >> End If
>> >>> >> >> Next
>> >>> >> >>
>> >>> >> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
>> >>> >> >> Nwb.Close False
>> >>> >> >> End Sub
>> >>> >> >>
>> >>> >> >>
>> >>> >> >> --
>> >>> >> >> Regards Ron de Bruin
>> >>> >> >> http://www.rondebruin.nl
>> >>> >> >>
>> >>> >> >>
>> >>> >> >>
>> >>> >> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> >>> >> >> > OK, I look at it after dinner
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > --
>> >>> >> >> > Regards Ron de Bruin
>> >>> >> >> > http://www.rondebruin.nl
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >> > "steve" <[email protected]> wrote in message news:[email protected]...
>> >>> >> >> >> there about 30 user input cells that I have named. (these are individual cells)
>> >>> >> >> >>
>> >>> >> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
>> >>> >> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
>> >>> >> >> >> yellow for userinput.
>> >>> >> >> >>
>> >>> >> >> >> so in all, there are about 35 names that will be copied. down the road,
>> >>> >> >> >> this may increase or decrease.
>> >>> >> >> >>
>> >>> >> >> >> Thanks,
>> >>> >> >> >> Steve
>> >>> >> >> >>
>> >>> >> >> >> "Ron de Bruin" wrote:
>> >>> >> >> >>
>> >>> >> >> >>> How many cells are there in all range names together
>> >>> >> >> >>>
>> >>> >> >> >>> --
>> >>> >> >> >>> Regards Ron de Bruin
>> >>> >> >> >>> http://www.rondebruin.nl
>> >>> >> >> >>>
>> >>> >> >> >>>
>> >>> >> >> >>>
>> >>> >> >> >>> "steve" <[email protected]> wrote in message
>> >>> >> >> >>> news:[email protected]...
>> >>> >> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
>> >>> >> >> >>> > This piece of code will be very crucial in my application.
>> >>> >> >> >>> >
>> >>> >> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
>> >>> >> >> >>> > User Input. I have also re-named each of these ranges.
>> >>> >> >> >>> >
>> >>> >> >> >>> > I want a button to create a new workbook. I want to copy the names of the
>> >>> >> >> >>> > ranges that are user input. I also want to copy their values. This is so
>> >>> >> >> >>> > later, I can call on this file and re-use this user input.
>> >>> >> >> >>> >
>> >>> >> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
>> >>> >> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>> >>> >> >> >>> > X 10 cells.
>> >>> >> >> >>> >
>> >>> >> >> >>> > Later, if the user wants to call on one of these files containing the user
>> >>> >> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
>> >>> >> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
>> >>> >> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
>> >>> >> >> >>> > and move to the next.
>> >>> >> >> >>> >
>> >>> >> >> >>> > Thanks for any help!
>> >>> >> >> >>> > Steve
>> >>> >> >> >>>
>> >>> >> >> >>>
>> >>> >> >> >>>
>> >>> >> >> >
>> >>> >> >> >
>> >>> >> >>
>> >>> >> >>
>> >>> >> >>
>> >>> >>
>> >>> >>
>> >>> >>
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>

[steve]

i sent you an e-mail.. please confirm that you have received it.

"Ron de Bruin" wrote:

> Can you send me your workbook private Steve
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "steve" <[email protected]> wrote in message news:[email protected]...
> > ok Ron, we are on the same page now!
> >
> > however, I am getting the following error at this line:
> >
> > Range(myName).Copy
> > Destination:=Basebook.Sheets("Calculations").Range(Nme.Name).Cells(1)
> >
> > Error: Method 'Range' of object '_Worksheet' failed
> >
> >
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Steve
> >>
> >> Test this one
> >> Run the code in Book1
> >>
> >>
> >> Sub test()
> >> Dim FName As Variant
> >> Dim wb As Workbook
> >> Dim Basebook As Workbook
> >> Dim MyPath As String
> >> Dim SaveDriveDir As String
> >> Dim myName As name
> >> Dim Nme As name
> >> Dim RefStr As String
> >>
> >> SaveDriveDir = CurDir
> >> Set Basebook = ThisWorkbook
> >>
> >> MyPath = "C:\"
> >> ChDrive MyPath
> >> ChDir MyPath
> >>
> >> Application.ScreenUpdating = False
> >>
> >> FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
> >> If FName <> False Then
> >> Set wb = Workbooks.Open(FName)
> >>
> >> For Each Nme In Basebook.Names
> >> Set myName = Nothing
> >> On Error Resume Next
> >> Set myName = wb.Names(Nme.name)
> >> On Error GoTo 0
> >>
> >> If myName Is Nothing Then
> >> 'do nothing
> >> Else
> >> Range(myName).Copy Destination:=Basebook.Sheets("Caclulations").Range(Nme.name).Cells(1)
> >> End If
> >> Next
> >>
> >> wb.Close False
> >>
> >> End If
> >>
> >> Application.ScreenUpdating = True
> >>
> >> ChDrive SaveDriveDir
> >> ChDir SaveDriveDir
> >>
> >> End Sub
> >>
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> >> >I post a example this evening Steve
> >> >
> >> > --
> >> > Regards Ron de Bruin
> >> > http://www.rondebruin.nl
> >> >
> >> >
> >> >
> >> > "steve" <[email protected]> wrote in message news:[email protected]...
> >> >> Both books have more than one sheet.
> >> >>
> >> >> The sheet in Book1 is "Caclulations"
> >> >> The sheet in Book2 is "User Input"
> >> >>
> >> >> let me know if the Sheets need to be the same to make the code smoother.
> >> >>
> >> >> "Ron de Bruin" wrote:
> >> >>
> >> >>> Hi Steve
> >> >>>
> >> >>> Have both workbooks one sheet ?
> >> >>> If not are the sheet names or position the same ?
> >> >>>
> >> >>> --
> >> >>> Regards Ron de Bruin
> >> >>> http://www.rondebruin.nl
> >> >>>
> >> >>>
> >> >>>
> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
> >> >>> > sorry, this is confusing.
> >> >>> >
> >> >>> > There are named ranges in both books. When the same name appears in both
> >> >>> > books, i want to copy the value of this named range from Book2 to Book1.
> >> >>> >
> >> >>> > so if the NamedRange "Customer" appears in both books:
> >> >>> >
> >> >>> > Copy the value of Book2!Customer to Book1!Customer.
> >> >>> >
> >> >>> > if a name in Book2 doesn't appear in Book1, skip it and check the next name.
> >> >>> >
> >> >>> > "Ron de Bruin" wrote:
> >> >>> >
> >> >>> >> Hi Steve
> >> >>> >>
> >> >>> >> I am confused
> >> >>> >>
> >> >>> >> >I want a button to create a new workbook. I want to copy the names of the
> >> >>> >> >ranges that are user input. I also want to copy their values. This is so
> >> >>> >> >later, I can call on this file and re-use this user input.
> >> >>> >>
> >> >>> >> Then
> >> >>> >>
> >> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> >> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
> >> >>> >> > of the Named Range from Book2 to Book1.
> >> >>> >>
> >> >>> >> All named ranges are in both books then ?
> >> >>> >> Do you want to test if the named range have values, and copy if there are values. ?
> >> >>> >>
> >> >>> >>
> >> >>> >>
> >> >>> >> --
> >> >>> >> Regards Ron de Bruin
> >> >>> >> http://www.rondebruin.nl
> >> >>> >>
> >> >>> >>
> >> >>> >>
> >> >>> >> "steve" <[email protected]> wrote in message news:[email protected]...
> >> >>> >> > Ron,
> >> >>> >> >
> >> >>> >> > Happy Belated B-day!
> >> >>> >> >
> >> >>> >> > Thanks for the start, but I think I did a bad job explaining exactly what I
> >> >>> >> > need.
> >> >>> >> >
> >> >>> >> > I am using a workbook to bring in data from other workbooks (we'll call it
> >> >>> >> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
> >> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
> >> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
> >> >>> >> > of the Named Range from Book2 to Book1.
> >> >>> >> >
> >> >>> >> > This seems a bit confusing. This is a program that Creates Quotes.
> >> >>> >> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
> >> >>> >> > again, I give the user the option to "Import data" from another file. The
> >> >>> >> > reason i have to do it like this is that the data is not static enough to put
> >> >>> >> > into a structured database. In other words, my boss changes the way we price
> >> >>> >> > things (variables are added, deleted, renamed)
> >> >>> >> >
> >> >>> >> > By importing data like this, I can bring in all of the data that is still
> >> >>> >> > relevant, and ignore anything else that doesn't match up.
> >> >>> >> >
> >> >>> >> > Thanks!
> >> >>> >> > Steve
> >> >>> >> >
> >> >>> >> > "Ron de Bruin" wrote:
> >> >>> >> >
> >> >>> >> >> Hi Steve
> >> >>> >> >>
> >> >>> >> >> Not much time on this moment (my birthday today)
> >> >>> >> >>
> >> >>> >> >> But this is my idea to save your data to a new file in the same cell locations
> >> >>> >> >> It not save the names but we not need that because the data is in the same cells.
> >> >>> >> >>
> >> >>> >> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
> >> >>> >> >>
> >> >>> >> >> Try it and let me know if this is a good start
> >> >>> >> >>
> >> >>> >> >> Sub Copy_named_ranges()
> >> >>> >> >> Dim ws As Worksheet
> >> >>> >> >> Dim wb As Workbook
> >> >>> >> >> Dim Nwb As Workbook
> >> >>> >> >> Dim Nme As name
> >> >>> >> >>
> >> >>> >> >> Set wb = ThisWorkbook
> >> >>> >> >> Set ws = wb.Sheets("Sheet1")
> >> >>> >> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
> >> >>> >> >> wb.Activate
> >> >>> >> >>
> >> >>> >> >> For Each Nme In wb.Names
> >> >>> >> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
> >> >>> >> >> With ws.Range(Nme.name)
> >> >>> >> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
> >> >>> >> >> End With
> >> >>> >> >> End If
> >> >>> >> >> Next
> >> >>> >> >>
> >> >>> >> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
> >> >>> >> >> Nwb.Close False
> >> >>> >> >> End Sub
> >> >>> >> >>
> >> >>> >> >>
> >> >>> >> >> --
> >> >>> >> >> Regards Ron de Bruin
> >> >>> >> >> http://www.rondebruin.nl
> >> >>> >> >>
> >> >>> >> >>
> >> >>> >> >>
> >> >>> >> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> >> >>> >> >> > OK, I look at it after dinner
> >> >>> >> >> >
> >> >>> >> >> >
> >> >>> >> >> > --
> >> >>> >> >> > Regards Ron de Bruin
> >> >>> >> >> > http://www.rondebruin.nl
> >> >>> >> >> >
> >> >>> >> >> >
> >> >>> >> >> >
> >> >>> >> >> > "steve" <[email protected]> wrote in message news:[email protected]...
> >> >>> >> >> >> there about 30 user input cells that I have named. (these are individual cells)
> >> >>> >> >> >>
> >> >>> >> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
> >> >>> >> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
> >> >>> >> >> >> yellow for userinput.
> >> >>> >> >> >>
> >> >>> >> >> >> so in all, there are about 35 names that will be copied. down the road,
> >> >>> >> >> >> this may increase or decrease.
> >> >>> >> >> >>
> >> >>> >> >> >> Thanks,
> >> >>> >> >> >> Steve
> >> >>> >> >> >>
> >> >>> >> >> >> "Ron de Bruin" wrote:
> >> >>> >> >> >>
> >> >>> >> >> >>> How many cells are there in all range names together
> >> >>> >> >> >>>
> >> >>> >> >> >>> --
> >> >>> >> >> >>> Regards Ron de Bruin
> >> >>> >> >> >>> http://www.rondebruin.nl
> >> >>> >> >> >>>
> >> >>> >> >> >>>
> >> >>> >> >> >>>
> >> >>> >> >> >>> "steve" <[email protected]> wrote in message
> >> >>> >> >> >>> news:[email protected]...
> >> >>> >> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
> >> >>> >> >> >>> > This piece of code will be very crucial in my application.
> >> >>> >> >> >>> >
> >> >>> >> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
> >> >>> >> >> >>> > User Input. I have also re-named each of these ranges.
> >> >>> >> >> >>> >
> >> >>> >> >> >>> > I want a button to create a new workbook. I want to copy the names of the
> >> >>> >> >> >>> > ranges that are user input. I also want to copy their values. This is so
> >> >>> >> >> >>> > later, I can call on this file and re-use this user input.
> >> >>> >> >> >>> >
> >> >>> >> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
> >> >>> >> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
> >> >>> >> >> >>> > X 10 cells.
> >> >>> >> >> >>> >
> >> >>> >> >> >>> > Later, if the user wants to call on one of these files containing the user
> >> >>> >> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
> >> >>> >> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
> >> >>> >> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
> >> >>> >> >> >>> > and move to the next.
> >> >>> >> >> >>> >
> >> >>> >> >> >>> > Thanks for any help!
> >> >>> >> >> >>> > Steve
> >> >>> >> >> >>>
> >> >>> >> >> >>>
> >> >>> >> >> >>>
> >> >>> >> >> >
> >> >>> >> >> >
> >> >>> >> >>
> >> >>> >> >>
> >> >>> >> >>
> >> >>> >>
> >> >>> >>
> >> >>> >>
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>

[Ron de Bruin]

>i sent you an e-mail.. please confirm that you have received it
Yep, I reply to you this evening



--
Regards Ron de Bruin
http://www.rondebruin.nl



"steve" <[email protected]> wrote in message news:[email protected]...
>i sent you an e-mail.. please confirm that you have received it.
>
> "Ron de Bruin" wrote:
>
>> Can you send me your workbook private Steve
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "steve" <[email protected]> wrote in message news:[email protected]...
>> > ok Ron, we are on the same page now!
>> >
>> > however, I am getting the following error at this line:
>> >
>> > Range(myName).Copy
>> > Destination:=Basebook.Sheets("Calculations").Range(Nme.Name).Cells(1)
>> >
>> > Error: Method 'Range' of object '_Worksheet' failed
>> >
>> >
>> >
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi Steve
>> >>
>> >> Test this one
>> >> Run the code in Book1
>> >>
>> >>
>> >> Sub test()
>> >> Dim FName As Variant
>> >> Dim wb As Workbook
>> >> Dim Basebook As Workbook
>> >> Dim MyPath As String
>> >> Dim SaveDriveDir As String
>> >> Dim myName As name
>> >> Dim Nme As name
>> >> Dim RefStr As String
>> >>
>> >> SaveDriveDir = CurDir
>> >> Set Basebook = ThisWorkbook
>> >>
>> >> MyPath = "C:\"
>> >> ChDrive MyPath
>> >> ChDir MyPath
>> >>
>> >> Application.ScreenUpdating = False
>> >>
>> >> FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
>> >> If FName <> False Then
>> >> Set wb = Workbooks.Open(FName)
>> >>
>> >> For Each Nme In Basebook.Names
>> >> Set myName = Nothing
>> >> On Error Resume Next
>> >> Set myName = wb.Names(Nme.name)
>> >> On Error GoTo 0
>> >>
>> >> If myName Is Nothing Then
>> >> 'do nothing
>> >> Else
>> >> Range(myName).Copy Destination:=Basebook.Sheets("Caclulations").Range(Nme.name).Cells(1)
>> >> End If
>> >> Next
>> >>
>> >> wb.Close False
>> >>
>> >> End If
>> >>
>> >> Application.ScreenUpdating = True
>> >>
>> >> ChDrive SaveDriveDir
>> >> ChDir SaveDriveDir
>> >>
>> >> End Sub
>> >>
>> >>
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >>
>> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> >> >I post a example this evening Steve
>> >> >
>> >> > --
>> >> > Regards Ron de Bruin
>> >> > http://www.rondebruin.nl
>> >> >
>> >> >
>> >> >
>> >> > "steve" <[email protected]> wrote in message news:[email protected]...
>> >> >> Both books have more than one sheet.
>> >> >>
>> >> >> The sheet in Book1 is "Caclulations"
>> >> >> The sheet in Book2 is "User Input"
>> >> >>
>> >> >> let me know if the Sheets need to be the same to make the code smoother.
>> >> >>
>> >> >> "Ron de Bruin" wrote:
>> >> >>
>> >> >>> Hi Steve
>> >> >>>
>> >> >>> Have both workbooks one sheet ?
>> >> >>> If not are the sheet names or position the same ?
>> >> >>>
>> >> >>> --
>> >> >>> Regards Ron de Bruin
>> >> >>> http://www.rondebruin.nl
>> >> >>>
>> >> >>>
>> >> >>>
>> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
>> >> >>> > sorry, this is confusing.
>> >> >>> >
>> >> >>> > There are named ranges in both books. When the same name appears in both
>> >> >>> > books, i want to copy the value of this named range from Book2 to Book1.
>> >> >>> >
>> >> >>> > so if the NamedRange "Customer" appears in both books:
>> >> >>> >
>> >> >>> > Copy the value of Book2!Customer to Book1!Customer.
>> >> >>> >
>> >> >>> > if a name in Book2 doesn't appear in Book1, skip it and check the next name.
>> >> >>> >
>> >> >>> > "Ron de Bruin" wrote:
>> >> >>> >
>> >> >>> >> Hi Steve
>> >> >>> >>
>> >> >>> >> I am confused
>> >> >>> >>
>> >> >>> >> >I want a button to create a new workbook. I want to copy the names of the
>> >> >>> >> >ranges that are user input. I also want to copy their values. This is so
>> >> >>> >> >later, I can call on this file and re-use this user input.
>> >> >>> >>
>> >> >>> >> Then
>> >> >>> >>
>> >> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>> >> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>> >> >>> >> > of the Named Range from Book2 to Book1.
>> >> >>> >>
>> >> >>> >> All named ranges are in both books then ?
>> >> >>> >> Do you want to test if the named range have values, and copy if there are values. ?
>> >> >>> >>
>> >> >>> >>
>> >> >>> >>
>> >> >>> >> --
>> >> >>> >> Regards Ron de Bruin
>> >> >>> >> http://www.rondebruin.nl
>> >> >>> >>
>> >> >>> >>
>> >> >>> >>
>> >> >>> >> "steve" <[email protected]> wrote in message news:[email protected]...
>> >> >>> >> > Ron,
>> >> >>> >> >
>> >> >>> >> > Happy Belated B-day!
>> >> >>> >> >
>> >> >>> >> > Thanks for the start, but I think I did a bad job explaining exactly what I
>> >> >>> >> > need.
>> >> >>> >> >
>> >> >>> >> > I am using a workbook to bring in data from other workbooks (we'll call it
>> >> >>> >> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
>> >> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>> >> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>> >> >>> >> > of the Named Range from Book2 to Book1.
>> >> >>> >> >
>> >> >>> >> > This seems a bit confusing. This is a program that Creates Quotes.
>> >> >>> >> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
>> >> >>> >> > again, I give the user the option to "Import data" from another file. The
>> >> >>> >> > reason i have to do it like this is that the data is not static enough to put
>> >> >>> >> > into a structured database. In other words, my boss changes the way we price
>> >> >>> >> > things (variables are added, deleted, renamed)
>> >> >>> >> >
>> >> >>> >> > By importing data like this, I can bring in all of the data that is still
>> >> >>> >> > relevant, and ignore anything else that doesn't match up.
>> >> >>> >> >
>> >> >>> >> > Thanks!
>> >> >>> >> > Steve
>> >> >>> >> >
>> >> >>> >> > "Ron de Bruin" wrote:
>> >> >>> >> >
>> >> >>> >> >> Hi Steve
>> >> >>> >> >>
>> >> >>> >> >> Not much time on this moment (my birthday today)
>> >> >>> >> >>
>> >> >>> >> >> But this is my idea to save your data to a new file in the same cell locations
>> >> >>> >> >> It not save the names but we not need that because the data is in the same cells.
>> >> >>> >> >>
>> >> >>> >> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
>> >> >>> >> >>
>> >> >>> >> >> Try it and let me know if this is a good start
>> >> >>> >> >>
>> >> >>> >> >> Sub Copy_named_ranges()
>> >> >>> >> >> Dim ws As Worksheet
>> >> >>> >> >> Dim wb As Workbook
>> >> >>> >> >> Dim Nwb As Workbook
>> >> >>> >> >> Dim Nme As name
>> >> >>> >> >>
>> >> >>> >> >> Set wb = ThisWorkbook
>> >> >>> >> >> Set ws = wb.Sheets("Sheet1")
>> >> >>> >> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
>> >> >>> >> >> wb.Activate
>> >> >>> >> >>
>> >> >>> >> >> For Each Nme In wb.Names
>> >> >>> >> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
>> >> >>> >> >> With ws.Range(Nme.name)
>> >> >>> >> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
>> >> >>> >> >> End With
>> >> >>> >> >> End If
>> >> >>> >> >> Next
>> >> >>> >> >>
>> >> >>> >> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
>> >> >>> >> >> Nwb.Close False
>> >> >>> >> >> End Sub
>> >> >>> >> >>
>> >> >>> >> >>
>> >> >>> >> >> --
>> >> >>> >> >> Regards Ron de Bruin
>> >> >>> >> >> http://www.rondebruin.nl
>> >> >>> >> >>
>> >> >>> >> >>
>> >> >>> >> >>
>> >> >>> >> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> >> >>> >> >> > OK, I look at it after dinner
>> >> >>> >> >> >
>> >> >>> >> >> >
>> >> >>> >> >> > --
>> >> >>> >> >> > Regards Ron de Bruin
>> >> >>> >> >> > http://www.rondebruin.nl
>> >> >>> >> >> >
>> >> >>> >> >> >
>> >> >>> >> >> >
>> >> >>> >> >> > "steve" <[email protected]> wrote in message
>> >> >>> >> >> > news:[email protected]...
>> >> >>> >> >> >> there about 30 user input cells that I have named. (these are individual cells)
>> >> >>> >> >> >>
>> >> >>> >> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
>> >> >>> >> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
>> >> >>> >> >> >> yellow for userinput.
>> >> >>> >> >> >>
>> >> >>> >> >> >> so in all, there are about 35 names that will be copied. down the road,
>> >> >>> >> >> >> this may increase or decrease.
>> >> >>> >> >> >>
>> >> >>> >> >> >> Thanks,
>> >> >>> >> >> >> Steve
>> >> >>> >> >> >>
>> >> >>> >> >> >> "Ron de Bruin" wrote:
>> >> >>> >> >> >>
>> >> >>> >> >> >>> How many cells are there in all range names together
>> >> >>> >> >> >>>
>> >> >>> >> >> >>> --
>> >> >>> >> >> >>> Regards Ron de Bruin
>> >> >>> >> >> >>> http://www.rondebruin.nl
>> >> >>> >> >> >>>
>> >> >>> >> >> >>>
>> >> >>> >> >> >>>
>> >> >>> >> >> >>> "steve" <[email protected]> wrote in message
>> >> >>> >> >> >>> news:[email protected]...
>> >> >>> >> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
>> >> >>> >> >> >>> > This piece of code will be very crucial in my application.
>> >> >>> >> >> >>> >
>> >> >>> >> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
>> >> >>> >> >> >>> > User Input. I have also re-named each of these ranges.
>> >> >>> >> >> >>> >
>> >> >>> >> >> >>> > I want a button to create a new workbook. I want to copy the names of the
>> >> >>> >> >> >>> > ranges that are user input. I also want to copy their values. This is so
>> >> >>> >> >> >>> > later, I can call on this file and re-use this user input.
>> >> >>> >> >> >>> >
>> >> >>> >> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
>> >> >>> >> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>> >> >>> >> >> >>> > X 10 cells.
>> >> >>> >> >> >>> >
>> >> >>> >> >> >>> > Later, if the user wants to call on one of these files containing the user
>> >> >>> >> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
>> >> >>> >> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
>> >> >>> >> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
>> >> >>> >> >> >>> > and move to the next.
>> >> >>> >> >> >>> >
>> >> >>> >> >> >>> > Thanks for any help!
>> >> >>> >> >> >>> > Steve
>> >> >>> >> >> >>>
>> >> >>> >> >> >>>
>> >> >>> >> >> >>>
>> >> >>> >> >> >
>> >> >>> >> >> >
>> >> >>> >> >>
>> >> >>> >> >>
>> >> >>> >> >>
>> >> >>> >>
>> >> >>> >>
>> >> >>> >>
>> >> >>>
>> >> >>>
>> >> >>>
>> >> >
>> >> >
>> >>
>> >>
>> >>

[Ron de Bruin]

This is working for Steve now

The first copy all named ranges from the Sheets("Calculations") that are yellow (input cells) to a new workbook for backup
The second macro you can use to get the values from the named ranges from the backup workbooks into the main workbook (basebook)

Sub Copy_named_ranges2()
Dim ws As Worksheet
Dim wb As Workbook
Dim Nwb As Workbook
Dim Nme As Name

Set wb = ThisWorkbook
Set ws = wb.Sheets("Calculations")
Set Nwb = Workbooks.Add(xlWBATWorksheet)
wb.Activate

For Each Nme In wb.Names
If Left(Nme, Len(ws.Name) + 1) = "=" & ws.Name Then
With ws.Range(Nme.Name)
If .Cells(1).Interior.ColorIndex = 19 Then
.Copy Destination:=Nwb.Sheets(1).Range(.Address)
Nwb.Sheets(1).Range(.Address).Name = Nme.Name
End If
End With
End If
Next

Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
Nwb.Close False
End Sub


Sub Get_Data_From_Named_Ranges()
Dim FName As Variant
Dim wb As Workbook
Dim Basebook As Workbook
Dim MyPath As String
Dim SaveDriveDir As String
Dim myName As Name
Dim Nme As Name
Dim rng As Range

SaveDriveDir = CurDir
Set Basebook = ThisWorkbook

MyPath = "C:\"
ChDrive MyPath
ChDir MyPath

Application.ScreenUpdating = False

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
Set wb = Workbooks.Open(FName)

For Each Nme In Basebook.Names
Set myName = Nothing
On Error Resume Next
Set myName = wb.Names(Nme.Name)
On Error GoTo 0

If myName Is Nothing Then
'do nothing
Else
wb.Sheets(1).Range(Nme.Name).Copy Destination:=Basebook.Sheets("Calculations").Range(Nme.Name)
End If
Next

wb.Close False

End If

Application.ScreenUpdating = True

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> >i sent you an e-mail.. please confirm that you have received it
> Yep, I reply to you this evening
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "steve" <[email protected]> wrote in message news:[email protected]...
>>i sent you an e-mail.. please confirm that you have received it.
>>
>> "Ron de Bruin" wrote:
>>
>>> Can you send me your workbook private Steve
>>>
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>>
>>> "steve" <[email protected]> wrote in message news:[email protected]...
>>> > ok Ron, we are on the same page now!
>>> >
>>> > however, I am getting the following error at this line:
>>> >
>>> > Range(myName).Copy
>>> > Destination:=Basebook.Sheets("Calculations").Range(Nme.Name).Cells(1)
>>> >
>>> > Error: Method 'Range' of object '_Worksheet' failed
>>> >
>>> >
>>> >
>>> >
>>> > "Ron de Bruin" wrote:
>>> >
>>> >> Hi Steve
>>> >>
>>> >> Test this one
>>> >> Run the code in Book1
>>> >>
>>> >>
>>> >> Sub test()
>>> >> Dim FName As Variant
>>> >> Dim wb As Workbook
>>> >> Dim Basebook As Workbook
>>> >> Dim MyPath As String
>>> >> Dim SaveDriveDir As String
>>> >> Dim myName As name
>>> >> Dim Nme As name
>>> >> Dim RefStr As String
>>> >>
>>> >> SaveDriveDir = CurDir
>>> >> Set Basebook = ThisWorkbook
>>> >>
>>> >> MyPath = "C:\"
>>> >> ChDrive MyPath
>>> >> ChDir MyPath
>>> >>
>>> >> Application.ScreenUpdating = False
>>> >>
>>> >> FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
>>> >> If FName <> False Then
>>> >> Set wb = Workbooks.Open(FName)
>>> >>
>>> >> For Each Nme In Basebook.Names
>>> >> Set myName = Nothing
>>> >> On Error Resume Next
>>> >> Set myName = wb.Names(Nme.name)
>>> >> On Error GoTo 0
>>> >>
>>> >> If myName Is Nothing Then
>>> >> 'do nothing
>>> >> Else
>>> >> Range(myName).Copy Destination:=Basebook.Sheets("Caclulations").Range(Nme.name).Cells(1)
>>> >> End If
>>> >> Next
>>> >>
>>> >> wb.Close False
>>> >>
>>> >> End If
>>> >>
>>> >> Application.ScreenUpdating = True
>>> >>
>>> >> ChDrive SaveDriveDir
>>> >> ChDir SaveDriveDir
>>> >>
>>> >> End Sub
>>> >>
>>> >>
>>> >>
>>> >> --
>>> >> Regards Ron de Bruin
>>> >> http://www.rondebruin.nl
>>> >>
>>> >>
>>> >>
>>> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>> >> >I post a example this evening Steve
>>> >> >
>>> >> > --
>>> >> > Regards Ron de Bruin
>>> >> > http://www.rondebruin.nl
>>> >> >
>>> >> >
>>> >> >
>>> >> > "steve" <[email protected]> wrote in message news:[email protected]...
>>> >> >> Both books have more than one sheet.
>>> >> >>
>>> >> >> The sheet in Book1 is "Caclulations"
>>> >> >> The sheet in Book2 is "User Input"
>>> >> >>
>>> >> >> let me know if the Sheets need to be the same to make the code smoother.
>>> >> >>
>>> >> >> "Ron de Bruin" wrote:
>>> >> >>
>>> >> >>> Hi Steve
>>> >> >>>
>>> >> >>> Have both workbooks one sheet ?
>>> >> >>> If not are the sheet names or position the same ?
>>> >> >>>
>>> >> >>> --
>>> >> >>> Regards Ron de Bruin
>>> >> >>> http://www.rondebruin.nl
>>> >> >>>
>>> >> >>>
>>> >> >>>
>>> >> >>> "steve" <[email protected]> wrote in message news:[email protected]...
>>> >> >>> > sorry, this is confusing.
>>> >> >>> >
>>> >> >>> > There are named ranges in both books. When the same name appears in both
>>> >> >>> > books, i want to copy the value of this named range from Book2 to Book1.
>>> >> >>> >
>>> >> >>> > so if the NamedRange "Customer" appears in both books:
>>> >> >>> >
>>> >> >>> > Copy the value of Book2!Customer to Book1!Customer.
>>> >> >>> >
>>> >> >>> > if a name in Book2 doesn't appear in Book1, skip it and check the next name.
>>> >> >>> >
>>> >> >>> > "Ron de Bruin" wrote:
>>> >> >>> >
>>> >> >>> >> Hi Steve
>>> >> >>> >>
>>> >> >>> >> I am confused
>>> >> >>> >>
>>> >> >>> >> >I want a button to create a new workbook. I want to copy the names of the
>>> >> >>> >> >ranges that are user input. I also want to copy their values. This is so
>>> >> >>> >> >later, I can call on this file and re-use this user input.
>>> >> >>> >>
>>> >> >>> >> Then
>>> >> >>> >>
>>> >> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>>> >> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>>> >> >>> >> > of the Named Range from Book2 to Book1.
>>> >> >>> >>
>>> >> >>> >> All named ranges are in both books then ?
>>> >> >>> >> Do you want to test if the named range have values, and copy if there are values. ?
>>> >> >>> >>
>>> >> >>> >>
>>> >> >>> >>
>>> >> >>> >> --
>>> >> >>> >> Regards Ron de Bruin
>>> >> >>> >> http://www.rondebruin.nl
>>> >> >>> >>
>>> >> >>> >>
>>> >> >>> >>
>>> >> >>> >> "steve" <[email protected]> wrote in message news:[email protected]...
>>> >> >>> >> > Ron,
>>> >> >>> >> >
>>> >> >>> >> > Happy Belated B-day!
>>> >> >>> >> >
>>> >> >>> >> > Thanks for the start, but I think I did a bad job explaining exactly what I
>>> >> >>> >> > need.
>>> >> >>> >> >
>>> >> >>> >> > I am using a workbook to bring in data from other workbooks (we'll call it
>>> >> >>> >> > "Book1") . Book1 will prompt the user to choose a file ("Book2"). When
>>> >> >>> >> > Book2 is selected & opened, I want to check the Named Ranges in Book2.Sheet1.
>>> >> >>> >> > Each time this Named Range also exists in Book1, I want to copy the value
>>> >> >>> >> > of the Named Range from Book2 to Book1.
>>> >> >>> >> >
>>> >> >>> >> > This seems a bit confusing. This is a program that Creates Quotes.
>>> >> >>> >> > Sometimes, we want to Re-Quote a project. Rather than type everythign in
>>> >> >>> >> > again, I give the user the option to "Import data" from another file. The
>>> >> >>> >> > reason i have to do it like this is that the data is not static enough to put
>>> >> >>> >> > into a structured database. In other words, my boss changes the way we price
>>> >> >>> >> > things (variables are added, deleted, renamed)
>>> >> >>> >> >
>>> >> >>> >> > By importing data like this, I can bring in all of the data that is still
>>> >> >>> >> > relevant, and ignore anything else that doesn't match up.
>>> >> >>> >> >
>>> >> >>> >> > Thanks!
>>> >> >>> >> > Steve
>>> >> >>> >> >
>>> >> >>> >> > "Ron de Bruin" wrote:
>>> >> >>> >> >
>>> >> >>> >> >> Hi Steve
>>> >> >>> >> >>
>>> >> >>> >> >> Not much time on this moment (my birthday today)
>>> >> >>> >> >>
>>> >> >>> >> >> But this is my idea to save your data to a new file in the same cell locations
>>> >> >>> >> >> It not save the names but we not need that because the data is in the same cells.
>>> >> >>> >> >>
>>> >> >>> >> >> The Data is on a sheet named Sheet1 in my example and I save the files in C:\ with a date/time stamp
>>> >> >>> >> >>
>>> >> >>> >> >> Try it and let me know if this is a good start
>>> >> >>> >> >>
>>> >> >>> >> >> Sub Copy_named_ranges()
>>> >> >>> >> >> Dim ws As Worksheet
>>> >> >>> >> >> Dim wb As Workbook
>>> >> >>> >> >> Dim Nwb As Workbook
>>> >> >>> >> >> Dim Nme As name
>>> >> >>> >> >>
>>> >> >>> >> >> Set wb = ThisWorkbook
>>> >> >>> >> >> Set ws = wb.Sheets("Sheet1")
>>> >> >>> >> >> Set Nwb = Workbooks.Add(xlWBATWorksheet)
>>> >> >>> >> >> wb.Activate
>>> >> >>> >> >>
>>> >> >>> >> >> For Each Nme In wb.Names
>>> >> >>> >> >> If Left(Nme, Len(ws.name) + 1) = "=" & ws.name Then
>>> >> >>> >> >> With ws.Range(Nme.name)
>>> >> >>> >> >> .Copy Destination:=Nwb.Sheets(1).Range(.Address)
>>> >> >>> >> >> End With
>>> >> >>> >> >> End If
>>> >> >>> >> >> Next
>>> >> >>> >> >>
>>> >> >>> >> >> Nwb.SaveAs "C:\" & Format(Now, "dd-mmm-yy h-mm-ss") & ".xls"
>>> >> >>> >> >> Nwb.Close False
>>> >> >>> >> >> End Sub
>>> >> >>> >> >>
>>> >> >>> >> >>
>>> >> >>> >> >> --
>>> >> >>> >> >> Regards Ron de Bruin
>>> >> >>> >> >> http://www.rondebruin.nl
>>> >> >>> >> >>
>>> >> >>> >> >>
>>> >> >>> >> >>
>>> >> >>> >> >> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>> >> >>> >> >> > OK, I look at it after dinner
>>> >> >>> >> >> >
>>> >> >>> >> >> >
>>> >> >>> >> >> > --
>>> >> >>> >> >> > Regards Ron de Bruin
>>> >> >>> >> >> > http://www.rondebruin.nl
>>> >> >>> >> >> >
>>> >> >>> >> >> >
>>> >> >>> >> >> >
>>> >> >>> >> >> > "steve" <[email protected]> wrote in message
>>> >> >>> >> >> > news:[email protected]...
>>> >> >>> >> >> >> there about 30 user input cells that I have named. (these are individual cells)
>>> >> >>> >> >> >>
>>> >> >>> >> >> >> then there are 3 "matrices" of data. they are 10 cells X 10 cells each.
>>> >> >>> >> >> >> for example, I have named cells A1:J10 "EpoxyMatrix". this whole area is
>>> >> >>> >> >> >> yellow for userinput.
>>> >> >>> >> >> >>
>>> >> >>> >> >> >> so in all, there are about 35 names that will be copied. down the road,
>>> >> >>> >> >> >> this may increase or decrease.
>>> >> >>> >> >> >>
>>> >> >>> >> >> >> Thanks,
>>> >> >>> >> >> >> Steve
>>> >> >>> >> >> >>
>>> >> >>> >> >> >> "Ron de Bruin" wrote:
>>> >> >>> >> >> >>
>>> >> >>> >> >> >>> How many cells are there in all range names together
>>> >> >>> >> >> >>>
>>> >> >>> >> >> >>> --
>>> >> >>> >> >> >>> Regards Ron de Bruin
>>> >> >>> >> >> >>> http://www.rondebruin.nl
>>> >> >>> >> >> >>>
>>> >> >>> >> >> >>>
>>> >> >>> >> >> >>>
>>> >> >>> >> >> >>> "steve" <[email protected]> wrote in message
>>> >> >>> >> >> >>> news:[email protected]...
>>> >> >>> >> >> >>> > I'm sorry for singling you out, but you've always given me great solutions.
>>> >> >>> >> >> >>> > This piece of code will be very crucial in my application.
>>> >> >>> >> >> >>> >
>>> >> >>> >> >> >>> > On a spreadsheet, I have colored yellow every range that i consider to be
>>> >> >>> >> >> >>> > User Input. I have also re-named each of these ranges.
>>> >> >>> >> >> >>> >
>>> >> >>> >> >> >>> > I want a button to create a new workbook. I want to copy the names of the
>>> >> >>> >> >> >>> > ranges that are user input. I also want to copy their values. This is so
>>> >> >>> >> >> >>> > later, I can call on this file and re-use this user input.
>>> >> >>> >> >> >>> >
>>> >> >>> >> >> >>> > One of the big issues I am encountering is that some of the Named Ranges are
>>> >> >>> >> >> >>> > larger than just a single cell. I have 3 particular ranges that are 10 cells
>>> >> >>> >> >> >>> > X 10 cells.
>>> >> >>> >> >> >>> >
>>> >> >>> >> >> >>> > Later, if the user wants to call on one of these files containing the user
>>> >> >>> >> >> >>> > input, I want to cycle through the names of the ranges that we saved, and
>>> >> >>> >> >> >>> > copy them back to the yellow cells of the same name. If for some reason,
>>> >> >>> >> >> >>> > this yellow cell has changed names or been deleted, I want to just skip that
>>> >> >>> >> >> >>> > and move to the next.
>>> >> >>> >> >> >>> >
>>> >> >>> >> >> >>> > Thanks for any help!
>>> >> >>> >> >> >>> > Steve
>>> >> >>> >> >> >>>
>>> >> >>> >> >> >>>
>>> >> >>> >> >> >>>
>>> >> >>> >> >> >
>>> >> >>> >> >> >
>>> >> >>> >> >>
>>> >> >>> >> >>
>>> >> >>> >> >>
>>> >> >>> >>
>>> >> >>> >>
>>> >> >>> >>
>>> >> >>>
>>> >> >>>
>>> >> >>>
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >>
>
>