Pull in column Data, perform calculation, Copy Results, and repeating

[Hamond]

Hi,

I know what I need done and how to do it but don't know how to automate hence I thought I'd give this forum a go. I could do it manually but it would take me ages! I'd describe my VBA knowledge as very basic in most areas.

Here's what I'm seeking to do.

I have four sheets in the same workbook:

Source Sheet - consists of 200 columns of data starting column B
Calculation Sheet - consists of some data and calculations
Results sheet - consists of results from the calculations
Results Copy - consists of a copy of the results from the results sheet

What I want to do is take each column of data from the source sheet, put it into column B (B3) of calculation sheet. Then run a macro, this performs calculations on the data in column B and data in other columns of the same sheet that does not change. When the macro is done, (a couple of seconds), it pastes the results in the results sheet (a1), I want to take a copy of these results and paste into a results copy.

I then want to repeat the same steps/cycle for the next variable in the source sheet (column C). But when copying from results sheets to Results Copy sheet, I don't want to paste over the results from the previous variable, so I need to paste the data underneath the previous results, say separating them by one row or something.

Would be greatful for any ideas, recommendations and advice.

Thanks,

Hamond

[Poo_Drop]

This shouldn't be hard to do at all, but may i ask why you have to do all that copying and pasting from sheet to sheet? Sounds like a waste of CPU cycles to me.

[figment]

this should be an easy task, but it would help if you could post and example workbook.

[Hamond]

Hi,

Ok, I've attached an example. I've also cut out one of the steps - copying and pasting from the Results sheet to Results copy sheet. Now the output from the calculations is stored on the calculation sheet and I just need to copy the results to the results sheets with the appropriate labels.

In the attached example, the results sheets displays the calculated results for the first two codes in the source sheet. But I want to do this for all 234 codes. Note I need to copy over the code number so I can indentify which results relate to which codes.

Thanks,

Hamond

[figment]

give this a try
Attachment 7520

[Hamond]

Hi,

I changed the code suppled to accomodate more rows and columns as per below. Now it it is taking ages to run, in fact after about a minute the screen just freezes.

I've tried setting screen updating to false, but this seems to makes very little difference.

I noticed the it pulls in the data from the source sheet one row/cell at a time, I think this is what is causing it to run so slow. Is it possible to change the code so that it just copies and pastes the entire range for each column from the source sheet into the calculation sheet instead by cell?

Thanks,

Hamond

Sub Calculating()
Dim a As Long, b As Variant, c As Long
a = 2
While Worksheets("Source Sheet").Cells(3, a) <> ""
For c = 3 To 573
Worksheets("Calculation Sheet").Cells(c, 2) = Worksheets("Source Sheet").Cells(c, a) 'Imports the data into calculation sheet
Next
Application.Calculate
b = Worksheets("Calculation Sheet").Range("B576:AG585")
Worksheets("Results Sheet").Range("b" & 2 + (12 * (a - 2)) & ":AG" & 11 + (12 * (a - 2))) = b 'sets where to paste results
Worksheets("Results Sheet").Range("b" & 1 + (12 * (a - 2))) = Worksheets("Source Sheet").Cells(1, a) 'create labels for each series in resulst sheet
a = a + 1
Wend
End Sub

[figment]

wow its been a long time sense i have time to post here. so here we go

first copy this formula into colume b on the Calculation sheet

=VLOOKUP('Source Sheet'!A3,'Source Sheet'!$1:$65536,'Calculation Sheet'!$A$2,FALSE)

then try runing this code

[VBA]Sub Calculating2()
Dim a As Long, b As Variant
For a = 2 To Worksheets("Source Sheet").Range("B3").End(xlToRight).Column 'Cells(3, a) <> ""
Worksheets("Calculation Sheet").Range("A2") = a
Application.Calculate
b = Worksheets("Calculation Sheet").Range("B576:AG585")
Worksheets("Results Sheet").Range("b" & 2 + (12 * (a - 2)) & ":AG" & 11 + (12 * (a - 2))) = b 'sets where to paste results
Worksheets("Results Sheet").Range("b" & 1 + (12 * (a - 2))) = Worksheets("Source Sheet").Cells(1, a) 'create labels for each series in resulst sheet
Next
End Sub[/VBA]