Paste Range to a new workbook

[Frank]

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

 

[Scoops]

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

 

[Frank]

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

 

[Tom Ogilvy]

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)

 

[Scoops]

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