transfer rows of data into tables that has formulas embedded

[ngs007]

Hi

I have 90+ rows of data that I want to transfer into a table format on another worksheet. At present I have laboriously been copying and pasting from one to the other but am losing the will to live:

Id like to create a macro to do this for me. Is it a matter of recording the macro to replicate as I copy and paste or is there a better way....I have dabbled with a pivot table but cannot get it to do what I want.

Here is the code from a recorded macro that does one person/row of data but I dont know how to make this automatic for all rows of data (see below):

Sub staff_rpt()
'
' staff_rpt Macro
'

'
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-133]C[2]"
Range("B137").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-134]C[2]"
Range("D137").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-134]C[1]"
Range("F137").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-134]C"
Range("B141").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(Formal_obs_2013_14_sorted!R3C25:R3C196,Subject_Reports_2013_14!R[-1]C)"
Range("B141").Select
Selection.AutoFill Destination:=Range("B141:E141"), Type:=xlFillDefault
Range("B141:E141").Select
ActiveWindow.SmallScroll Down:=6
Range("B146").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Formal_obs_2013_14_sorted!R1C13:R1C196,RC[-1],Formal_obs_2013_14_sorted!R3C13:R3C196,R145C)"
Range("B146").Select
Selection.AutoFill Destination:=Range("B146:E146"), Type:=xlFillDefault
Range("B146:E146").Select
Selection.AutoFill Destination:=Range("B146:E155"), Type:=xlFillDefault
Range("B146:E155").Select
ActiveWindow.SmallScroll Down:=18
Range("A161:F163").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-114]C[9]"
Range("A161:F163").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-158]C[9]"
Range("A164:F166").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-161]C[28]"
Range("A167:F169").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-164]C[47]"
Range("A171:F173").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-168]C[10]"
Range("A174:F176").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-171]C[29]"
Range("A177:F179").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-174]C[48]"
Range("A177:F179").Select
End Sub




Any help would be appreciated
Thanks

[Richard Buttrey]

Hi,

Are you able to upload your workbook so that we can see the request in context. This will be far easier than trying to 'reverse engineer' your code and understand your layout.

Show your data and then the results that you expect to see after any macro has run. A sample of data will be sufficient as long as it represents all permutations.

[ngs007]

Hi

Thanks for the reply. I have attached a sample from the actual workbook....obviously the actual links refer to the original workbook but am guessing/hoping you get the idea...I want to be able to automatically transfer the data from the rows in worksheet one to tables/report (for individual people)on worksheet two....hope this makes sense and look forward to your reply
many thanks

[Richard Buttrey]

Hi,

I'd be inclined to use something like the following

First change the formulae in Sheet 2 in the A6:F45 range to pick up the values from Sheet 1 using the name in A1 as the key. So for instance in B11 you would have

Please Login or Register  to view this content.

change all the other formulae in a simple way so that they pick up the relevant column from the sheet1 data.

Then run the following macro.

Please Login or Register  to view this content.

As it stands it will convert all the formulae to values, i.e. there would be no direct links so you'd need to run the macro every time the workbook was opened, so you could put it in the workbook open event.

Alternatively if you wanted to keep the links then use

Please Login or Register  to view this content.

[ngs007]

Hi
Many thanks for this and i think it may well work but im struggling with index and match...do you think you could do the first few INDEX and Match formulas for me as im not quite sure what to do here?
Thanks again

[Richard Buttrey]

Hi,

What do you expect to see in the B:E columns? For instance do 1s only ever appear in the 1 column, 2s in the 2 etc.?
Would you manually add the results you expect to see for say Sue Cole (I use her because there are a variety of results).

Also upload the Formal_Obs_2013_14_sorted and Formal_Obs_2013_14 workbooks.

It's not clear to me why there are two. Isn't all the information you require contained on Sheet1, which in turn comes from the Formal_Obs_2013_14 workbook?

[ngs007]

Hi
Thanks again for your reply.......its unfortunately not quite as simple as what you suggest. The worksheets are linked to other worksheets which can be added to and updated daily, weekly or whenever. I created the 'sorted' worksheet to enable me to copy and paste more easily!

