Copy/paste macro

[Barrakooda]

Hi all

Bit stumped & seeking assistance, vba isnt my thing.

I have a work book with several sheets in it. I am trying to copy the data from M2 on each sheet & paste it into Column C but in the next blank cell.

At the end of running the macro i would like to refresh a pivot table = PivotTable1

Help would be appreciated

So far i have

Sub RefreshGraphs()
'
' RefreshGraphs Macro
'


'
    Sheets("PIVOT").Select
    Range("D10").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
    Sheets("MTD Trending").Select
End Sub

&

Worksheets("TRUCKS").Range("M2").Copy _
        Destination:=Worksheets("TRUCKS").Cells(Worksheets("TRUCKS").Rows.Count, "C").End(xlUp).Offset(1, 0)


Worksheets("EX2600").Range("M2").Copy _
        Destination:=Worksheets("EX2600").Cells(Worksheets("EX2600").Rows.Count, "C").End(xlUp).Offset(1, 0)


Worksheets("WA500").Range("M2").Copy _
        Destination:=Worksheets("WA500").Cells(Worksheets("WA500").Rows.Count, "C").End(xlUp).Offset(1, 0)
        
Worksheets("WA600").Range("M2").Copy _
        Destination:=Worksheets("WA600").Cells(Worksheets("WA600").Rows.Count, "C").End(xlUp).Offset(1, 0)


Worksheets("WA600LC").Range("M2").Copy _
        Destination:=Worksheets("WA600LC").Cells(Worksheets("WA600LC").Rows.Count, "C").End(xlUp).Offset(1, 0)


Worksheets("992K").Range("M2").Copy _
        Destination:=Worksheets("992K").Cells(Worksheets("992K").Rows.Count, "C").End(xlUp).Offset(1, 0)


Worksheets("16M").Range("M2").Copy _
        Destination:=Worksheets("16MK").Cells(Worksheets("16M").Rows.Count, "C").End(xlUp).Offset(1, 0)


Worksheets("D10T").Range("M2").Copy _
        Destination:=Worksheets("D10T").Cells(Worksheets("D10T").Rows.Count, "C").End(xlUp).Offset(1, 0)

 

[Roodey]

Can you post the sheet or a screendump (with blurred data) with the result of the macro and how it should behave?
The table doesn't get expanded, right?

 

[Barrakooda]

Hi Roodey

unfortunately cant link to pic or know how to upload from work PC

However what it is doing is putting the value into C33 instead of C2 (C1 has a header). By putting it into C33 it has added another row to the table.

 

[Roodey]

Okay, because firstrow and lastrow both give back 33 I assume cell C2 is not empty.
Do you want it to be overwritten then? Is it always C2?

 

[Barrakooda]

C2 to C32 in table are blank. It's like it seeing the table as data & going straight to C33

 

[Roodey]

I cannot help you with seeing the sheet and what your expectations are.

The code below reads the data in your table, table named Table1.

Sub Temp()

Dim tbl As ListObjectSet tbl = ActiveSheet.ListObjects("Table1")
MsgBox tbl.Range.Rows.Count
MsgBox tbl.HeaderRowRange.Rows.Count
MsgBox tbl.DataBodyRange.Rows.Count
Set tbl = Nothing

End Sub

 

[Barrakooda]

No worries I'll run that tomorrow when I get back to work. Thanks.

 

[Barrakooda]

Get Compile error: Syntax error on this line

Dim tbl As ListObjectSet tbl = ActiveSheet.ListObjects("Table1")

have confirmed that i was on sheet with Table1 on it

 

[pvr928]

Hi Barrakooda

The two lines seem to have run together.

Try:

Dim tbl As ListObject 
Set tbl = ActiveSheet.ListObjects("Table1")

Cheers

pvr928

 

[Barrakooda]

Hi pvr928

getting Run-time error '9' subscript out of range

Sub Temp1()


Dim tbl As ListObject
[COLOR=#ff0000]Set tbl = ActiveSheet.ListObjects("Table1")[/COLOR]
MsgBox tbl.Range.Rows.Count
MsgBox tbl.HeaderRowRange.Rows.Count
MsgBox tbl.DataBodyRange.Rows.Count
Set tbl = Nothing


End Sub

 

[pvr928]

Hi Barrakooda

The error will be occurring because there is not a Table called "Table1" on ActiveSheet.

The fix is to ensure this is the case.

I don't like referring to ActiveSheet, as there is a risk that the ActiveSheet is not the one the code needs to refer to.

I prefer the syntax:

Set tbl = Sheets("Sheet1").ListObjects("Table1")

Where "Table1" is indeed located on the tab called "Sheet1" [or whatever the name of the tab is in your file].

Cheers

pvr928