Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a row to new worksheet
I've copied some information - actually filtered data - from a source
worksheet (src or "ACTIVE"). Below I write that to a newly created destination worksheet (dst). Afterwards I need to copy a row with header information from src to dst. However when I to this, Row 2 in dst disappears. (dst appear with row 1 and then row 3.) What am I doing wrong? Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a row to new worksheet
try changing
Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) to Sheets("ACTIVE").Range("a2").EntireRow.Copy _ Destination:=dst_sheet.rows(2) -- Regards, Tom Ogilvy "David Gerstman" wrote: I've copied some information - actually filtered data - from a source worksheet (src or "ACTIVE"). Below I write that to a newly created destination worksheet (dst). Afterwards I need to copy a row with header information from src to dst. However when I to this, Row 2 in dst disappears. (dst appear with row 1 and then row 3.) What am I doing wrong? Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a row to new worksheet
Thank you but the same thing happens.
David "Tom Ogilvy" wrote: try changing Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) to Sheets("ACTIVE").Range("a2").EntireRow.Copy _ Destination:=dst_sheet.rows(2) -- Regards, Tom Ogilvy "David Gerstman" wrote: I've copied some information - actually filtered data - from a source worksheet (src or "ACTIVE"). Below I write that to a newly created destination worksheet (dst). Afterwards I need to copy a row with header information from src to dst. However when I to this, Row 2 in dst disappears. (dst appear with row 1 and then row 3.) What am I doing wrong? Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a row to new worksheet
On the destination sheet,
You select A2 and insert a row. that leaves the previously copied data in row 1 still in row1 and the other data that was in rows 2 and down is now in rows 3 and down. the code then copies whatever is in row 2 of a sheet named "Active" to row2 of the destination sheet. In that context, I guess you would have to explain what you mean by disappears. -- Regards, Tom Ogilvy "David Gerstman" wrote: Thank you but the same thing happens. David "Tom Ogilvy" wrote: try changing Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) to Sheets("ACTIVE").Range("a2").EntireRow.Copy _ Destination:=dst_sheet.rows(2) -- Regards, Tom Ogilvy "David Gerstman" wrote: I've copied some information - actually filtered data - from a source worksheet (src or "ACTIVE"). Below I write that to a newly created destination worksheet (dst). Afterwards I need to copy a row with header information from src to dst. However when I to this, Row 2 in dst disappears. (dst appear with row 1 and then row 3.) What am I doing wrong? Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a row to new worksheet
Row 2, the empty row, appears to have been deleted. Normally the rows beneath
would have been renamed 2..., but that didn't happen here. There's Row1 with the info copied there. And rows 3+ with the info copied there. And the empty row 2 is nowhere to be found. David "Tom Ogilvy" wrote: On the destination sheet, You select A2 and insert a row. that leaves the previously copied data in row 1 still in row1 and the other data that was in rows 2 and down is now in rows 3 and down. the code then copies whatever is in row 2 of a sheet named "Active" to row2 of the destination sheet. In that context, I guess you would have to explain what you mean by disappears. -- Regards, Tom Ogilvy "David Gerstman" wrote: Thank you but the same thing happens. David "Tom Ogilvy" wrote: try changing Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) to Sheets("ACTIVE").Range("a2").EntireRow.Copy _ Destination:=dst_sheet.rows(2) -- Regards, Tom Ogilvy "David Gerstman" wrote: I've copied some information - actually filtered data - from a source worksheet (src or "ACTIVE"). Below I write that to a newly created destination worksheet (dst). Afterwards I need to copy a row with header information from src to dst. However when I to this, Row 2 in dst disappears. (dst appear with row 1 and then row 3.) What am I doing wrong? Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a row to new worksheet
I ran your code and it worked exactly as I expected it to:
Sub eee() Worksheets("Active").Range("A1").CurrentRegion.Cop y Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) End Sub So I can't say what might be causing you to get results you don't expect. -- Regards, Tom Ogilvy "David Gerstman" wrote: Row 2, the empty row, appears to have been deleted. Normally the rows beneath would have been renamed 2..., but that didn't happen here. There's Row1 with the info copied there. And rows 3+ with the info copied there. And the empty row 2 is nowhere to be found. David "Tom Ogilvy" wrote: On the destination sheet, You select A2 and insert a row. that leaves the previously copied data in row 1 still in row1 and the other data that was in rows 2 and down is now in rows 3 and down. the code then copies whatever is in row 2 of a sheet named "Active" to row2 of the destination sheet. In that context, I guess you would have to explain what you mean by disappears. -- Regards, Tom Ogilvy "David Gerstman" wrote: Thank you but the same thing happens. David "Tom Ogilvy" wrote: try changing Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) to Sheets("ACTIVE").Range("a2").EntireRow.Copy _ Destination:=dst_sheet.rows(2) -- Regards, Tom Ogilvy "David Gerstman" wrote: I've copied some information - actually filtered data - from a source worksheet (src or "ACTIVE"). Below I write that to a newly created destination worksheet (dst). Afterwards I need to copy a row with header information from src to dst. However when I to this, Row 2 in dst disappears. (dst appear with row 1 and then row 3.) What am I doing wrong? Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a row to new worksheet
You've helped me narrow down my problem
I have a column of contacts and do an autofilter on the results to determine who gets the information. What I have to do is turn off the autofilter before selecting the lines from the source that need to be copied to dst. David "Tom Ogilvy" wrote: I ran your code and it worked exactly as I expected it to: Sub eee() Worksheets("Active").Range("A1").CurrentRegion.Cop y Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) End Sub So I can't say what might be causing you to get results you don't expect. -- Regards, Tom Ogilvy "David Gerstman" wrote: Row 2, the empty row, appears to have been deleted. Normally the rows beneath would have been renamed 2..., but that didn't happen here. There's Row1 with the info copied there. And rows 3+ with the info copied there. And the empty row 2 is nowhere to be found. David "Tom Ogilvy" wrote: On the destination sheet, You select A2 and insert a row. that leaves the previously copied data in row 1 still in row1 and the other data that was in rows 2 and down is now in rows 3 and down. the code then copies whatever is in row 2 of a sheet named "Active" to row2 of the destination sheet. In that context, I guess you would have to explain what you mean by disappears. -- Regards, Tom Ogilvy "David Gerstman" wrote: Thank you but the same thing happens. David "Tom Ogilvy" wrote: try changing Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) to Sheets("ACTIVE").Range("a2").EntireRow.Copy _ Destination:=dst_sheet.rows(2) -- Regards, Tom Ogilvy "David Gerstman" wrote: I've copied some information - actually filtered data - from a source worksheet (src or "ACTIVE"). Below I write that to a newly created destination worksheet (dst). Afterwards I need to copy a row with header information from src to dst. However when I to this, Row 2 in dst disappears. (dst appear with row 1 and then row 3.) What am I doing wrong? Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copying a row to new worksheet
Again, thank you for this response.
You confirmed that I pasted correctly. That meant that I copied wrong. I've now been able to isolate what was wrong. (Something that's been bothering for weeks!) I've fixed the copying problem (by canceling autofilter) and the macro now works correctly. David "Tom Ogilvy" wrote: I ran your code and it worked exactly as I expected it to: Sub eee() Worksheets("Active").Range("A1").CurrentRegion.Cop y Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) End Sub So I can't say what might be causing you to get results you don't expect. -- Regards, Tom Ogilvy "David Gerstman" wrote: Row 2, the empty row, appears to have been deleted. Normally the rows beneath would have been renamed 2..., but that didn't happen here. There's Row1 with the info copied there. And rows 3+ with the info copied there. And the empty row 2 is nowhere to be found. David "Tom Ogilvy" wrote: On the destination sheet, You select A2 and insert a row. that leaves the previously copied data in row 1 still in row1 and the other data that was in rows 2 and down is now in rows 3 and down. the code then copies whatever is in row 2 of a sheet named "Active" to row2 of the destination sheet. In that context, I guess you would have to explain what you mean by disappears. -- Regards, Tom Ogilvy "David Gerstman" wrote: Thank you but the same thing happens. David "Tom Ogilvy" wrote: try changing Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) to Sheets("ACTIVE").Range("a2").EntireRow.Copy _ Destination:=dst_sheet.rows(2) -- Regards, Tom Ogilvy "David Gerstman" wrote: I've copied some information - actually filtered data - from a source worksheet (src or "ACTIVE"). Below I write that to a newly created destination worksheet (dst). Afterwards I need to copy a row with header information from src to dst. However when I to this, Row 2 in dst disappears. (dst appear with row 1 and then row 3.) What am I doing wrong? Set dst_sheet = Worksheets.Add ActiveSheet.Paste Selection.Columns.AutoFit dst_sheet.Activate Range("a2").Select Selection.EntireRow.Insert Sheets("ACTIVE").Range("a2").EntireRow.Copy dst_sheet.Paste Destination:=dst_sheet.Cells(2, 1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet formula incorrect after copying to another worksheet | Excel Worksheet Functions | |||
Copying the active worksheet to another worksheet in the same work | Excel Worksheet Functions | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
Copying a worksheet witrh protected cells to a new worksheet | Excel Worksheet Functions | |||
Copying Worksheet triggers Click event of combobox on another worksheet | Excel Programming |