Copy/paste macro

Barrakooda

Board Regular
Joined
Feb 3, 2012
Messages
75
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

Code:
Sub RefreshGraphs()
'
' RefreshGraphs Macro
'


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

&

Code:
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)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Add the following line to your code which does the copying.
Put it at the end. After copying everything it executes the macro which refreshed the pivottable.
When it has executed both the macro's it is done.

Code:
Call RefreshGraphs
 
Upvote 0
Thx Roodey

do you know how the code is suppose to be structured at the start/end?

i assume its something like Sub ??? & have End with at the end.
 
Upvote 0
Below you find the total macro that copies and refreshes the pivottabel.
Paste the code in Module1 where the macro RefreshGraph is located.

Code:
Sub CopyAndRefresh()

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)

Call RefreshGraphs

End Sub
 
Upvote 0
Hi Roodey, thanks mate.

i should have mentioned that it is pasting data into a table (Column C)

so far it pastes it in the next row below the table.

Please note Row 1 has all the headers for columns & ill have to work out how to stop the column auto calculating

wonder if i should replace the end part with

Code:
.End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
 
Last edited:
Upvote 0
also M1 is located outside the table on each sheet as it has a formula referring to another sheet with query data on it.
 
Last edited:
Upvote 0
I don't get your question to be honest. I only combined the 2 macro's. So I did nothing with the original macro that copied the data from all the other sheets.
What is not working correctly in that macro?
 
Upvote 0
Hi Roodey

It copies the cell data fine but its adds another line to bottom of table, not the next blank cell in Column C.

My apologies was in a rush out of the office yesterday & forgot to mention it was pasting into a table
 
Upvote 0
I think I understand you now. That is because you use Rows.Count with End(xlUp).
VBA then looks from cell C1048576 going up. The first cell in column C that is not empty will be used in your macro.

Try running this macro. If the messagebox shows the right numbers you can implement this in your macro.

Code:
Sub Temp()


Dim FirstRow As Integer
FirstRow = ActiveSheet.Cells(1, 3).End(xlDown).Row + 1


Dim LastRow As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row + 1


MsgBox "Firstrow: " & FirstRow & vbNewLine & "Lastrow: " & LastRow


End Sub
 
Upvote 0
Roodey

Firstrow: 33
Lastrow: 33

I think it may be because i am trying to copy a cell outside of & pasting that value into a table. Row 32 is last row of Table1
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top