Using Range Find Offset together with Range as variable

Jodie3

New Member
Joined
Aug 11, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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



VBA Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Aug 11, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Will you always be just copying a single column?
 

Jodie3

New Member
Joined
Aug 11, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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!

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

New Member
Joined
Aug 11, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

1597160563306.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Oops this FindRng = "C6:G6" should be FindAddr = "C6:G6"
 

Jodie3

New Member
Joined
Aug 11, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Bingo!!!!!!! Utter star thank you! Off to investigate the use of "With" now as I don't understand it lol
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,204
Messages
5,623,354
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top