copy&paste dynamic ranges macro help

[pcorpz]

Hi guys, I've gotten away with automating tasks using the macro recorder, but unfortunately writing my own macros is still very confusing to me It it not making sense to me yet... here's my scenario:
on the active spreadsheet, I want the first worksheet/tab to be renamed to "pbitems". Then on the "Displays" worksheet, I want data to be copied from A2 until the last available row/cell and paste it to the pbitems worksheet on the next available cell in column A.

TIA!

 

[HalfAce]

Hi pcorpz, a couple questions, just to clarify.
1. You want to use a macro to change the name of your sheet?
(This is surely easy enough to do, but unless you're starting out with a different workbook each time (like if you're using a template or something) this only needs to be done one time, right?)

2.

Then on the "Displays" worksheet, I want data to be copied from A2 until the last available row/cell and paste it to the pbitems worksheet on the next available cell in column A.

This is easy too but I'm not sure if you want to copy from A2 to "until the last available row/cell" in column A only? (Copying only column A?) or do you mean the last row/cell no matter what column it's in and copying all data from column A to whatever column that is?

 

[pboltonchina]

This seems to work for me, but try it on a copy of data first.

Option Explicit

Sub copy()
Worksheets(1).Name = "pbitems"
Worksheets("Displays").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.copy Destination:=Sheets("pbitems").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End Sub

 

[pcorpz]

Hi pcorpz, a couple questions, just to clarify.
1. You want to use a macro to change the name of your sheet?
(This is surely easy enough to do, but unless you're starting out with a different workbook each time (like if you're using a template or something) this only needs to be done one time, right?)

2. This is easy too but I'm not sure if you want to copy from A2 to "until the last available row/cell" in column A only? (Copying only column A?) or do you mean the last row/cell no matter what column it's in and copying all data from column A to whatever column that is?

Hi HalfAce! Thanks for replying. I am starting out with a different workbook each time, it is a file I download from our intranet daily, so I would have to rename it everytime I open that file (then I save a copy for myself) As for copying, yes I would like to copy from a2 to whatever column has data in it. Sorry for the confusion! Let me know if this clears it up.

 

[pcorpz]

This seems to work for me, but try it on a copy of data first.

Option Explicit

Sub copy()
Worksheets(1).Name = "pbitems"
Worksheets("Displays").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.copy Destination:=Sheets("pbitems").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End Sub

Hi pboltonchina, does this go to the actual workbook? I am looking to have the macro saved on a separate workbook, only because the workbook (with Displays, pbitems tabs) is a different file everyday (same format and everything, but just a different file)

 

[pboltonchina]

Try

Option Explicit

Sub copy()
With ActiveWorkbook
Worksheets(1).Name = "pbitems"
Worksheets("Displays").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.copy Destination:=Sheets("pbitems").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    End With
 End Sub

 

[pcorpz]

Try

Option Explicit

Sub copy()
With ActiveWorkbook
Worksheets(1).Name = "pbitems"
Worksheets("Displays").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.copy Destination:=Sheets("pbitems").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    End With
 End Sub

Awesome! That did it. Thank you!

 

[pboltonchina]

You're very welcome, happy to help.

 

[HalfAce]

Looks like pboltonchina got you going already so this isn't real necessary, but here's a way that should be a nanosecond or two faster because it doesn't use Select.

Sub CopyDemo()

With Sheets("Displays")
  LstRw = .Cells.SpecialCells(xlLastCell).Row
  LstCol = .Cells.SpecialCells(xlLastCell).Column
  Range(.Cells(2, 1), .Cells(LstRw, LstCol)).copy _
    Sheets(1).Cells(Rows.Count, "A").End(xlUp)(2)
End With

With Sheets(1)
  .Name = "pbitems"
  .Activate
End With

End Sub

Hope it helps.