Automate boring repetitive task

Hi Guys,

This is probably simple for you boffins but, although I'm sure it must be possible, it's doing my head in trying to get it to work!:-))

Everyday I get a spreadsheet emailed to me with data on 35 tabs - GroupKPI.xls.

I need to grab the "values" only (no formulae etc) from one specific line (#7) from 12 of these tabs and insert it into a local spreadsheet (LocalTGP.xls) on similarly named tabs making sure that the new day's data goes on the next available line on the LocalKPI.xls file and doesn't overwrite any previous entry thereby building a local history record.

I have 2007 Excel (.xlsx) but the file is an earlier version (.xls) and runs in Compatibility Mode.

I hope I've been able to explain the task completely and have not been overly succinct.

All help/advice greatly appreciated

Spence

Hi

what are the names of the tabs that you want to action?

rylo

Thanks rylo

Perth (L15), Broome (L15), Esperance (L15), Geraldton (L15) Port Hedland (L15), Dampier (L15)

Perth, Broome, Esperance, Geraldton, Port Hedland, Dampier

12 in total.

Spence

Hi

haven't tested this at all but see how it goes. It does assume that both workbooks are open in the same instance of excel, and there will always be something in column A of the row being copied.

See how it goes. If there are problems, then you may have to set up a couple of workbooks that reflect your structure - but you won't have to include all the sheet names.

Sub aaa()
  Dim DataWB As Workbook, OutWB As Workbook
  Set DataWB = Workbooks("GroupKPI.xls")
  Set OutWB = Workbooks("LocalTGP.xls")
  Set shtarr = Array("Perth (L15)", "Broome (L15)", "Esperance (L15)", "Geraldton (L15)", "Port Hedland (L15)", "Dampier (L15)", "Perth", "Broome", "Esperance", "Geraldton", "Port Hedland", "Dampier")
  For i = LBound(shtarr) To UBound(shtarr)
    DataWB.Sheets(arr(i)).Rows("7:7").Copy Destination:=OutWB.Sheets(arr(i)).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  Next i
End Sub

rylo

Thanks rylo - but I'm afraid it didn't work. Produced a compile error at aar (i).

I really appreciate your help.

I've attached a sample of the two spreadsheets, minus their data, in the hope it makes what I'm after clearer. The GroupKPI.xls is the spreadsheet that I open as an email attachment. LocalTGP.xls is the spreadsheet I need to copy the data into. I guess this means they are opened as separate instances of Excel. Certainly they appear as separate items when minimised to the task bar.

The only line I'm interested in is #7 (and only the values not any formulae) from the specific tabs in GroupKPI and it needs to be copied to the next available line in each relevant tab on LocalTGP moving down for the next one each day. Column A always has the date. Copying the entire line rather than specific cells covers any future possibility of additional data being shared.

Again - really appreciate your help
Spence

Hi

sorry, arr(i) should be shtarr(i).

Also the line

Set shtarr = Array("Perth (L15)", "Broome (L15)", "Esperance (L15)", "Geraldton (L15)", "Port Hedland (L15)", "Dampier (L15)", "Perth", "Broome", "Esperance", "Geraldton", "Port Hedland", "Dampier")

should be

shtarr = Array("Perth (L15)", "Broome (L15)", "Esperance (L15)", "Geraldton (L15)", "Port Hedland (L15)", "Dampier (L15)", "Perth", "Broome", "Esperance", "Geraldton", "Port Hedland", "Dampier")

rylo

Thanks Rylo for your continuing help - I appreciate it - but if you run out of time/patience, I understand!

Made the amendments you suggested but still no go. Macro didn't see the opened spreadsheet and prompted me to navigate to where it was saved. then the data copied was corrupted and it looked for each sheet separately.

I went 'old school' and used the "Macro Record" tool to show exactly what it is I'm trying to achieve. It's in the attached txt file. The problem with what I've record is not being able to allow for the one variable each day.

For example this line is a constant:

"Rows ("7:7"). Select

as the data required is always on this line.

However this line;

Rows ("168:168"). Select

is different every day as it steps down one so that the previous day's history is retained.

Is it possible to instruct the macro to use the manually selected line each time it is run to place the data copied instead of 'hard coding' a specific line?

The selected line is the same on each sheet each day within the LocalTGP spreadsheet.

Spence

Hi

row 168 is a new item, so lets put that on hold for the moment.

I opened both the files from #5. As I've had them opened before, they came in with the names GroupKPI[1].xls and LocalTGP[1].xls. I opened the general module in LocalTGP and put in

Sub aaa()
  Dim DataWB As Workbook, OutWB As Workbook
  Set DataWB = Workbooks("GroupKPI[1].xls")
  Set OutWB = Workbooks("LocalTGP[1].xls")
  shtarr = Array("Perth (L15)", "Broome (L15)", "Esperance (L15)", "Geraldton (L15)", "Port Hedland (L15)", "Dampier (L15)", "Perth", "Broome", "Esperance", "Geraldton", "Port Hedland", "Dampier")
  For i = LBound(shtarr) To UBound(shtarr)
    DataWB.Sheets(shtarr(i)).Rows("7:7").Copy Destination:=OutWB.Sheets(shtarr(i)).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  Next i
End Sub

It did error when trying to paste the data because I don't have the file
'V:\TGP List_Sox13\Production\[TGPs List TC 2012 5.8.xls]TGP Wsale L15'!$A331
and this is an item in your data. So apart from that it seemed to work. Can you get things to that point?

rylo

Hi,

I don't have the file on that path either but it shouldn't matter as the data I'm after is replicated from that source (I'm assuming) into the spreadsheet I'm sent and I'm only looking to copy and paste (values only) in to my local file.

