Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range name copy
I am still confused. The range names that I have are pg1 all the way to pg10
right now. I may have more later. These range names are on each sheet of one workbook. I want to go to the range name that I created and copy the range to another worksheet which I have call data that is located in the same workbook. When I copy to the data sheet I want to past special value and transpose. After that is done I want to go get pg2 and put it into data at active file. Please help. -- Mr Speedy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range name copy
See inline comments...
I am still confused. That maybe because your first posting didn't ask the actual question you wanted an answer to. The range names that I have are pg1 all the way to pg10 right now. I may have more later. These range names are on each sheet of one workbook. I want to go to the range name that I created and copy the range to another worksheet which I have call data that is located in the same workbook. When I copy to the data sheet I want to past special value and transpose. After that is done I want to go get pg2 and put it into data at active file. Please help. Give us a hint as to how your data is laid out. What kind of ranges do pg1, pg2, etc. refer to? Where on the "data" sheet to you want to place the transposed data? You say in your next to last sentence "active file"... did you mean "active workbook"? The more data you give us, the better able we are to understand what you are trying to do. -- Rick (MVP - Excel) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range name copy
Okay,
My workbook contain several sheets right now. The workbook will increase in sheets as time goes on. The first sheet has numbers on it that I need. I created a range name to capture the numbers that I need on the first sheet. The range of the range name is c2 through h51. I named this range pg1. I have the same range on the next worksheet which is right next to the other worksheet. I am using the same RANGE but I am calling this pg2. My steps are as follow: Go to or F5. highlight range name. copy. paste special. transpose. Then I will paste this to a sheet that I have already created that is the last worksheet in this workbook. I need to put the next range that I paste to this last sheet let us call it data below the items that I just pasted from pg1. So I will have a pg1 pasted then pg2 pasted and so on and so on until all of my ranges are pasted. I hope that makes it clear. -- Mr Speedy "Rick Rothstein" wrote: See inline comments... I am still confused. That maybe because your first posting didn't ask the actual question you wanted an answer to. The range names that I have are pg1 all the way to pg10 right now. I may have more later. These range names are on each sheet of one workbook. I want to go to the range name that I created and copy the range to another worksheet which I have call data that is located in the same workbook. When I copy to the data sheet I want to past special value and transpose. After that is done I want to go get pg2 and put it into data at active file. Please help. Give us a hint as to how your data is laid out. What kind of ranges do pg1, pg2, etc. refer to? Where on the "data" sheet to you want to place the transposed data? You say in your next to last sentence "active file"... did you mean "active workbook"? The more data you give us, the better able we are to understand what you are trying to do. -- Rick (MVP - Excel) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range name copy
Yes, that helps clarify what want for me. Give this macro a try...
Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub Use the two Const statements to set the row and column you want to start placing your transposed data at. -- Rick (MVP - Excel) "speedy" wrote in message ... Okay, My workbook contain several sheets right now. The workbook will increase in sheets as time goes on. The first sheet has numbers on it that I need. I created a range name to capture the numbers that I need on the first sheet. The range of the range name is c2 through h51. I named this range pg1. I have the same range on the next worksheet which is right next to the other worksheet. I am using the same RANGE but I am calling this pg2. My steps are as follow: Go to or F5. highlight range name. copy. paste special. transpose. Then I will paste this to a sheet that I have already created that is the last worksheet in this workbook. I need to put the next range that I paste to this last sheet let us call it data below the items that I just pasted from pg1. So I will have a pg1 pasted then pg2 pasted and so on and so on until all of my ranges are pasted. I hope that makes it clear. -- Mr Speedy "Rick Rothstein" wrote: See inline comments... I am still confused. That maybe because your first posting didn't ask the actual question you wanted an answer to. The range names that I have are pg1 all the way to pg10 right now. I may have more later. These range names are on each sheet of one workbook. I want to go to the range name that I created and copy the range to another worksheet which I have call data that is located in the same workbook. When I copy to the data sheet I want to past special value and transpose. After that is done I want to go get pg2 and put it into data at active file. Please help. Give us a hint as to how your data is laid out. What kind of ranges do pg1, pg2, etc. refer to? Where on the "data" sheet to you want to place the transposed data? You say in your next to last sentence "active file"... did you mean "active workbook"? The more data you give us, the better able we are to understand what you are trying to do. -- Rick (MVP - Excel) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range name copy
A minor modification just in case you have other named ranges besides the
pg# ones... Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names If N.Name Like "[Pp][Gg]*" Then N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count End If Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Yes, that helps clarify what want for me. Give this macro a try... Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub Use the two Const statements to set the row and column you want to start placing your transposed data at. -- Rick (MVP - Excel) "speedy" wrote in message ... Okay, My workbook contain several sheets right now. The workbook will increase in sheets as time goes on. The first sheet has numbers on it that I need. I created a range name to capture the numbers that I need on the first sheet. The range of the range name is c2 through h51. I named this range pg1. I have the same range on the next worksheet which is right next to the other worksheet. I am using the same RANGE but I am calling this pg2. My steps are as follow: Go to or F5. highlight range name. copy. paste special. transpose. Then I will paste this to a sheet that I have already created that is the last worksheet in this workbook. I need to put the next range that I paste to this last sheet let us call it data below the items that I just pasted from pg1. So I will have a pg1 pasted then pg2 pasted and so on and so on until all of my ranges are pasted. I hope that makes it clear. -- Mr Speedy "Rick Rothstein" wrote: See inline comments... I am still confused. That maybe because your first posting didn't ask the actual question you wanted an answer to. The range names that I have are pg1 all the way to pg10 right now. I may have more later. These range names are on each sheet of one workbook. I want to go to the range name that I created and copy the range to another worksheet which I have call data that is located in the same workbook. When I copy to the data sheet I want to past special value and transpose. After that is done I want to go get pg2 and put it into data at active file. Please help. Give us a hint as to how your data is laid out. What kind of ranges do pg1, pg2, etc. refer to? Where on the "data" sheet to you want to place the transposed data? You say in your next to last sentence "active file"... did you mean "active workbook"? The more data you give us, the better able we are to understand what you are trying to do. -- Rick (MVP - Excel) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range name copy
The macro starts to work but it is bringing up a box with all of my files and
I must answer cancel also the macro stops at N. Refers ToRange.copy -- Mr Speedy "Rick Rothstein" wrote: A minor modification just in case you have other named ranges besides the pg# ones... Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names If N.Name Like "[Pp][Gg]*" Then N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count End If Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Yes, that helps clarify what want for me. Give this macro a try... Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub Use the two Const statements to set the row and column you want to start placing your transposed data at. -- Rick (MVP - Excel) "speedy" wrote in message ... Okay, My workbook contain several sheets right now. The workbook will increase in sheets as time goes on. The first sheet has numbers on it that I need. I created a range name to capture the numbers that I need on the first sheet. The range of the range name is c2 through h51. I named this range pg1. I have the same range on the next worksheet which is right next to the other worksheet. I am using the same RANGE but I am calling this pg2. My steps are as follow: Go to or F5. highlight range name. copy. paste special. transpose. Then I will paste this to a sheet that I have already created that is the last worksheet in this workbook. I need to put the next range that I paste to this last sheet let us call it data below the items that I just pasted from pg1. So I will have a pg1 pasted then pg2 pasted and so on and so on until all of my ranges are pasted. I hope that makes it clear. -- Mr Speedy "Rick Rothstein" wrote: See inline comments... I am still confused. That maybe because your first posting didn't ask the actual question you wanted an answer to. The range names that I have are pg1 all the way to pg10 right now. I may have more later. These range names are on each sheet of one workbook. I want to go to the range name that I created and copy the range to another worksheet which I have call data that is located in the same workbook. When I copy to the data sheet I want to past special value and transpose. After that is done I want to go get pg2 and put it into data at active file. Please help. Give us a hint as to how your data is laid out. What kind of ranges do pg1, pg2, etc. refer to? Where on the "data" sheet to you want to place the transposed data? You say in your next to last sentence "active file"... did you mean "active workbook"? The more data you give us, the better able we are to understand what you are trying to do. -- Rick (MVP - Excel) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range name copy
Where did you place the macro at? What version of Excel are you using? Did
you change any of the code in the macro (if so, please post your modified code)? I ask because I tested the code before I posted it and it worked fine on my copy of Excel 2003. -- Rick (MVP - Excel) "speedy" wrote in message ... The macro starts to work but it is bringing up a box with all of my files and I must answer cancel also the macro stops at N. Refers ToRange.copy -- Mr Speedy "Rick Rothstein" wrote: A minor modification just in case you have other named ranges besides the pg# ones... Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names If N.Name Like "[Pp][Gg]*" Then N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count End If Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Yes, that helps clarify what want for me. Give this macro a try... Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub Use the two Const statements to set the row and column you want to start placing your transposed data at. -- Rick (MVP - Excel) "speedy" wrote in message ... Okay, My workbook contain several sheets right now. The workbook will increase in sheets as time goes on. The first sheet has numbers on it that I need. I created a range name to capture the numbers that I need on the first sheet. The range of the range name is c2 through h51. I named this range pg1. I have the same range on the next worksheet which is right next to the other worksheet. I am using the same RANGE but I am calling this pg2. My steps are as follow: Go to or F5. highlight range name. copy. paste special. transpose. Then I will paste this to a sheet that I have already created that is the last worksheet in this workbook. I need to put the next range that I paste to this last sheet let us call it data below the items that I just pasted from pg1. So I will have a pg1 pasted then pg2 pasted and so on and so on until all of my ranges are pasted. I hope that makes it clear. -- Mr Speedy "Rick Rothstein" wrote: See inline comments... I am still confused. That maybe because your first posting didn't ask the actual question you wanted an answer to. The range names that I have are pg1 all the way to pg10 right now. I may have more later. These range names are on each sheet of one workbook. I want to go to the range name that I created and copy the range to another worksheet which I have call data that is located in the same workbook. When I copy to the data sheet I want to past special value and transpose. After that is done I want to go get pg2 and put it into data at active file. Please help. Give us a hint as to how your data is laid out. What kind of ranges do pg1, pg2, etc. refer to? Where on the "data" sheet to you want to place the transposed data? You say in your next to last sentence "active file"... did you mean "active workbook"? The more data you give us, the better able we are to understand what you are trying to do. -- Rick (MVP - Excel) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range name copy
Here is the macro that I tried to use. It started to work but it brings up
my file box and I have to say cancel then it stops in the middle and needs to be debugged. I have this saved in the workbook that I want to use it in. Where should I save it and what is wrong it. Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 1 Const DataStartColumn As String = "a" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names If N.Name Like "[Pp][Gg]*" Then N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count End If Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub -- Mr Speedy "Rick Rothstein" wrote: Where did you place the macro at? What version of Excel are you using? Did you change any of the code in the macro (if so, please post your modified code)? I ask because I tested the code before I posted it and it worked fine on my copy of Excel 2003. -- Rick (MVP - Excel) "speedy" wrote in message ... The macro starts to work but it is bringing up a box with all of my files and I must answer cancel also the macro stops at N. Refers ToRange.copy -- Mr Speedy "Rick Rothstein" wrote: A minor modification just in case you have other named ranges besides the pg# ones... Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names If N.Name Like "[Pp][Gg]*" Then N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count End If Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Yes, that helps clarify what want for me. Give this macro a try... Sub GetTransposeData() Dim N As Name Dim LastRow As Long Const DataStartRow As Long = 3 Const DataStartColumn As String = "c" With Worksheets("Data") LastRow = DataStartRow .Range(.Cells(DataStartRow, DataStartColumn), _ .Cells(.Rows.Count, .Columns.Count)).Clear For Each N In ActiveWorkbook.Names N.RefersToRange.Copy .Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True LastRow = LastRow + N.RefersToRange.Columns.Count Next Application.CutCopyMode = False Application.Goto .Cells(LastRow, DataStartColumn) End With End Sub Use the two Const statements to set the row and column you want to start placing your transposed data at. -- Rick (MVP - Excel) "speedy" wrote in message ... Okay, My workbook contain several sheets right now. The workbook will increase in sheets as time goes on. The first sheet has numbers on it that I need. I created a range name to capture the numbers that I need on the first sheet. The range of the range name is c2 through h51. I named this range pg1. I have the same range on the next worksheet which is right next to the other worksheet. I am using the same RANGE but I am calling this pg2. My steps are as follow: Go to or F5. highlight range name. copy. paste special. transpose. Then I will paste this to a sheet that I have already created that is the last worksheet in this workbook. I need to put the next range that I paste to this last sheet let us call it data below the items that I just pasted from pg1. So I will have a pg1 pasted then pg2 pasted and so on and so on until all of my ranges are pasted. I hope that makes it clear. -- Mr Speedy "Rick Rothstein" wrote: See inline comments... I am still confused. That maybe because your first posting didn't ask the actual question you wanted an answer to. The range names that I have are pg1 all the way to pg10 right now. I may have more later. These range names are on each sheet of one workbook. I want to go to the range name that I created and copy the range to another worksheet which I have call data that is located in the same workbook. When I copy to the data sheet I want to past special value and transpose. After that is done I want to go get pg2 and put it into data at active file. Please help. Give us a hint as to how your data is laid out. What kind of ranges do pg1, pg2, etc. refer to? Where on the "data" sheet to you want to place the transposed data? You say in your next to last sentence "active file"... did you mean "active workbook"? The more data you give us, the better able we are to understand what you are trying to do. -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
Create/copy combo boxes in one range if condition is met in a different range | Excel Programming | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming | |||
Range COPY function - how to copy VALUES and not formulas | Excel Programming |