|
Ron de Bruin
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" wrote in message ... 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
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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
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 |
Ron de Bruin
Thanks Casey
40 today, I am an old men now <g -- Regards Ron de Bruin http://www.rondebruin.nl "Casey" wrote in message ... 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 |
Ron de Bruin
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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
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" wrote in message ... 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" wrote in message ... 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 |
Ron de Bruin
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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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
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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... I post a example this evening Steve -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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 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" wrote in message ... I post a example this evening Steve -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... I post a example this evening Steve -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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.
"Ron de Bruin" wrote: Can you send me your workbook private Steve -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... I post a example this evening Steve -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... I post a example this evening Steve -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... I post a example this evening Steve -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... OK, I look at it after dinner -- Regards Ron de Bruin http://www.rondebruin.nl "steve" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com