Excel VBA use found column and row to paste data

Question

I want to send data from one workbook to another workbook (workbook A sends data to workbook B).

I made a code that opens workbook B and searches for values which are given in certain cells in workbook A.

The only thing that I can't make and need help with is to copy a range from workbook A (Range: G71 up to and including DI71) and paste this range in workbook B at the column and row that is found with this code

The code that I have up to now:

Private Sub CommandButton1_Click()
Dim Fstring As String
Dim Pstring As String
Dim Bureauplanning As String
Dim wb As Workbook
Dim cFind As Range
Dim rFind As Range
Dim rngc As Range
Dim rngp As Range

    'cell with data to find
    Fstring = Range("G13").Value
    Pstring = Range("A2").Value


Bureauplanning = "\\nel-data\Document\Planning\Bureauplanning.xlsm"
    Workbooks.Open (Bureauplanning)

With Sheets("Blad1").Range("G13:DI13")
Set rFind = .find(What:=Fstring, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
    MsgBox rFind.Column

End If
End With

With Sheets("Blad1").Range("F:F")
Set cFind = .find(What:=Pstring, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not cFind Is Nothing Then
    MsgBox cFind.Row

End If
End With

End Sub

The msgboxes just for checking if i get the good rows and columns.

I hope someone can help.

Could you precisely say what do you want to copy where if for example Fstring was found in I13 and Pstring in F17? And are the ranges of the Fstring and Pstring values on sheet Blad1? And the name of the worksheet in Bureauplanning (maybe ActiveSheet)? In which row does your data start in column F? Do you want to find the same value in range H13:DI13 as in G13 or are these different sheets? — VBasic2008
– VBasic2008, Commented Dec 14, 2018 at 14:46
If rFind is not Nothing, can you not just do this within your With statement: .Copy rFind.Address? — Zac
– Zac, Commented Dec 14, 2018 at 15:14
I cracked it, but it nearly was the death of me. It is unbelievable that after all this I wrote in the first comment, you actually posted (barely) enough info. Good luck with the rest of it. — VBasic2008
– VBasic2008, Commented Dec 14, 2018 at 16:35

GMalc · Accepted Answer · 2018-12-14 15:37:16Z

0

I don't know what your second workbook or worksheet is, so you will have to fill in the "?"s. I would also put the workbook you are copying from in front of Sheets("Blad1"). But, here is a basic copy paste using variables.

Sheets("Blad1").Range("G13:DI13").Copy Destination:=Workbook(?).Sheets(?).Cells(rFind, cFind)

answered Dec 14, 2018 at 15:37

GMalc

2,6381 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2018-12-14 16:23:40Z

0

The Copy Method

Just add this after your code:

With Sheets("Blad1")
    Range("G71:DI71").Copy .Cells(cFind.Row, rFind.Column) _
        .Resize(, .Range("G71:DI71").Columns.Count)
End With

If you want to check if a workbook is open, you can use this:

  Const wbName As String = "Bureauplanning.xls"
  Dim wb As Workbook

 ' Check if workbook is open.
  For Each wb In Workbooks
    If wb.Name = wbName Then Exit For
  Next
  If wb Is Nothing Then Set wb = Workbooks.Open(Bureauplanning)

edited Dec 14, 2018 at 16:23

answered Dec 14, 2018 at 16:09

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Collectives™ on Stack Overflow

Excel VBA use found column and row to paste data

2 Answers 2

Comments

The Copy Method

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

The Copy Method

Comments

Your Answer

Sign up or log in

Post as a guest

Related