I entered your recent code - just editing the file names to remove the [1] and got the error as per the attached image. Also tried renaming the local files to include the [1] - just in case. Same result.

Spence

HI

Ok, try a value paste output

Sub aaa()
  Dim DataWB As Workbook, OutWB As Workbook
  Set DataWB = Workbooks("GroupKPI[1].xls")
  Set OutWB = Workbooks("LocalTGP[1].xls")
  shtarr = Array("Perth (L15)", "Broome (L15)", "Esperance (L15)", "Geraldton (L15)", "Port Hedland (L15)", "Dampier (L15)", "Perth", "Broome", "Esperance", "Geraldton", "Port Hedland", "Dampier")
  For i = LBound(shtarr) To UBound(shtarr)
    DataWB.Sheets(shtarr(i)).Rows("7:7").Copy
    OutWB.Sheets(shtarr(i)).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
  Next i
End Sub

rylo

Works PERFECTLY!!! Thank you very much. I've been away for awhile so this task had built up from being a daily chore to being half a day's work! All up to date now in 10 mins. Thank you again - very much appreciated.

Side query arising out of the task now that it does run. When I open the daily spreadsheet directly out of Outlook it gets renamed with a sequential digit in brackets, eg. "GroupKPI (7).xls" (I'm sure this is because there are existing copies from previous days with the same name in Outlook's cache). The workaround is to save a local copy each time and work from that but I was wondering if the the macro could contain a 'wildcard' element to the spreadsheet's name - something like GroupKPI "*".xls - which would remove the need to save a local copy. Not a biggy really I can do the local save option.

Again many thanks for your help.

Spence

Spence

you could do something like

for each wb in workbooks
  if left(wb.name, 8) = "GroupKPI" then
    set DataWB = wb
  end if
next wb

rylo

Sorry for late response, rylo - been travelling for work last two weeks. Couldn't get that additional segment to work - probably because I was putting it in the wrong place! But thanks for trying - it really was just icing on the cake and I'm very pleased with the huge time saving the previous code has brought me. Spence