VBA code to copy values from 4 sheets to 5th sheet.

[aacod]

I have 5 excel sheets. Sheets 1 to 4 are labelled as BA, DD, JJ and DP. In each sheet there are numerical values in cells H7 to Q7. The values in cells from H7 to Q7 come from other cells on the same sheet. The formula in cells H7 to Q7 is: =Cell#. I need a VBA code to bring the values from cells H7 to Q7 from all 4 sheets to sheet 5 labelled as Get Total and to total the values on sheet 5 under each column from column H to Column Q.

Thanks.

 

[cooper645]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

so all the H7 from sheets 1 to 4 totalled on the fifth sheet in cell H7 and so on across your range?

Also why does the solution need to be VBA? You could simply use a formula:

=SUM(BA!$H$7,DD!$H$7,JJ!$H$7,DP!$H$7)

 

[cooper645]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

as VBA code the following is what you require

Sub Get_Totals()
Dim i As Long
For i = 8 To 17
Sheets("Get Total").Cells(7, i).Value = Sheets("BA").Cells(7, i).Value + Sheets("DD").Cells(7, i).Value + Sheets("JJ").Cells(7, i).Value + Sheets("DP").Cells(7, i).Value
Next i
End Sub

 

[aacod]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

cooper645,

Following is what I am trying to achieve On Sheet 5.

Thanks.

aacod

		H	I	J	K	L	M	N	O	P	Q
From Sheet 1		45000	2000	600	2000	400	550	95
From Sheet 2		80000	5000	1000	1000	200	500	75
From Sheet 3		70000	1000	750	1500	300	600	150
From Sheet 4		90000	1500	850	3000	450	650	80
	Total	285000	9500	3200	7500	1350	2300	400

<colgroup><col><col span="7"><col span="5"></colgroup><tbody>
</tbody>

 

[cooper645]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

aacod,

the code below does as you require, I have made an assumption on the range to be used in the Get totals page, will be from cells H1:Q4 for the info drag and the totals in H5:Q5.

Please advise me if this is not the case and we can get the code amended to suit your needs better.

[COLOR=#0000ff]Sub[/COLOR] Get_Totals2()


[COLOR=#0000ff]Dim[/COLOR] LastRow [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Long[/COLOR], i [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]Long[/COLOR]

Range("H1:Q5").ClearContents

LastRow = Cells(Rows.Count, "H").End(xlUp).Row

[COLOR=#0000ff]For[/COLOR] i = 8 [COLOR=#0000ff]To[/COLOR] 17
    [COLOR=#0000ff]With[/COLOR] Sheets("Get Total")
        .Cells(LastRow, i).Value = Sheets("BA").Cells(7, i).Value
        .Cells(LastRow + 1, i).Value = Sheets("DD").Cells(7, i).Value
        .Cells(LastRow + 2, i).Value = Sheets("JJ").Cells(7, i).Value
        .Cells(LastRow + 3, i).Value = Sheets("DP").Cells(7, i).Value
            [COLOR=#0000ff]With[/COLOR] Sheets("Get Total").Cells(LastRow + 4, i)
                .FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
                .Font.Bold = [COLOR=#0000ff]True[/COLOR]
[COLOR=#0000ff]            End With
    End With
Next[/COLOR] i

[COLOR=#0000ff]With[/COLOR] Range("G5")
    .Value = "Total"
    .Font.Bold = [COLOR=#0000ff]True[/COLOR]
[COLOR=#0000ff]End With

End Sub[/COLOR]

Kindest Regards,

Coops

 

[aacod]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

Coops,

With the above code I get following:
'Subscript out of range'.

I need to bring the data from sheets 1 to 4 in Cells H7 to Q7 to sheet 5, tab labelled as Get Total.

On Sheet 1, Tab 'BA' following are the values from cells H7 to Q7:

Cell H5 has data 4500
Cells IJKL 7 are merged cells with data 2000
Cell M7 is BLANK (no Values)
Cell N7 has value 2000
Cell O7 has value 400
Cell P7 has value 550
Cell Q7 has value 95

Values on Tabs 2, 3 and 4 present in cells H7 to Q7.

From the values posted above, neglect values 600, 1000, 750, 850 and 3200.

Hope this makes it easy for you.

Thanks.

Aacod

 

[cooper645]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

Aacod,

I have just merged my cells on sheets 1-4 in IJKL and the code still runs and calculates correctly.

Do you also want the 'Get Total' sheet to have IJKL merged?

Can you just clarify also that cell H5 has data 4500 is a typo, and should read H7.


I receive no subscript out of range errors either.
-please clarify your 5 sheet names (case sensitive) as this is most likely the cause for the error.

 

[aacod]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

Coops,

Sorry for that H5 is a typo, should be H7.

Name of Sheet 5 is 'Get Total'

Do you also want the 'Get Total' sheet to have IJKL merged? Yes please.

Thanks

 

[cooper645]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

Here is the code that works as you require, as long as we can avoid subscript out of range.

If you get subscript out of range, press debug and post back the highlighted line please

Sub Get_Totals3()

Dim LastRow As Long, i As Long


Range("H1:Q5").ClearContents


LastRow = Cells(Rows.Count, "H").End(xlUp).Row


For i = 8 To 17
    With Sheets("Get Total")
        .Cells(LastRow, i).Value = Sheets("BA").Cells(7, i).Value
        .Cells(LastRow + 1, i).Value = Sheets("DD").Cells(7, i).Value
        .Cells(LastRow + 2, i).Value = Sheets("JJ").Cells(7, i).Value
        .Cells(LastRow + 3, i).Value = Sheets("DP").Cells(7, i).Value
            With Sheets("Get Total").Cells(LastRow + 4, i)
                .FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
                .Font.Bold = True
            End With
    End With
Next i


Application.DisplayAlerts = False
For i = 1 To 5
Range(Cells(i, 9), Cells(i, 12)).Merge
Range(Cells(i, 9), Cells(i, 12)).HorizontalAlignment = xlCenter
Next i
Application.DisplayAlerts = True


With Range("G5")
    .Value = "Total"
    .Font.Bold = True
End With


End Sub

 

[aacod]

Re: EXCEL1: Need VBA code to copy values from 4 sheets to 5th sheet.

Coops,

Thanks, Works GREAT.

Just one more thing, what do I change to start pasting from cell H7 on sheet 5 and put borders around the pasted cells.

aacod