VBA code to copy and paste a range of cells in mutliple sheets

  • I want the copy and paste (ideally paste values) to apply to specific ranges (the copy and paste ranges are not the same) and to certain sheets only in the workbook. The loop works well in other macro's, I suspect the the copy and paste is the problem. Any assistance would be appreciated, thank you.


    Sub Copy_Paste()
    Dim b As Worksheet
    For Each b In ThisWorkbook.Worksheets
    If b.Name <> "Setup" And b.Name <> "Soil Report" And b.Name <> "Soil Test" And b.Visible Then
    range("B10:E36").Value = range("A10:D36").Value
    End If
    Next b
    End Sub
  • I think you need to specify b.range, as below, to cycle through the worksheets.

    Code
    1. Sub Copy_Paste()
    2. Dim b As Worksheet
    3. For Each b In ThisWorkbook.Worksheets
    4. If b.Name <> "Setup" And b.Name <> "Soil Report" And b.Name <> "Soil Test" And b.Visible Then
    5. b.Range("F10:I36").Value = b.Range("A10:D36").Value
    6. End If
    7. Next b
    8. End Sub
  • C&J


    Please take the time to read our Forum Rules and in future use Code Tags when posting to the Forum.


    Quote


    the copy and paste ranges are not the same


    What does this mean? Your code seems to working with two differnt ranges


    Code
    1. range("B10:E36").Value = range("A10:D36").Value


    Your ranges are overlapping. An example workbook would help.


    Also, a Select case would be much more suited than an If Statement, something like this


  • Thanks Justin and Roy. I tried both suggestions and they produced the same result, I ended up with cleared cells on the paste?


    Roy, 'copy and paste ranges not the same', I mean while I want to copy and paste the same range I want to paste to a different location. Perhaps I need to incorporate Offset.


    I've attached the now and needed (this is a cut down version so the ranges won't match the code).

  • Hi C&J,


    Both codes appear to work fine on the example you gave, I copied your example sheet to the other two sheets then ran as below:


    And both give the same result with no missing data, although it was confusing as you only had one column filled in your Now-copy range so I copied the rest from your needed copy range first. All three sheets resulted as below.


    Is there something else you are trying to achieve that I am missing?

  • Justin, sorry about the one column filled, I did make it more confusing. You are correct the code (both) are spot on. My mistake, I've been reading the first range as Copy... second as Paste. Swapped those around, no problems. One more question, what if I wanted to Cut instead of Copy? Thanks

  • like this?

  • No, surprisingly, didn't do anything. So I went with:


    ws.range("E5:E11").Value = ws.range("Z5:Z11").Value


    Affectively cleared contents


    Nothing will occur out at column Z


    Thanks

  • Why not use Cut?