Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Cell Address (String or Range)
I have 3 sheets. The firs is my main sheet, the 2nd and
third are temp sheets whihc I have created. I am copying data from sheet 1 to the temp sheets. I want to copy to temp1 by offseting by 1 volumn then to temp 2 by offseting by 1 row. Currently having trouble setting up the copy an paste range to do this. I am tryint pass the cell address from sheet to a varible. I then want to used this address with an offset to set two other variables. I can pass the address to the first variable by making the first variable a string. However, this does not all be to pass offset version of this address to the 2nd and 3rd variables. I am assuming that this is because they are dimensioned as ranges. Should I be defineing them all as ranges and use the Set stmt? Dim CopyStartCell As String Dim TempSh2PasteCell As Range Dim TempSh3PasteCell As Range For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartCell = sh.UsedRange.Cells(1, 1).Address 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets(TempSh2Name).Range (CopyStartCell).Offset(0, 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets(TempSh3Name).Range (CopyStartCell).Offset(1, 0) Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Cell Address (String or Range)
One way
For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets(TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets(TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol) Next -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... I have 3 sheets. The firs is my main sheet, the 2nd and third are temp sheets whihc I have created. I am copying data from sheet 1 to the temp sheets. I want to copy to temp1 by offseting by 1 volumn then to temp 2 by offseting by 1 row. Currently having trouble setting up the copy an paste range to do this. I am tryint pass the cell address from sheet to a varible. I then want to used this address with an offset to set two other variables. I can pass the address to the first variable by making the first variable a string. However, this does not all be to pass offset version of this address to the 2nd and 3rd variables. I am assuming that this is because they are dimensioned as ranges. Should I be defineing them all as ranges and use the Set stmt? Dim CopyStartCell As String Dim TempSh2PasteCell As Range Dim TempSh3PasteCell As Range For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartCell = sh.UsedRange.Cells(1, 1).Address 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets(TempSh2Name).Range (CopyStartCell).Offset(0, 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets(TempSh3Name).Range (CopyStartCell).Offset(1, 0) Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Cell Address (String or Range)
So I am now trying to copy and paste the original sheets
UsedRange to the defined past cells. But its not working. Nothing is pasted. Why is that? For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartCellRow = sh.UsedRange.Cells(1, 1).Row CopyStartCellCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets(TempSh2Name).Cells (CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets(TempSh3Name).Cells (CopyStartRow + 1, CopyStartCol) 'Copy original range to new range off by 1 column sh.Range(UsedRange).Copy Destination:= _ Worksheets(sh2).Range(TempSh2PasteCell) 'Copy original range to new range off by 1 row sh.Range(UsedRange).Copy Destination:= _ Worksheets(sh3).Range(TempSh3PasteCell) Next -----Original Message----- One way For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets(TempSh2Name).Cells (CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets(TempSh3Name).Cells (CopyStartRow + 1, CopyStartCol) Next -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... I have 3 sheets. The firs is my main sheet, the 2nd and third are temp sheets whihc I have created. I am copying data from sheet 1 to the temp sheets. I want to copy to temp1 by offseting by 1 volumn then to temp 2 by offseting by 1 row. Currently having trouble setting up the copy an paste range to do this. I am tryint pass the cell address from sheet to a varible. I then want to used this address with an offset to set two other variables. I can pass the address to the first variable by making the first variable a string. However, this does not all be to pass offset version of this address to the 2nd and 3rd variables. I am assuming that this is because they are dimensioned as ranges. Should I be defineing them all as ranges and use the Set stmt? Dim CopyStartCell As String Dim TempSh2PasteCell As Range Dim TempSh3PasteCell As Range For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartCell = sh.UsedRange.Cells(1, 1).Address 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets (TempSh2Name).Range (CopyStartCell).Offset(0, 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets (TempSh3Name).Range (CopyStartCell).Offset(1, 0) Next . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Cell Address (String or Range)
Sorry I had some variable name inconsitencies. But
cannot seem to paste to tem sheets. Nothing shows up after copy paste For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right Set TempSh2PasteCell = Worksheets (TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below Set TempSh3PasteCell = Worksheets (TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol) 'Copy original range to new range off by 1 column sh.UsedRange.Copy Destination:= _ Worksheets(sh2).Range(TempSh2PasteCell) 'Copy original range to new range off by 1 row sh.UsedRange.Copy Destination:= _ Worksheets(sh3).Range(TempSh3PasteCell) Next -----Original Message----- One way For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets(TempSh2Name).Cells (CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets(TempSh3Name).Cells (CopyStartRow + 1, CopyStartCol) Next -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... I have 3 sheets. The firs is my main sheet, the 2nd and third are temp sheets whihc I have created. I am copying data from sheet 1 to the temp sheets. I want to copy to temp1 by offseting by 1 volumn then to temp 2 by offseting by 1 row. Currently having trouble setting up the copy an paste range to do this. I am tryint pass the cell address from sheet to a varible. I then want to used this address with an offset to set two other variables. I can pass the address to the first variable by making the first variable a string. However, this does not all be to pass offset version of this address to the 2nd and 3rd variables. I am assuming that this is because they are dimensioned as ranges. Should I be defineing them all as ranges and use the Set stmt? Dim CopyStartCell As String Dim TempSh2PasteCell As Range Dim TempSh3PasteCell As Range For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartCell = sh.UsedRange.Cells(1, 1).Address 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets (TempSh2Name).Range (CopyStartCell).Offset(0, 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets (TempSh3Name).Range (CopyStartCell).Offset(1, 0) Next . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Cell Address (String or Range)
Isn't that because you code doesn't do any pasting? You are just getting the
value in the other sheets offset with the code as it stands. -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... Sorry I had some variable name inconsitencies. But cannot seem to paste to tem sheets. Nothing shows up after copy paste For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right Set TempSh2PasteCell = Worksheets (TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below Set TempSh3PasteCell = Worksheets (TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol) 'Copy original range to new range off by 1 column sh.UsedRange.Copy Destination:= _ Worksheets(sh2).Range(TempSh2PasteCell) 'Copy original range to new range off by 1 row sh.UsedRange.Copy Destination:= _ Worksheets(sh3).Range(TempSh3PasteCell) Next -----Original Message----- One way For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets(TempSh2Name).Cells (CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets(TempSh3Name).Cells (CopyStartRow + 1, CopyStartCol) Next -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... I have 3 sheets. The firs is my main sheet, the 2nd and third are temp sheets whihc I have created. I am copying data from sheet 1 to the temp sheets. I want to copy to temp1 by offseting by 1 volumn then to temp 2 by offseting by 1 row. Currently having trouble setting up the copy an paste range to do this. I am tryint pass the cell address from sheet to a varible. I then want to used this address with an offset to set two other variables. I can pass the address to the first variable by making the first variable a string. However, this does not all be to pass offset version of this address to the 2nd and 3rd variables. I am assuming that this is because they are dimensioned as ranges. Should I be defineing them all as ranges and use the Set stmt? Dim CopyStartCell As String Dim TempSh2PasteCell As Range Dim TempSh3PasteCell As Range For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartCell = sh.UsedRange.Cells(1, 1).Address 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets (TempSh2Name).Range (CopyStartCell).Offset(0, 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets (TempSh3Name).Range (CopyStartCell).Offset(1, 0) Next . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Cell Address (String or Range)
The bottom part of the code I pasted in the last post had
the copy/paste code. Shouldn't this work 'Copy original range to new range off by 1 column sh.UsedRange.Copy Destination:= _ Worksheets(sh2).Range(TempSh2PasteCell) 'Copy original range to new range off by 1 row sh.UsedRange.Copy Destination:= _ Worksheets(sh3).Range(TempSh3PasteCell) -----Original Message----- Isn't that because you code doesn't do any pasting? You are just getting the value in the other sheets offset with the code as it stands. -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... Sorry I had some variable name inconsitencies. But cannot seem to paste to tem sheets. Nothing shows up after copy paste For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right Set TempSh2PasteCell = Worksheets (TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below Set TempSh3PasteCell = Worksheets 'Copy original range to new range off by 1 column sh.UsedRange.Copy Destination:= _ Worksheets(sh2).Range(TempSh2PasteCell) 'Copy original range to new range off by 1 row sh.UsedRange.Copy Destination:= _ Worksheets(sh3).Range(TempSh3PasteCell) (TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol) Next -----Original Message----- One way For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets (TempSh2Name).Cells (CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets (TempSh3Name).Cells (CopyStartRow + 1, CopyStartCol) Next -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... I have 3 sheets. The firs is my main sheet, the 2nd and third are temp sheets whihc I have created. I am copying data from sheet 1 to the temp sheets. I want to copy to temp1 by offseting by 1 volumn then to temp 2 by offseting by 1 row. Currently having trouble setting up the copy an paste range to do this. I am tryint pass the cell address from sheet to a varible. I then want to used this address with an offset to set two other variables. I can pass the address to the first variable by making the first variable a string. However, this does not all be to pass offset version of this address to the 2nd and 3rd variables. I am assuming that this is because they are dimensioned as ranges. Should I be defineing them all as ranges and use the Set stmt? Dim CopyStartCell As String Dim TempSh2PasteCell As Range Dim TempSh3PasteCell As Range For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartCell = sh.UsedRange.Cells(1, 1).Address 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets (TempSh2Name).Range (CopyStartCell).Offset(0, 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets (TempSh3Name).Range (CopyStartCell).Offset(1, 0) Next . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Cell Address (String or Range)
Sorry, I was still looking at the original code.
I must admit to not fully following the code, but these lines TempSh2PasteCell = Worksheets(TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1) TempSh3PasteCell = Worksheets(TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol) aren't geting a cell as I think you believe, they are getting a cell value. You need to declare them as ranges and set them. Also, the copy can then just use that range, as it includes the sheet. Try this Dim sh, CopyStartRow, CopyStartCol Const TempSh2Name = "Sheet2" Const TempSh3Name = "Sheet3" Dim TempSh2PasteCell, TempSh3PasteCell For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right Set TempSh2PasteCell = Worksheets(TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below Set TempSh3PasteCell = Worksheets(TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol) 'Copy original range to new range off by 1 column sh.UsedRange.Copy Destination:=TempSh2PasteCell 'Copy original range to new range off by 1 row sh.UsedRange.Copy Destination:=TempSh3PasteCell Next -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... The bottom part of the code I pasted in the last post had the copy/paste code. Shouldn't this work 'Copy original range to new range off by 1 column sh.UsedRange.Copy Destination:= _ Worksheets(sh2).Range(TempSh2PasteCell) 'Copy original range to new range off by 1 row sh.UsedRange.Copy Destination:= _ Worksheets(sh3).Range(TempSh3PasteCell) -----Original Message----- Isn't that because you code doesn't do any pasting? You are just getting the value in the other sheets offset with the code as it stands. -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... Sorry I had some variable name inconsitencies. But cannot seem to paste to tem sheets. Nothing shows up after copy paste For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right Set TempSh2PasteCell = Worksheets (TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below Set TempSh3PasteCell = Worksheets 'Copy original range to new range off by 1 column sh.UsedRange.Copy Destination:= _ Worksheets(sh2).Range(TempSh2PasteCell) 'Copy original range to new range off by 1 row sh.UsedRange.Copy Destination:= _ Worksheets(sh3).Range(TempSh3PasteCell) (TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol) Next -----Original Message----- One way For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartRow = sh.UsedRange.Cells(1, 1).Row CopyStartCol = sh.UsedRange.Cells(1, 1).Column 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets (TempSh2Name).Cells (CopyStartRow, CopyStartCol + 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets (TempSh3Name).Cells (CopyStartRow + 1, CopyStartCol) Next -- HTH RP (remove nothere from the email address if mailing direct) "ExcelMonkey" wrote in message ... I have 3 sheets. The firs is my main sheet, the 2nd and third are temp sheets whihc I have created. I am copying data from sheet 1 to the temp sheets. I want to copy to temp1 by offseting by 1 volumn then to temp 2 by offseting by 1 row. Currently having trouble setting up the copy an paste range to do this. I am tryint pass the cell address from sheet to a varible. I then want to used this address with an offset to set two other variables. I can pass the address to the first variable by making the first variable a string. However, this does not all be to pass offset version of this address to the 2nd and 3rd variables. I am assuming that this is because they are dimensioned as ranges. Should I be defineing them all as ranges and use the Set stmt? Dim CopyStartCell As String Dim TempSh2PasteCell As Range Dim TempSh3PasteCell As Range For Each sh In ActiveWorkbook.Worksheets 'Set up past ranges in temp sheets CopyStartCell = sh.UsedRange.Cells(1, 1).Address 'Set Paste cell off 1 column to the right TempSh2PasteCell = Worksheets (TempSh2Name).Range (CopyStartCell).Offset(0, 1) 'Set Paste cell off 1 row below TempSh3PasteCell = Worksheets (TempSh3Name).Range (CopyStartCell).Offset(1, 0) Next . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return cell address of longest text string in a range | Excel Discussion (Misc queries) | |||
Passing a Cell Range | Excel Programming | |||
Passing Cell Address to Offset | Excel Worksheet Functions | |||
Passing a String in Array to Range as String | Excel Programming | |||
Passing cell addressess to variables to be use in range | Excel Programming |