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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,388
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,388
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,388
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,388
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,388
Office Version
  1. 2016
Platform
  1. Windows
Are all the klantnummers integers (that is whole numbers no bigger than 32,767) ?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,713
Messages
5,833,269
Members
430,200
Latest member
ADLHMA2022

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