I ran the macro:
Dim x As Long
For x = 1 To Sheet1.Range("A1").CurrentRegion.Rows.Count - 1
Sheet2.Range("A1") = Sheet1.Range("C" & x + 1)
Sheet2.Range("A1:F45").Copy Destination:= Sheet3.Range("A" & x * 45 - 44)
Next x

and all 'new people' were added (great) but the linked formulas referenced the wrong cells as in the attached. I need them to reference the next row down on sheet 1 rather than pasting to the new sheet and referencing 45 rows down (ie where it has been pasted to). Hopefully that makes sense and you can adjust the macro to do what I would love it to do and save me hours and hours of cut and paste!
Many thanks again

[Richard Buttrey]

Hi,

Does the attached help.

Once you get to row 123 on the data the names are not unique which is why details for rows 123:200 will appear with row 123 results.
When you need to run the macro again I suggest you just clear out all of sheet3, don't try and delete the rows since it will take ages.. Select all the cells and delete them, then clear out all the conditional formats so that you avoid building up duplicate CFs.

[ngs007]

Hi again

I think we are getting closer but i dont think I am explaining myself clearly enough. I have attached the file again - this time with the macro having run. If you go to sheet 3 you will see that the next table (starting with 'Hadaway Gayle') has correctly placed this persons name in the correct place but all the other linked cells have been 'moved on' by 45 rows. I want them simply calculated by the same data that is on the row for Hadaway Gayle on sheet 1 (as per Jane Davies). Then the same for Heath helpAmanda and so on....ie I want each row on sheet 1 to be calculated by the relevant data on sheet 1 but plugged into the tables as per sheet 2. Hope this makes sense and you can wave your magic wand to help me? I have been trying to work out how to adjust the macro but no joy Im afraid.
Hope you can help and hope I am being clearer.
Kind regards.
(PS ideally I would like the tables on sheet 3 to be formatted in exactly the same way as that on sheet 2 too...ie cell width and height but if this is not possible I can tidy up formatting afterwards).
Keeping my fingers crossed!

[Richard Buttrey]

Hi,

The file you attached did not have the all important helper columns G&H on sheet 2. These identify the relevant row from the data sheet each time the macro changes the name in A1, and the columns so it's not surprising that you weren't seeing the right results. Did you simply try and use the macro from that workbook with your own workbook which would not have the helper columns? (These could be hidden of course).

I'm attaching it again with the macro already having been run. I've also added an additional macro which sets all the row heights on sheet 3 after the main macro has finished.

[ngs007]

Hi again

That's great - almost perfect! The only thing/challenge now is that if there are more than one times that a particular criteria occurs I need it to add the amount of times it occurs up. So in the attached sample I have indicated that for the first person some of the criteria occurs twice but at present the lesson observation or pedagogical analysis only shows '1' (if it is true)....can you adapt it so that will tally things up rather than just true or false if yo get what I mean?

I have attached the sample with your macro but now with additional data for Davies Jane and Hadaway Gale (i have added this on sheet 1) to see if you can work your magic?
Kind regards again and a merry xmas!

PS
I have also attached the original with formulas so you can see how it should work but had to zip your macro one in order to fit to forum size.

[Richard Buttrey]

Hi,

See attached.

It will take slightly longer since COUNTIFS() are processing more calculations than INDEX/MATCH combinations.

Is there any reason why you don't hold this analysis in the Forrmal_Obs_2013_14 workbook and avoid the need for linking files.

[ngs007]

That's brilliant thanks - my best xmas present!
The reason I have transferred to a 'sorted list' is simply because in the formal obs 2013-14 workbook they are sorted alphabetically by surname but for the reports i need them sorted alphabetically by dept/faculty and then alphabetically by teacher's surname and then first name. I dont know how to do this in one go which is why i transferred to a sorted list first....now if you could enable this while maintaining everything else as per your last download then all my xmases would have come at once!

I attach the original list from the formal obs 2013-14 workbook just in case?
Once again many thanks - you have saved me hours and hours of laborious copy n pasting and if you can do directly from formal obs 2013-14 this would be even better again

