Copying of multiple ranges but with offset both on master sheet and destination

[Whereabout]

Hi All,

Hope there is someone who can help and utter newbie in VBA coding.

I have a workbook with multiple sheets. The first sheet (PFD Copy) is my master sheet from which I want data to be pasted into other sheets. The following is the code I have written so far:

Sub Frommastertobuftarget()

For Each Cell In Sheets("PFD Copy").Range("A6:A34")
If Cell.Value = "1" Then
Sheets("PFD Copy").Range("G6").Copy (Sheets("Buffers target").Range("B4"))
Sheets("PFD Copy").Range("D6").Copy (Sheets("Buffers target").Range("B5"))
Sheets("PFD Copy").Range("G41:G44").Copy (Sheets("Buffers target").Range("A15:A18"))
Sheets("PFD Copy").Range("F41:F44").Copy (Sheets("Buffers target").Range("B15:B18"))
Sheets("PFD Copy").Range("H41:H44").Copy (Sheets("Buffers target").Range("E15:E18"))
Sheets("PFD Copy").Range("H45").Copy (Sheets("Buffers target").Range("E14"))
Sheets("PFD Copy").Range("I48").Copy (Sheets("Buffers target").Range("E23"))
Sheets("PFD Copy").Range("N6").Copy (Sheets("Buffers target").Range("F9"))
Sheets("PFD Copy").Range("I49:I51").Copy (Sheets("Buffers target").Range("C23:C25"))
End If
Next Cell
End sub

This example is doing what I want it to, but the trick is that I have 29 almost identical commands with the only difference being that data taken from "PFD Copy" and the destination changes so the next command looks like:

For Each Cell In Sheets("PFD Copy").Range("A6:A34")
If Cell.Value = "2" Then
Sheets("PFD Copy").Range("G7").Copy (Sheets("Buffers target").Range("B34"))
Sheets("PFD Copy").Range("D7").Copy (Sheets("Buffers target").Range("B35"))
Sheets("PFD Copy").Range("G57:G60").Copy (Sheets("Buffers target").Range("A45:A48"))
Sheets("PFD Copy").Range("F57:F60").Copy (Sheets("Buffers target").Range("B45:B48"))
Sheets("PFD Copy").Range("H57:H60").Copy (Sheets("Buffers target").Range("E45:E48"))
Sheets("PFD Copy").Range("H61").Copy (Sheets("Buffers target").Range("E44"))
Sheets("PFD Copy").Range("I64").Copy (Sheets("Buffers target").Range("E53"))
Sheets("PFD Copy").Range("N7").Copy (Sheets("Buffers target").Range("F39"))
Sheets("PFD Copy").Range("I65:I67").Copy (Sheets("Buffers target").Range("C53:C55"))
End If
Next Cell

So to my actual question. Can you make an offset based on my "IF" command? So when the value of my "IF" changes from 1 to 2, the ranges from the first command offsets with different values?

And is there a smarter way of writing the code so I don't have to write the same code 29 times

Hope you can help me and my question is understandable.

Thanks in advance.

 

[offthelip]

the wayto do this is to use the value in the cell to calculate the offset like this:

