Using Range Find Offset together with Range as variable

[Jodie3]

Hi Guys,

Not much clue what I'm talking about here being brand new to VBA and hope I'm following the forum rules appropriately.

I've set up a test scenario where I'm copying data from one workbook and pasting it to a particular range on another workbook. I can get it to work fine, but I'm trying to reduce the number of lines of code I have to maintain should something change in the destination sheet, using variables.

So the format of all sheets on "wbto" are the same, I was hoping I could set the range regardless of sheet to sit as a variable and then reference that in my destination.

Any hints and tips? Like I say the code works if I make "FindRng" on action 1 the same as action 2 and 3, I'm just badly attempting to make it more manageable in the future.

TIA

Sub COPY()

Dim Rng As Range
Dim FindRng As Range

Dim wbfrom As Workbook
Dim wbto As Workbook



Set wbfrom = Workbooks("Test from.xlsx")
Set wbto = Workbooks("Test to.xlsx")
Set Rng = wbfrom.Worksheets("Sheet1").Range("d3")
Set FindRng = Range("C6:G6")


wbfrom.Worksheets("Sheet1").Range("E7:E10").COPY
wbto.Worksheets("Sheet1").FindRng.Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues

wbfrom.Worksheets("Sheet1").Range("E7:E10").COPY
wbto.Worksheets("Sheet2").Range("C6:G6").Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues

wbfrom.Worksheets("Sheet1").Range("E7:E10").COPY
wbto.Worksheets("Sheet3").Range("C6:G6").Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues



End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Jodie()
   Dim Rng As Range
   Dim FindAddr As String
   Dim wbfrom As Workbook
   Dim wbto As Workbook
   Dim CopyRws As Long

   Set wbfrom = Workbooks("Test from.xlsx")
   Set wbto = Workbooks("Test to.xlsx")
   Set Rng = wbfrom.Worksheets("Sheet1").Range("d3")
   FindRng = "C6:G6"

   With wbfrom.Worksheets("Sheet1").Range("E7:E10")
      CopyRws = .Rows.Count
      wbto.Worksheets("Sheet1").Range(FindAddr).Find(Rng).Offset(5, 0).Resize(CopyRws).Value = .Value
      wbto.Worksheets("Sheet2").Range(FindAddr).Find(Rng).Offset(5, 0).Resize(CopyRws).Value = .Value
      wbto.Worksheets("Sheet3").Range(FindAddr).Find(Rng).Offset(5, 0).Resize(CopyRws).Value = .Value
   End With
End Sub

Do not use VBA keywords (such as Copy) for the names of variables or procedures, as it can cause problems.

 

[Jodie3]

Hi Fluff,

Thank you for your speedy response. It's at this point that I sincerely apologise for not stating that I didn't want the same source data copying on to all three tabs (I appreciate that's what my test code says, but my real life example gets different bits of data).

I see your point on the Keyword tip.

Thank you.

 

[Fluff]

Will you always be just copying a single column?

 

[Jodie3]

The copying ranges are static in my real life code but are different Columns/Rows. I've edited my test code to be more akin to my real life version because I'm struggling to explain clearly.

Thank you!

Sub COPY()

Dim Rng As Range
Dim FindRng As Range

Dim wbfrom As Workbook
Dim wbto As Workbook



Set wbfrom = Workbooks("Test from.xlsx")
Set wbto = Workbooks("Test to.xlsx")
Set Rng = wbfrom.Worksheets("Sheet1").Range("d3")
Set FindRng = Range("C6:G6")


wbfrom.Worksheets("Sheet1").Range("F20:F26").COPY
wbto.Worksheets("Sheet1").FindRng.Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues

wbfrom.Worksheets("Sheet1").Range("E7:E10").COPY
wbto.Worksheets("Sheet2").Range("C6:G6").Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues

wbfrom.Worksheets("Sheet1").Range("A15:A19").COPY
wbto.Worksheets("Sheet3").Range("C6:G6").Find(Rng).Offset(5, 0).PasteSpecial Paste:=xlPasteValues



End Sub

 

[Fluff]

Ok, how about

Sub Jodie()
   Dim Rng As Range
   Dim FindAddr As String
   Dim wbfrom As Workbook
   Dim wbto As Workbook

   Set wbfrom = Workbooks("Test from.xlsx")
   Set wbto = Workbooks("Test to.xlsx")
   Set Rng = wbfrom.Worksheets("Sheet1").Range("d3")
   FindRng = "C6:G6"

   With wbfrom.Worksheets("Sheet1")
      With .Range("F20:F26")
         wbto.Worksheets("Sheet1").Range(FindAddr).Find(Rng).Offset(5, 0).Resize(.Rows.Count).Value = .Value
      End With
      With .Range("E7:E10")
         wbto.Worksheets("Sheet2").Range(FindAddr).Find(Rng).Offset(5, 0).Resize(.Rows.Count).Value = .Value
      End With
      With .Range("A15:A19")
         wbto.Worksheets("Sheet3").Range(FindAddr).Find(Rng).Offset(5, 0).Resize(.Rows.Count).Value = .Value
      End With
   End With
End Sub

 

[Jodie3]

It's failing on the below (sorry for the screen shot) saying Run-time error "1004": Application-defined or Object-defined error?

 

[Fluff]

Oops this FindRng = "C6:G6" should be FindAddr = "C6:G6"

 

[Jodie3]

Bingo!!!!!!! Utter star thank you! Off to investigate the use of "With" now as I don't understand it lol

 

[Fluff]

You're welcome & thanks for the feedback.