How to reproduce an area of cells on another worksheets automatically?-CodePudding

I want to know how to automatically copy an area of cells of Worksheet 1 to Worksheet 2

Explanation :

Worksheet 1 is the proposition phase, we only put the product, localization, and price.

Worksheet 2 is the contract phase where the customers sign, so there is all Worksheet 1 information plus the legal information.

Everything between the orange line (13) and the last line where there is written “TOTAL H.T” need to be reproduced to the contract.

So you see that there is the second page made for this.

My idea:

I wanted to make a simple Copy and PasteSpecial xlPasteFormats with the designated area, but the problem is that every contract is different.

Now I need to find a way to select everything between A13:I13 and the line where there is “TOTAL H.T” written.

CodePudding user response：

Im guessing that you mean there is a variable number of rows between 13 and "TOTAL H.T"

Anyway, heres the code:

Sub Copy_Paste()

    Dim sht1 As Worksheet: Set sht1 = ThisWorkbook.Worksheets("Sheet1")
    Dim sht2 As Worksheet: Set sht2 = ThisWorkbook.Worksheets("Sheet2")
    
    Dim last_row As Integer: last_row = sht1.Cells(sht1.Rows.Count, 3).End(xlUp).Row
    Dim rng As Range: Set rng = sht1.Range(sht1.Cells(13, 1), sht1.Cells(last_row, 9))
    
    rng.Copy (sht2.Range("I1"))

End Sub

CodePudding user response：

This is the answer :

Sub Copy_Paste()

    Dim sht1 As Worksheet: Set sht1 = ThisWorkbook.Worksheets("Sheet1")
    Dim sht2 As Worksheet: Set sht2 = ThisWorkbook.Worksheets("Sheet2")
    
    Dim last_row As Integer: last_row = sht1.Cells(sht1.Rows.Count, 3).End(xlUp).Row
    Dim rng As Range: Set rng = sht1.Range(sht1.Cells(13, 1), sht1.Cells(last_row, 9))
    
    rng.Copy
    sht2.Range("I52").PasteSpecial xlPasteValues
    sht2.Range("I52").PasteSpecial xlPasteFormats


End Sub

So there is the same format !

Thanks to the team.