PS
One tiny thing (which I can/will edit is column width - ie they are narrower when the 'create tables' macro is enabled...do I need a macro to fix these like the row height one or?

Merry xmas and New Year! Thanks again you have been brilliant, patient and tolerant

[Richard Buttrey]

Hi,

I'll take a look at a direct from formal file after the festivities return us to something like normal!

The macros don't change the column widths, so just set the sheet 3 column widths to whatever you want. I thought I had aligned them to the widths on sheet2 but maybe something got changed.

Regards

[ngs007]

Hi again - festivities over?!
I tried to apply the macro to my actual workbook and come up with 'compile error; variable not defined'

I took the 'option explicit' out and come up with runtime error 424 - object required error

Here's the macro:

Option Explicit

Sub CreateTables()
Dim x As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For x = 1 To Formal_obs_2013_14_sorted.Range("A1").CurrentRegion.Rows.Count - 1
Subject_Reports_2013_14b.Range("A1") = Formal_obs_2013_14_sorted.Range("C" & x + 1)
Subject_Reports_2013_14b.Range("A1:H45").Copy
Subject_Rpts_2013_14c.Range("A" & x * 45 - 44).PasteSpecial (xlPasteAll)
Next x
Call Module13.RowHeight
Application.Calculation = xlCalculationAutomatic
End Sub
Sub RowHeight()
Dim ar
Dim x As Long, y As Long
Application.Calculation = xlCalculationManual
ar = Array(18.75, 24, 12.75, 15, 30, 15, 8.25, 15, 30, 15, 14.25, 14.75, 14.75, 14.75, 14.75, 14.75, 14.75, 14.75, 14.75, 14.75, 11.25, 13.5, 30, 15, 17.25, 15, 15, 15, 15, 15, 15, 15, 15, 15, 16.5, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15)
Application.ScreenUpdating = False

For x = 1 To Formal_obs_2013_14_sorted.Range("A1").CurrentRegion.Rows.Count - 1
For y = 1 To 45
Subject_Rpts_2013_14c.Range("J" & x * 45 - 45 + y).RowHeight = ar(y - 1)

Next y
Next x
Application.Calculation = xlCalculationAutomatic
End Sub

Can you let me know what I am doing wrong? Best regards

[ngs007]

PS
I have now highlighted the row that the error occurs (I have double and triple checked spelling and cannot see any errors):
Option Explicit

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Please remember to wrap your code in code tags, we are rather keen on that around these parts! I'll edit it for you on this occasion.

Which line does the macro halt with when you click Debug? I suspect it's on the line
For x = 1 To Formal_obs_2013_14_sorted

This has been changed from my original which was
For x = 1 To Sheet1

The macro as changed will not recognise the Formal..... bit. We know you're talking about a workbook name but the macro has no idea that that refers to a workbook name, and neither is it necessary in this case. The macro needs to know which sheet contains your data, and it's generally good practice to use the VBA sheet code name rather than a tab name. Hence the reference to 'Sheet1'. If your data sheet should be different when you look at the sheet code name in the VBE then change it accordingly.

[ngs007]

Please ignore above - duh - I thought I had worked it out - ie I used the sheet numbers rather than what I named them as so the code now looks like this:

Option Explicit

Sub CreateTables()
Dim x As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For x = 1 To Sheet1.Range("A1").CurrentRegion.Rows.Count - 1
Sheet9.Range("A1") = Sheet1.Range("C" & x + 1)
Sheet9.Range("A1:H45").Copy
Sheet24.Range("A" & x * 45 - 44).PasteSpecial (xlPasteAll)
Next x
Call Module13.RowHeight
Application.Calculation = xlCalculationAutomatic
End Sub
Sub RowHeight()
Dim ar
Dim x As Long, y As Long
Application.Calculation = xlCalculationManual
ar = Array(18.75, 24, 12.75, 15, 30, 15, 8.25, 15, 30, 15, 14.25, 14.75, 14.75, 14.75, 14.75, 14.75, 14.75, 14.75, 14.75, 14.75, 11.25, 13.5, 30, 15, 17.25, 15, 15, 15, 15, 15, 15, 15, 15, 15, 16.5, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15)
Application.ScreenUpdating = False

For x = 1 To Sheet1.Range("A1").CurrentRegion.Rows.Count - 1
For y = 1 To 45
Sheet24.Range("J" & x * 45 - 45 + y).RowHeight = ar(y - 1)

Next y
Next x
Application.Calculation = xlCalculationAutomatic
End Sub

However, when I try to run it - it starts off fine and then after a few seconds or so it says 'not responding' and just hangs...help?!

[ngs007]

Apologies - I dont know what you mean by 'code tags' so if i have incorrectly listed below please accept my apologies again!

[Richard Buttrey]

Code needs to be encloded within the [ CODE] & [ /CODE] tags. (ignore the leading space character required here to show the literal text)

You can do this by selecting the code and clicking the # symbol in the reply pane.

Does the sheet which contains all the data have the VBE code name 'Sheet1', the layout sheet for the results 'Sheet9' and the output sheet 'Sheet24', and is the RowHeight prodecure contained within a module called 'Module13'?


As I mentioned this does take several minutes if you have 200 names and at times Excel may show a 'Not Responding' message. Often these come and go as processing continues. I'd be inclined to ignore this and be patient. If after say 15 minutes it still hasn't finished then there may be some other problem with your system. If Excel crashes/debugs then obviously there is an Excel problem.

You can test the process by restricting the main loop to say 10 names. So where you have

Please Login or Register  to view this content.

change that temporarily to

Please Login or Register  to view this content.

If this proceeds to conclusion then there seems no reason why 200 names won't eventually process.

[ngs007]

Hi
Yes sheet1, sheet9, sheet24 and Module 13 were the actual VBE code names
Bad news Im afraid.....this didn't work in the actual document
I can see the code works fine in the sample data but when I transferred it to the actual document even with

Please Login or Register  to view this content.

it hung (I waited 45minutes)
Earlier I sent you the data direct from the 'formal obs page' with a view to looking at this and trying to generate the report directly rather than having to sort it first - maybe (although I doubt it) it would be better to try it direct like this?
I really thought/hoped you had cracked it so a real shame there - any further ideas?
best rgds

[Richard Buttrey]

Hi,

Can you explain what you mean by 'transferred it to the actual document'?

Better still upload the actual document so that we can detect why it's not working before we get it to work with the 'formal' workbook. If it works in one there's no reason it wouldn't work with any workbook with the same layout format.

Assume we then get the formal document to work, what determines the order in which you want the output?

[ngs007]

Hi

I have tried to attach 'the actual document' but even when zipped its 3mb so above limits. The order of output will be by department so first is Art, then drama etc

I can send the actual document by email if that helps?

regards

[Richard Buttrey]

Hi,

Why is the actual document so much bigger than the one you attached previously?

Can we rewind a step.

With the last workbook I sent you did did you rerun the macro before you did anything with the workbook. I suggest that is the first thing you confirm. I see no reason why that wouldn't work.

So assuming it does work, exactly what steps are you then doing to arrive at the 'actual' workbook you say isn't working?

Can't you just use the workbook I did send, and if the underlying workbook from which it draws its data has changed, just use Data Edit Links to change the current linked workbook to the workbook which does contain the data?

[Richard Buttrey]

Hi,

One other way forward.

Take a copy of the actual document but delete most of the rows leaving just say 100 so that the size is reduced and it will attach. here. That will enable us to check why it apparently isn't working.

[ngs007]

Hi again

OK - I have done all that you said above and here are my responses:
With the last workbook you sent I did rerun the macro before I did anything with the workbook - unfortunately it didnt work how i want it to. I noticed that the 'subjects', 'dept/faculty' and 'pay scale' are not the same for each person as they are on sheet two - ie they have jumped 45 or so rows. I fixed that with the index function. I have applied to whole document and it works!! It did take a very (very) long time but that doesnt matter as such - im just so pleased!

Ideally I would like it to run faster and directly from the worksheet (without having to sort it) but these are minor developmental things that I will continue to work on. Just in case though I attach the workbook without the sorted worksheet in and I would want to create the same reports directly from this. The reports would be sorted in dept/faculty first and then surname of staff member order.

Thank you for all your support and a happy new year!

PS
Even better would also to be able to create a simle form that allowed you to add names etc to this workbook and slot them in alphabetical order and at the same time amending the report so they would be slotted in to correct department again in alphabetical order within that department