VBA automatically adding up cells ish?

[Ramballah]

Hello i have a question
I have this code i wrote for copying my charts to save the information once im done with. heres the code:

Sub Range_Copy_Example()    Dim First_Chart As String, Second_Chart As String, Third_Chart As String
    Dim Fourth_Chart As String
    First_Chart = Range("B2")
    Second_Chart = Range("K2")
    Third_Chart = Range("T2")
    Fourth_Chart = Range("B104")
    'This copies a cellrange and removes it.
    If First_Chart = "" = False And Second_Chart = "" = True Then
        Range("A1:H101").Copy Range("J1:Q101")
        Range("B2:D101").ClearContents
        Range("F2:H101").ClearContents
    ElseIf First_Chart = "" = False And Third_Chart = "" = True Then
        Range("A1:H101").Copy Range("S1:Z101")
        Range("B2:D101").ClearContents
        Range("F2:H101").ClearContents
    End If
End Sub

As you can see it does its job i want him to do. However i need more than 50 charts and this only covers the first 2 basically... Is there a way that VBA automatically adds up the amount of cells he needs to go to the right / down to paste the next chart? So i dont need to write this code 48 more times?? if im not clear enough just ask im willing to drop my file for download.

 

[Ramballah]

And yes my rows and columns will always be the same as i just have a chart like that and put my information in myself. So it will always be the same design.

 

[Ramballah]

Sub Range_Copy_Example()    Dim cel As Range, rng1 As Range, rng2 As Range, rng3 As Range, d As Integer, i As Integer
    Set cel = Range("B2")
    Set rng1 = Range("A1:H101")
    Set rng2 = Range("B2:D101")
    Set rng3 = Range("F2:H101")
    'this was setting up all the ranges and terms
    
For d = 1 To 20 'Going downwards
    For i = 1 To 5 'Going to the right
        If cel = "" = False And cel.Offset(, 9 * i) = "" = True Then
            Range("A1:H101").Copy rng1.Offset(, 9 * i)
        ElseIf cel = "" = False And cel.Offset(d * 102) = "" = True Then
            Range("A1:H101").Copy rng1.Offset(d * 102)
        End If
        rng2.ClearContents
        rng3.ClearContents
    Next i
Next d


End Sub

This is what i've got now... trying to study ur help... But my problem i got now is. it wont go more than 1 time to the right and downwards. it just goes 1 time to the right. and then 1 time downwards. i need it to go 5 times to the right then 1 time downwards and then 5 times to the right again and 1 downwards etc. But i'm not sure what i did wrong here. care to help a bit someone xD?

 

[Yongle]

I need to understand what you are trying to achieve

With the first set of charts everything is relative to A1:H101, and the ranges to be cleared were B2:D101 and F2:F101
(this is the 5 charts that go across the workbook)

With the next set of charts (down the worksheet) which range is the eqivalent to A1:H101? and which ranges should be cleared?

 

[Ramballah]

in A1:H101 is the entire first chart. This includes all the lines, rownumberlabelings etc
in B2:D101 and F2:F101 are information that i put in my self that will change depending on what i put in. the rest are things that never change basically. thats why i want those ranges to be cleared with content only.
Now once my first chart is filled in. i want it to be copied to the right of it. the entire chart/table what ever u wanna call it. So it keeps it as a record basically. Now when i got another full chart i want it to be copied next to the chart i already copied so it will look like this:
A1:H101 is completely filled in and goes to J1:Q101.
A1:H101 is completely filled again and goes to S1:Z101 this time because J1:Q101 already has the previous chart.
A1:H101 is completely filled again and goes to the next range. untill i have 5 charts in a row (including the A1:H101 which is just a chart i fill my information in)
Then once the 4th chart is pasted. so we got this; My fill-in chart - Chart 1 - Chart 2 - Chart 3 - Chart 4
then i want it to go down one chart and then go again
so we got then still A1:H101 as my fill in chart
bt now it will be:
My fill in chart - Chart 1 - Chart 2 - Chart 3 - Chart 4
Chart 5 - Chart 6 - Chart 7 - Chart 8 - Chart 9 Now go down again
Chart 10 - Chart 11 - Chart 12 - Chart 13 - Chart 14 and go down again and keep this doing it.

I hope this cleared it up a bit?

 

[Yongle]

