Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting Pivot data
Hi everyone,
I am pasting data from e.g sheet1 into sheet2 once I've used VBA to add an autofilter and remove the blanks (thanks to Ron's site) and on the sheet (and cell range) where I am pasting it, I use that range in the Pivot Table Wizard as the array for the Pivot table. I use sheet3 for the Pivot Table and in VBA select a cell within the table and use a PivotTable.Refresh to update the table. I wish to copy out the data from the updated Pivot Table in VBA (the amount of rows in the tbale will vary as the pasting in of data changes). However I am unsure how to declare the variables I was thinking, something like r = 2, last row = range("b65536).xlUp Coudl you help please? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting Pivot data
Hey Simon!! Here are some examples:
Find Last Used Cell: Sub FindLastCell1() Cells(Rows.Count, "A").End(xlUp).Select End Sub Sub FindLastCell2() Range("A:A").Find("*", Cells(1), _ xlValues, xlWhole, xlByRows, xlPrevious).Select End Sub Sub test() With Sheets("Sheet1").Range("A1:A" & .Range( _ "A" & .Rows.Count).End(xlUp).Row).Copy Sheets("Sheet2").Range("A1") End With End Sub Set StartRange and End Range: Sub StartEnd() Dim StartCell, EndCell As Range Set StartCell = Range("A3") Set EndCell = Cells(Rows.Count, "A").End(xlUp) Range(StartCell, EndCell).Select End Sub Sub zeroo() n = Cells(Rows.Count, "A").End(xlUp).Row Range("A3:A" & n).Select End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Simon" wrote: Hi everyone, I am pasting data from e.g sheet1 into sheet2 once I've used VBA to add an autofilter and remove the blanks (thanks to Ron's site) and on the sheet (and cell range) where I am pasting it, I use that range in the Pivot Table Wizard as the array for the Pivot table. I use sheet3 for the Pivot Table and in VBA select a cell within the table and use a PivotTable.Refresh to update the table. I wish to copy out the data from the updated Pivot Table in VBA (the amount of rows in the tbale will vary as the pasting in of data changes). However I am unsure how to declare the variables I was thinking, something like r = 2, last row = range("b65536).xlUp Coudl you help please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting values from pivot table still linked | Excel Discussion (Misc queries) | |||
pasting into pivot tables query | Excel Discussion (Misc queries) | |||
Why does data validation not work when pasting data into a cell. | Excel Discussion (Misc queries) | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) | |||
Copying & Pasting from Pivot Table | Excel Discussion (Misc queries) |