Sub test()
For Each cell In Sheets("PFD Copy").Range("A6:A34")
'If cell.Value = "1" Then
Sheets("PFD Copy").Range("G6").Copy (Sheets("Buffers target").Range("B4").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("D6").Copy (Sheets("Buffers target").Range("B5").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("G41:G44").Copy (Sheets("Buffers target").Range("A15:A18").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("F41:F44").Copy (Sheets("Buffers target").Range("B15:B18").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("H41:H44").Copy (Sheets("Buffers target").Range("E15:E18").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("H45").Copy (Sheets("Buffers target").Range("E14").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("I48").Copy (Sheets("Buffers target").Range("E23").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("N6").Copy (Sheets("Buffers target").Range("F9").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("I49:I51").Copy (Sheets("Buffers target").Range("C23:C25").Offset(0, 30 * (cell.Value - 1)))
'End If
Next cell
End Sub

This one sub does both of yours without the if statement at all

 

[offthelip]

actually I didn't get it quite right you need to offset the source too like this:

Sheets("PFD Copy").Range("G6").Offset( cell.Value-1,0).Copy (Sheets("Buffers target").Range("B4").Offset(0, 30 * (cell.Value - 1)))

you need to copy that down the rest of the "copy" statements multiplying as needed

 

[Whereabout]

the wayto do this is to use the value in the cell to calculate the offset like this:

Sub test()
For Each cell In Sheets("PFD Copy").Range("A6:A34")
'If cell.Value = "1" Then
Sheets("PFD Copy").Range("G6").Copy (Sheets("Buffers target").Range("B4").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("D6").Copy (Sheets("Buffers target").Range("B5").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("G41:G44").Copy (Sheets("Buffers target").Range("A15:A18").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("F41:F44").Copy (Sheets("Buffers target").Range("B15:B18").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("H41:H44").Copy (Sheets("Buffers target").Range("E15:E18").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("H45").Copy (Sheets("Buffers target").Range("E14").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("I48").Copy (Sheets("Buffers target").Range("E23").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("N6").Copy (Sheets("Buffers target").Range("F9").Offset(0, 30 * (cell.Value - 1)))
Sheets("PFD Copy").Range("I49:I51").Copy (Sheets("Buffers target").Range("C23:C25").Offset(0, 30 * (cell.Value - 1)))
'End If
Next cell
End Sub

This one sub does both of yours without the if statement at all

Ok nice. That seems easy enough. Will I be able to use the same offset code on the first part of the code, so the code that determines where to take the data from so it looks like this:

Sheets("PFD Copy").Range("G6").Offset(0, 1 * (cell.Value - 2))Copy (Sheets("Buffers target").Range("B4").Offset(0, 30 * (cell.Value - 2)))

?

 

[Whereabout]

Thanks offthelip.

But I still need to write/copy the code 29 times and change the offsets manually right?

Thanks a lot for your help

 

[offthelip]

But I still need to write/copy the code 29 times and change the offsets manually right?

I don't think so, ti really depends on what yuo are trying to do in the other 29 bits of code. The two example you gave fitted an exact pattern which means all the offsets for source and destination can be calculated from the cell.value. If that pattern is true for the other 27 cases then this code will work for all of them. However you only gave us two cases to work on so I dfon't know

 

[Whereabout]

I don't think so, ti really depends on what yuo are trying to do in the other 29 bits of code. The two example you gave fitted an exact pattern which means all the offsets for source and destination can be calculated from the cell.value. If that pattern is true for the other 27 cases then this code will work for all of them. However you only gave us two cases to work on so I dfon't know

ARH.... so the cell.value will multiply the offsets.... NICE. did not figure that out. That is awesome. Will try to incorporate it and then ill be back.

 

[Whereabout]

Hi again,

I tried the code and it works for the first block, but the second block will not be executed. The code is as following:

Sub test()
    For Each cell In Sheets("PFD Copy").Range("A6:A34")
        'If cell.Value = "1" Then
        Sheets("PFD Copy").Range("G6").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("B4").Offset(0, 30 * (cell.Value - 1)))
        Sheets("PFD Copy").Range("D6").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("B5").Offset(0, 30 * (cell.Value - 1)))
        Sheets("PFD Copy").Range("G41:G44").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("A15:A18").Offset(0, 30 * (cell.Value - 1)))
        Sheets("PFD Copy").Range("F41:F44").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("B15:B18").Offset(0, 30 * (cell.Value - 1)))
        Sheets("PFD Copy").Range("H41:H44").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("E15:E18").Offset(0, 30 * (cell.Value - 1)))
        Sheets("PFD Copy").Range("H45").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("E14").Offset(0, 30 * (cell.Value - 1)))
        Sheets("PFD Copy").Range("I48").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("E23").Offset(0, 30 * (cell.Value - 1)))
        Sheets("PFD Copy").Range("N6").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("F9").Offset(0, 30 * (cell.Value - 1)))
        Sheets("PFD Copy").Range("I49:I51").Offset(0, cell.Value - 1).Copy (Sheets("Buffers target").Range("C23:C25").Offset(0, 30 * (cell.Value - 1)))
        'End If
    Next cell

    For Each cell In Sheets("PFD Copy").Range("A6:A34")
        'If cell.Value = "2" Then
        Sheets("PFD Copy").Range("G6").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("B4").Offset(0, 30 * (cell.Value - 2)))
        Sheets("PFD Copy").Range("D6").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("B5").Offset(0, 30 * (cell.Value - 2)))
        Sheets("PFD Copy").Range("G41:G44").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("A15:A18").Offset(0, 30 * (cell.Value - 2)))
        Sheets("PFD Copy").Range("F41:F44").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("B15:B18").Offset(0, 30 * (cell.Value - 2)))
        Sheets("PFD Copy").Range("H41:H44").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("E15:E18").Offset(0, 30 * (cell.Value - 2)))
        Sheets("PFD Copy").Range("H45").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("E14").Offset(0, 30 * (cell.Value - 2)))
        Sheets("PFD Copy").Range("I48").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("E23").Offset(0, 30 * (cell.Value - 2)))
        Sheets("PFD Copy").Range("N6").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("F9").Offset(0, 30 * (cell.Value - 2)))
        Sheets("PFD Copy").Range("I49:I51").Offset(cell.Value - 2, 0).Copy (Sheets("Buffers target").Range("C23:C25").Offset(0, 30 * (cell.Value - 2)))
        'End If
    Next cell


End Sub

First off, I get a bug for section 2 when I try executing the code. Have no clue on what I have written wrong, tried to copy your edited suggestion.

It also seems like the multiplying code in the code above are written in 2 different ways. Can that work?

hope you can help me again.

 

[JW00]

Are you looking to populate from any occurrence of 1 or 2 in your range or just the first occurrence.

 

[Whereabout]

Are you looking to populate from any occurrence of 1 or 2 in your range or just the first occurrence.

Just first occurrence. It will essentially just be a list from 1-x stating how many blocks of code that I need to have. So each number in Cell.Value from 1-x will be a block of code listed in my example above.

does that make sense? sorry my native language is not English.