selecting variable data form one sheet and pasting it in another

shewendavelaar

New Member
Joined
Feb 19, 2019
Messages
9
I'm new to VBA (and coding in general. I've been trying to copy a set of data (client data) from one sheet and pasting it in a specific row. And I want this to happen when the client number equals the client number on the first sheet.

This is the code that I've written so far. Can someone help me debug it?

Private Sub CommandButton1_Click()
Dim a As Integer
Dim b As Integer
Dim cl As Integer
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a
b = Worksheets("Sheet1").Cells(i, 7).Value
Worksheets("sheet2").Activate
With Worksheets("sheet2").Range("a1:a273")
Set c = .Find(b, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox "klantnummer is niet gevonden"
Do
cl = ActiveCell.Column
Worksheets("sheet2").Range(.Cells(cl, 2), .Cells(cl, 5)).Copy Worksheets("Sheet1").Range(.Cells(i, 12), .Cells(i, 14))
Loop While Not c Is Nothing
End If
End With
Next


End Sub
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
It is not clear from your description or the code what you are trying to do.
Describe with more detail.

If Not c Is Nothing Then
MsgBox "klantnummer is niet gevonden"
Presumably this should be : If c Is Nothing Then

The syntax for the Cells method is :
Cells(row number, column number) or Cells(row number, "column letter ")

 

shewendavelaar

New Member
Joined
Feb 19, 2019
Messages
9
What I'm trying to do is:

1. select number in sheet1
2. Check in sheet2 if number is present
3. Copy data from the next three columns on the row where the number is present
4. past copied data on the same row where the number is present
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
What I'm trying to do is:

1. select number in sheet1
2. Check in sheet2 if number is present
3. Copy data from the next three columns on the row where the number is present
4. past copied data on the same row where the number is present

1. select number in sheet1
In what cell (or cells) is the number?
2. Check in sheet2 if number is present
Present in A1:A273?
Can the number appear more than once? If yes, what should happen?
3. Copy data from the next three columns on the row where the number is present
4. past copied data on the same row where the number is present
Copy from Sheet2 (columns B:D) and paste to Sheet1 (columns L:N) ?
 

shewendavelaar

New Member
Joined
Feb 19, 2019
Messages
9

ADVERTISEMENT

1. the number is in cells (i (2), 7), meaning the row is a variable. The code has to be able to loop through a list of numbers.
2. yes the number is present in A1:A273 in sheet 2
3. The number can only appear once.

Thank you for your help :).
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Code:
Private Sub CommandButton1_Click()
Dim a As Integer, b As Integer, cl As Integer, c as Range
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To a
    b = Worksheets("Sheet1").Cells(i, 7).Value
    With Worksheets("sheet2")
        Set c = .Range("a1:a273").Find(b)
        If c Is Nothing Then
            MsgBox "klantnummer( " & b & " is niet gevonden"
            GoTo nxt
        End If
       .Cells(c.Row, 2).Resize(, 3).Copy Worksheets("Sheet1").Cells(i, 12)
    End With
nxt:
Next
End Sub
 
Last edited:

shewendavelaar

New Member
Joined
Feb 19, 2019
Messages
9

ADVERTISEMENT

This doesn't solve the problem unfortunately, I think the value of cell(i, 7) doesn't get stored in b.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
This doesn't solve the problem unfortunately, I think the value of cell(i, 7) doesn't get stored in b.
Step through the code via F8 (with the Locals window open in the VBE) to check what values are getting assigned.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
Are all the klantnummers integers (that is whole numbers no bigger than 32,767) ?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,158
Messages
5,527,137
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top