| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Range to a new workbook
The macro below is designed to copy data from a master tab and paste
to one of three resultant tabs. Unfortunately, I have made a some error with my paste command. It pastes ONLY cell (1,AC) instead of pasting cells 1 THROUGH 29 in the active row. Any advice would be great! Thanks to Steve (Scoops) for the previous assistance! Sub UPDATE_STATUS() ' ' UPDATE_STATUS Macro ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select For Each cell In Range("F:F") Select Case cell Case "P" Cells(1, 29).Copy Sheets("Personal").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "C" Cells(1, 29).Copy Sheets("Corporate").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "D" Cells(1, 29).Copy Sheets("Disconnect").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select End Select Next ActiveWorkbook.Save |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste Range to a new workbook
On 4 May, 15:55, Frank wrote:
The macro below is designed to copy data from a master tab and paste to one of three resultant tabs. Unfortunately, I have made a some error with my paste command. It pastes ONLY cell (1,AC) instead of pasting cells 1 THROUGH 29 in the active row. Any advice would be great! Thanks to Steve (Scoops) for the previous assistance! Sub UPDATE_STATUS() ' ' UPDATE_STATUS Macro ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select For Each cell In Range("F:F") Select Case cell Case "P" Cells(1, 29).Copy Sheets("Personal").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "C" Cells(1, 29).Copy Sheets("Corporate").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "D" Cells(1, 29).Copy Sheets("Disconnect").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select End Select Next ActiveWorkbook.Save Hi Frank I posted this to your last thread but you've moved on! Sub TestRange() Dim cell As Range For Each cell In Range("F1", Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F")) Select Case cell Case "F" cell.Resize(1, 29).Copy _ Sheets("Personal").Cells(Rows.Count, 1).End(xlUp)(2) Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub Regards Steve |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste Range to a new workbook
On May 4, 10:57 am, Scoops wrote:
On 4 May, 15:55, Frank wrote: The macro below is designed to copy data from a master tab and paste to one of three resultant tabs. Unfortunately, I have made a some error with my paste command. It pastes ONLY cell (1,AC) instead of pasting cells 1 THROUGH 29 in the active row. Any advice would be great! Thanks to Steve (Scoops) for the previous assistance! Sub UPDATE_STATUS() ' ' UPDATE_STATUS Macro ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select For Each cell In Range("F:F") Select Case cell Case "P" Cells(1, 29).Copy Sheets("Personal").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "C" Cells(1, 29).Copy Sheets("Corporate").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "D" Cells(1, 29).Copy Sheets("Disconnect").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select End Select Next ActiveWorkbook.Save Hi Frank I posted this to your last thread but you've moved on! Sub TestRange() Dim cell As Range For Each cell In Range("F1", Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F")) Select Case cell Case "F" cell.Resize(1, 29).Copy _ Sheets("Personal").Cells(Rows.Count, 1).End(xlUp)(2) Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub Regards Steve- Hide quoted text - - Show quoted text - Steve: THANKS!! Again, I see TONS of improvement to my code. One statement instead of 6! (and it works better too) Almost there. The new paste statement selects the NEXT 29 cells (since we started at column 6 or F) instead of selecting cells from column A (through the end of row). Is there a simpler command that will select the entire row and paste it to the appropriate workbook? It's about 99% done. Sorry for the volume of questions. I'm still learning my way Frank |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste Range to a new workbook
Sub TestRange()
Dim cell As Range For Each cell In Range("F1", _ Cells(Rows.Count,"F").End(xlUp)) Select Case cell Case "F" Cells(cell.row).Resize(1, 29).Copy _ Sheets("Personal").Cells(Rows.Count, 1).End(xlUp)(2) Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub or cell.Entirerow.copy _ Sheets("Personal").Cells(Rows.Count, 1).End(xlUp)(2) -- Regards, Tom Ogilvy "Frank" wrote: On May 4, 10:57 am, Scoops wrote: On 4 May, 15:55, Frank wrote: The macro below is designed to copy data from a master tab and paste to one of three resultant tabs. Unfortunately, I have made a some error with my paste command. It pastes ONLY cell (1,AC) instead of pasting cells 1 THROUGH 29 in the active row. Any advice would be great! Thanks to Steve (Scoops) for the previous assistance! Sub UPDATE_STATUS() ' ' UPDATE_STATUS Macro ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select For Each cell In Range("F:F") Select Case cell Case "P" Cells(1, 29).Copy Sheets("Personal").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "C" Cells(1, 29).Copy Sheets("Corporate").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "D" Cells(1, 29).Copy Sheets("Disconnect").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select End Select Next ActiveWorkbook.Save Hi Frank I posted this to your last thread but you've moved on! Sub TestRange() Dim cell As Range For Each cell In Range("F1", Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F")) Select Case cell Case "F" cell.Resize(1, 29).Copy _ Sheets("Personal").Cells(Rows.Count, 1).End(xlUp)(2) Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub Regards Steve- Hide quoted text - - Show quoted text - Steve: THANKS!! Again, I see TONS of improvement to my code. One statement instead of 6! (and it works better too) Almost there. The new paste statement selects the NEXT 29 cells (since we started at column 6 or F) instead of selecting cells from column A (through the end of row). Is there a simpler command that will select the entire row and paste it to the appropriate workbook? It's about 99% done. Sorry for the volume of questions. I'm still learning my way Frank |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste Range to a new workbook
On May 4, 4:21 pm, Frank wrote:
On May 4, 10:57 am, Scoops wrote: On 4 May, 15:55, Frank wrote: The macro below is designed to copy data from a master tab and paste to one of three resultant tabs. Unfortunately, I have made a some error with my paste command. It pastes ONLY cell (1,AC) instead of pasting cells 1 THROUGH 29 in the active row. Any advice would be great! Thanks to Steve (Scoops) for the previous assistance! Sub UPDATE_STATUS() ' ' UPDATE_STATUS Macro ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select For Each cell In Range("F:F") Select Case cell Case "P" Cells(1, 29).Copy Sheets("Personal").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "C" Cells(1, 29).Copy Sheets("Corporate").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select Case "D" Cells(1, 29).Copy Sheets("Disconnect").Select Cells(Rows.Count, 1).End(xlUp)(2).Select ActiveSheet.Paste Sheets("Master").Select End Select Next ActiveWorkbook.Save Hi Frank I posted this to your last thread but you've moved on! Sub TestRange() Dim cell As Range For Each cell In Range("F1", Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F")) Select Case cell Case "F" cell.Resize(1, 29).Copy _ Sheets("Personal").Cells(Rows.Count, 1).End(xlUp)(2) Case "P" 'DoThat Case "C" 'DoTheOther End Select Next End Sub Regards Steve- Hide quoted text - - Show quoted text - Steve: THANKS!! Again, I see TONS of improvement to my code. One statement instead of 6! (and it works better too) Almost there. The new paste statement selects the NEXT 29 cells (since we started at column 6 or F) instead of selecting cells from column A (through the end of row). Is there a simpler command that will select the entire row and paste it to the appropriate workbook? It's about 99% done. Sorry for the volume of questions. I'm still learning my way Frank- Hide quoted text - - Show quoted text - Hi Frank Just got back online after a day's work. Glad to have been of help but I see you've been sorted out by one of the greats. Regards Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste Filtered Range to New Workbook- AS | Excel Discussion (Misc queries) | |||
| Copy paste a range from one Workbook to another | Excel Programming | |||
| Paste Range to New Workbook | Excel Programming | |||
| Need a macro to copy a range in one workbook and paste into another workbook | Excel Programming | |||
| Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming | |||
![Frank[_27_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
