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)

 

[Barrakooda]

Thanks that returned 33, 1, 31

i really have to work out how to add file, but this is a work PC & restricted.

Sub Temp1()


Dim tbl As ListObject
Set tbl = Sheets("TRUCKS").ListObjects("Table1")
MsgBox tbl.Range.Rows.Count
MsgBox tbl.HeaderRowRange.Rows.Count
MsgBox tbl.DataBodyRange.Rows.Count
Set tbl = Nothing


End Sub

 

[Barrakooda]

managed to get photobucket working

Last row below table shows what is happening when current macro is run = Copying data from M1 value to next blank cell in column C.

[/URL][/IMG]

 

[Roodey]

Hi Barrakooda,

I did some assumptions. I used a cell (T1) on your mastersheet (that contains the table) that holds the initial row that the values should get inserted in to.
The macro adjust the counter so the next row gets filled. Is this correct?
Questions I have:
What if the last day of the month had occured? Now it continues after 31, going to 32.

Sub Temp()

Dim MasterSheet As String
MasterSheet = "Blad1" 'Insert the name of your sheet that contains the table here


Dim shMaster As Worksheet
Set shMaster = ThisWorkbook.Sheets(MasterSheet)


Dim counter As Integer
counter = shMaster.Range("T1").Value


Dim ws As Worksheet


For Each ws In Worksheets
    If ws.Name <> MasterSheet Then 'I assume you have 1 mastersheet, with the table, all other sheet that contain detail insight
        shMaster.Cells(counter, 3).Value = ws.Range("M2").Value
        counter = counter + 1
        shMaster.Range("T1").Value = counter
    End If
Next ws


End Sub

 

[Barrakooda]

Hi Roodey, how was your weekend mate?

The main aim of the work book is to feed into a "dashboard" as per screenshot below (i have included the sheets tabs at the bottom)

There is a query data sheet that pulls info in from our software, when we change the date each day (on dashboard) the query refreshes. I want to then copy the value in M1 on each sheet into the next blank cell in Column C of each table.

Each day this gets updated & is intended to show a progression during the month. At the end of the month we save a copy as the next month e.g. this would be August in this example, data in column C get wiped & we start all over again.

The is a table for each fleet on separate sheets, these tables feed into a single pivot table, which feed into a pivot charts. Slicer allows the user to select which one they want to look at & also print out (for morning meetings)

Sheets are;

TRUCKS
EX2600
WA500
WA600
WA600LC
992K
16M
D10T

[/URL][/IMG]

[/URL][/IMG]

 

[Roodey]

Weekend was very good! Kids had a blast.

Thanks for the explanation.

So you change the date on the sheet Dashboard? After that you do the copying.
On which cell is that date on the sheet Dashboard?
I assume that date corresponds with a record in your table on the separate sheets.
I can loop through the table on 1 one sheet, for example TRUCKS, looking for the date.
The row that matches with the date from the sheet Dashboard will receive the data from cell M1 in column C.


Date 6/07/2017 is on cell A7 on your screenshot below. I assume for all tables that specific date is in cell A7.


If you reply to this I will create an example macro.


Edit:
Can you give the names of the remaining sheets? I can't read them very well.

 

[Roodey]

New attempt:
Create sheets CopySheets.
Fill in column A, starting with A1, the names of the sheets which require the copy action (TRUCKS, EX2600, WA500, WA600, WA600LC, 992K, 16M, D10T).

Paste my macro in your module in Visual Basic.
Change the following line in my macro: CopyDate = sh1.Range("A1").
I tested with A1. If your date on sheet Dashboard is in D3, then change A1 to D3.

Test the macro! Always make a copy before testrunning this macro.

Sub Temp2()

Dim sh1 As Worksheet
Set sh1 = ThisWorkbook.Sheets("Dashboard")


Dim CopyDate As Date
CopyDate = sh1.Range("A1") 'Gets date to match in table


Dim sh2 As Worksheet
Set sh2 = ThisWorkbook.Sheets("CopySheets")


Dim varLastRowCopySheets As Integer
varLastRowCopySheets = sh2.Cells(Rows.Count, 1).End(xlUp).Row


Dim LookupSheet As String
LookupSheet = sh2.Cells(1, 1).Value


Dim sh3 As Worksheet
Set sh3 = ThisWorkbook.Sheets(LookupSheet)


Dim varLastRowLookupSheet As Integer
varLastRowLookupSheet = sh3.Cells(Rows.Count, 1).End(xlUp).Row 'Get last row from the lookup sheet


Dim TargetRow As Integer 'Variable for the targetrow for the copy action


For d = 2 To varLastRowLookupSheet
    If CopyDate = sh3.Cells(d, 1).Value Then
        TargetRow = d 'Insert targetrow in to variable
    End If
Next d


If TargetRow = 0 Then
    MsgBox "Date not found, macro gets aborted!", vbCritical 'Error message if date is not found
    Exit Sub
End If


For s = 1 To varLastRowCopySheets
    Sheets(sh2.Cells(s, 1).Value).Cells(TargetRow, 3).Value = Sheets(sh2.Cells(s, 1).Value).Range("M1").Value 'Copy in to targetrow, per sheet from CopySheets
Next s



End Sub

Edit:
I didn't insert checks for errors. If the date on sheet Dashboard is a valid date and not a string.
And if you name a sheet in sheet CopySheet that does not exist you will receive a standard VBA error message.

 

[Barrakooda]

Hi Roodey

Thanks for that, current back home but will try that tomorrow.

As for the date i change that on the dashboard sheet (top image) where it says Report End date. If that date changes it refreshes the query & pulls info from software. The Query sheet has formulas in it as we are specific data (the results of this formula are shown in M1 in each sheet as that is what we want to copy & paste into table.

However bear in mind that we are reporting in the "previous" 24hr period, so in the top image where the report end date says 2/7/17 0600, we are in fact looking at data from 1/7/17 0600 to 2/7/17 0600, so we are actually reporting for 1/7/17.

ill send you a PM

 

[Barrakooda]

Roodey

Thanks for your assistance with this, everything appears to be up & running.

Your time has been appreciated.