Azur
02-24-2020, 05:45 PM
The current shown below works. The only problem I have to continually go into the code to change the values of the cells because the data in the worksheet A21 change daily.
I found a found a script on the Internet I am trying to modify to suit my need. But I am running into trouble as you can see in the attachment.
The range cells involved are the following:
Set rng = Worksheets("A21Cals").Range("b3:k7", "b10:k15")
Set tng = Worksheets("A21Cals").Range("b18:k26", "b29:k32")
On transferring (or pasting only) the data the range cells are:
Worksheets("A21Cals").Range("l3", "l10").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
Worksheets("A21Cals").Range("l19", "l27").Resize(tng.Rows.Count, tng.Columns.Count).Cells.Value = tng.Cells.Value
I would to make these range of cells dynamic so that as the data in A21 change the ranges adjust in rows and columns sizes to reflect to new data.
I just cannot figure out how to to do that.
Azur
Current script
Dim rng As Range
'Grab Some Data and Store it in a "Range" variable
Set rng = Worksheets("A21Cals").Range("b3:k7", "b10:k16")
Set tng = Worksheets("A21Cals").Range("b19:k24", "b27:k27")
'Transfer Values to same spot in another worksheet (Mimics PasteSpecial Values Only)
Worksheets("A21Cals").Range("l3", "l10").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
Cells.WrapText = False
SkipBlanks _
= False
Worksheets("A21Cals").Range("l19", "l27").Resize(tng.Rows.Count, tng.Columns.Count).Cells.Value = tng.Cells.Value
Cells.WrapText = False
SkipBlanks _
= False
'Call SelectBlanks
End Sub
I found a found a script on the Internet I am trying to modify to suit my need. But I am running into trouble as you can see in the attachment.
The range cells involved are the following:
Set rng = Worksheets("A21Cals").Range("b3:k7", "b10:k15")
Set tng = Worksheets("A21Cals").Range("b18:k26", "b29:k32")
On transferring (or pasting only) the data the range cells are:
Worksheets("A21Cals").Range("l3", "l10").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
Worksheets("A21Cals").Range("l19", "l27").Resize(tng.Rows.Count, tng.Columns.Count).Cells.Value = tng.Cells.Value
I would to make these range of cells dynamic so that as the data in A21 change the ranges adjust in rows and columns sizes to reflect to new data.
I just cannot figure out how to to do that.
Azur
Current script
Dim rng As Range
'Grab Some Data and Store it in a "Range" variable
Set rng = Worksheets("A21Cals").Range("b3:k7", "b10:k16")
Set tng = Worksheets("A21Cals").Range("b19:k24", "b27:k27")
'Transfer Values to same spot in another worksheet (Mimics PasteSpecial Values Only)
Worksheets("A21Cals").Range("l3", "l10").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
Cells.WrapText = False
SkipBlanks _
= False
Worksheets("A21Cals").Range("l19", "l27").Resize(tng.Rows.Count, tng.Columns.Count).Cells.Value = tng.Cells.Value
Cells.WrapText = False
SkipBlanks _
= False
'Call SelectBlanks
End Sub