vba copy and paste

[Troychico]

I am new to vba and i was wondering if anyone could help me with this issue:

I am trying to copy a value from sheet 1 to sheet 2 and i would like it to be based on the contents in a cell on sheet 2. So, on Sheet 2 i have ~30 titles in column b and i would like to, for each one of them, copy a value from sheet 1. So, in sheet 1 I have ~245 rows of data and the titles are dispersed among the rows. Example i have a title called "TOTALS: THEME U" in cell A and the value that i want to have copied to sheet 2 is in the last cell to the right. One of the problems is that i have blank cells between the title and that value!

On sheet 2 one of the titles is called "U: Catastrophe & Humanity". All titles on sheet 2 are in column b and the value i would like to paste would go in column C. So, i would like to copy the values on sheet 1 to sheet 2 based on the titles: sheet 2 title: "U: Catastrophe & Humanity" and sheet 1 title: "TOTALS: THEME U". I would like to copy the values for all ~30 titles.

thanks for your help
Troy

[westconn1]

i am not sure that i follow what you are wanting to do exactly, but maybe this can help, move down sheet1 column A to get the titile you need to find in sheet2, from your description it seems the part you look for is the U of theme U you can then select the value in the last cell of the row
move to sheet 2 do a find for the string & ":" then put the data in the next cell in the row, this seems fairly simple, but if you need more help you might have to post some excel data

pete

[asmdev]

hi,
i did a small sampel for you where there is a macro inside which copy values from sheet1 that is larger than 100, so , you could modify the macro to suit your env

[Troychico]

i have attached an example. What i need to do is copy the total average (which for Theme A this number is located in G12). the problem is that when i add additional numbers the average totals will no longer be in G12...it would be over more to the right.
So, i need to locate the average for each theme (Totals: Theme xxx) and copy it to sheet 2.

thanks again for looking it over!
Troy

[asmdev]

k, u r lucky
i did some code for u, now it could get the mean value already after u press a button

plez try to understand the code in Module 1

[Troychico]

Thanks a lot...that is a good start! I have add the rest and i ran the code and i keep getting a 'Run time error 9: Subscript out of range'

what on earth does that mean?

I have included the workbook and VBA code.

thanks

[Troychico]

Thanks again for your help ----- asmdev!!!

[asmdev]

before i start coding more, em.. i need to make sure your sheet format or style, otherwise, the formular i gonna make would break, once u change ur sheet formation or add new style.

the problem is, i don't understand ur problem

let say if ur mean value would located in column AS (assume each year means one year information) so, we would use another method to bring the mean value to sheet2... right now, i wanna help, but i don't know how would exactly u wish.

if possible, plez give me information how this sheet is use, add, and calculated. coz ur new sheet looks totally different from the previous sheet u submit :0

[Troychico]

The first sheet I submitted was to see if I could get the code to work…I wanted to test it out before I added headers and formats, etc…, but looking back on it, it was not the best way to do it.

I will only show 4 years and when I add a new year I hide the oldest year (so it does not show). That’s why I have a column ‘AS’. Now I take the data (mean average) in the last column and will take that to sheet 2. This sheet summaries the information and therefore I am able to print them out.

One more thing, I have more than 3 themes. Because, as I stated in my original help message I have ~30 titles. I did not want to show them all because I figured that if it could work on three, then I could get it to work on the rest.

Thanks for your help and I hope I clarified thing a little better. Let me know if there is anything else needed.

Thanks again!

[asmdev]

ok, i got a suggestion for u,
make a button,
when people click on it, it would ask the user (input box) what column does those mean values reside. so after people key it the column name, then it would transmit the mean values (since the rows of those mean value are known alread) so basically it is just copying the mean values and paste it into sheet2.

[Troychico]

Would i use the same code in your first example or start over. if i start over with a input box (and by the way most examples i have seen for input boxes only have one line for users to enter a value. Would i have 30 input boxes...one for each title?). also, how would i have it pull just the mean for only the totals? would it be this code that you provided:

Sub check_mean_value()
 
    Dim update As Boolean
    
    col_get_next_two
    update = False
    
' Title A
    If Worksheets("Detail_Enrollment").Range(colA2 & colB2 & "18").Value <> "" Then
        update = True
        valMean(0) = Worksheets("Detail_Enrollment").Range(colA2 & colB2 & "12").Value
    End If
    
' Title B
    If Worksheets("Detail_Enrollment").Range(colA2 & colB2 & "29").Value <> "" Then
        update = True
        valMean(1) = Worksheets("Detail_Enrollment").Range(colA2 & colB2 & "29").Value
    End If
    
' Title C
    If Worksheets("Detail_Enrollment").Range(colA2 & colB2 & "44").Value <> "" Then
        update = True
        valMean(2) = Worksheets("Detail_Enrollment").Range(colA2 & colB2 & "44").Value
    End If
 
If update = True Then
    Worksheets("Summary_of_Enrollment").Range("I1").Value = colA2
    Worksheets("Summary_of_Enrollment").Range("J1").Value = colB2
End If
 
Worksheets("Summary_of_Enrollment").Range("B13").Value = valMean(0)
Worksheets("Summary_of_Enrollment").Range("B14").Value = valMean(1)
Worksheets("Summary_of_Enrollment").Range("B15").Value = valMean(2)

attached is the workbook

[asmdev]

hi Troychico,
my problem is actually i don't know how u gonna update ur sheet and how u would modify your sheet when another year coming.

the tricks that to copy a cell value from Sheet1 and put it in Sheet2 is like below:

Sheet2.Range("B8").Value = Sheet1.Range("A1").Value

where B8 (Sheet2) would contained the value of A1 (Sheet1)

[Troychico]

Can i still use a input box? Something like:
select cell for average for title one
select cell for average for title two
etc...

do you know i would create something like this and also use your code:
Sheet2.Range("B8").Value = InputBox("title one").Value

[asmdev]

em.. not

Sheet1.Range("B8").Value = InputBox("title one").Value

it should be

Sheet1.Range("B8").Value = InputBox("title one")

try to play with the above code, and u could see it is actually not so hard to put a value into a cell or to gain a value from user.

[Troychico]

thanks. i will have an input box for each title...so once the user selects the cell for the first title then another input box will come up for the next title and so-on-and-so-forth.

thanks for your help