Chart 5 is the first chart after going down
Is this immediately below fill-in-chart? (ie in columns A:H)
Which is the first row?

 

[Ramballah]

Yes, if you look in my code it will say D * 102 which pasted it on the perfect thing. but chart 5 is directly below yes. because my chart is exactly 1 to 101 so on 103 comes the new chart.
because its like this
1. column labeling
2. first info
3. second info
~
101. 100th info
102 blank
103. new column labeling
104. first info
you get the point. Xd same with going to the right i leave 1 blank between the charts so u can see that there is a new one. so it will be
x x x

x x x

and not
xxx
xxx

 

[Yongle]

focus ONLY on the range to be copied and where is should be copied to

VBA below copies original range (A1:H101) 19 times to give you 5 X 4 = 20 including the original
This line prevents the data being copied onto itself

If d = 0 And i = 0 Then i = 1

Message string added to help you follow the sequence of what is happening within the loops

Sub CopyToCorrectRanges()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rng1 As Range, d As Integer, i As Integer
    Dim myStr As String
    Set rng1 = Range("A1:H101")

    For d = 0 To 3
        For i = 0 To 4
            If d = 0 And i = 0 Then i = 1
            rng1.Copy rng1.Offset(101 * d, 9 * i)
            myStr = myStr & vbCr & "down " & d & vbTab & "across " & i & vbTab & rng1.Offset(101 * d, 9 * i).Address(0, 0)
        Next i
    Next d

Application.Calculation = xlCalculationAutomatic
MsgBox myStr
End Sub

Do you now understand how offset works?
Is it copying to the correct place each time?

 

[Ramballah]

Can you explain a bit more in the code you posted now especially these lines:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

myStr = myStr & vbCr & "down " & d & vbTab & "across " & i & vbTab & rng1.Offset(101 * d, 9 * i).Address(0, 0)

Application.Calculation = xlCalculationAutomatic
MsgBox myStr

Because im not sure what they do / mean with my current knowledge of VBA. And uhm when i did my code it pasted in the correct places but it just didnt go 4 times to the right and then down. it just went 1 time to the right and then 1 time down and then nothing so yes i understand how offset works and it went in the correct places when i editted it in my own code. But i didnt test this new code from you yet.

 

[Yongle]

Make the code faster
Prevent the screen updating every time the code changes something

Application.ScreenUpdating = False

Prevent any calculations taking place whilst code is running

[COLOR=#333333]Application.Calculation = xlCalculationManual[/COLOR]

Create a helpful string that can be dumped to message box
- every time the code loops it adds a new line (with vbCr) to the old string (myStr) beore inserting the current values for d and r and the address of the copyTo range
& symbol is for concatenation of all the above and works similar in Excel strings
vbTab is purely for spacing

myStr = myStr & vbCr & "down " & d & vbTab & "across  " & i & vbTab & rng1.Offset(101 * d, 9 * i).Address(0, 0)

Put Excel back to automatic calculation mode
and run recalcs once to refresh every formula etc

Application.Calculation = xlCalculationAutomatic

Dump the above helpful string to a message box

MsgBox myStr

 

[Ramballah]

Wow this code works great, i just had to add the clearcontents and edit the offset a little bit. But its just 1 problem now. When i run this code it copies A1:H101 20 times at the same time giving 20 times the same chart. I just need it to copy it once. then i put in new information in A1:H101 chart and then it copies that information to the next place. so it goes 1 for 1 if you know what i mean xD? How do i edit the code so it will do that?

Sub CopyToCorrectRanges()    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rng1 As Range, d As Integer, i As Integer, rng2 As Range, rng3 As Range
    Dim myStr As String
    Set rng1 = Range("A1:H101")
    Set rng2 = Range("B2:D101")
    Set rng3 = Range("F2:H101")


    For d = 0 To 3
        For i = 0 To 4
            If d = 0 And i = 0 Then i = 1
            rng1.Copy rng1.Offset(102 * d, 9 * i)
            myStr = myStr & vbCr & "down " & d & vbTab & "across " & i & vbTab & rng1.Offset(102 * d, 9 * i).Address(0, 0)
            rng2.ClearContents
            rng3.ClearContents
        Next i
    Next d


Application.Calculation = xlCalculationAutomatic
MsgBox myStr
End Sub