Problems to copy and paste from differente worksheets

L

Legacy 316950

Guest
Hello everybordy,

I am new on VBA and I am tryng to copy specifc values from Sheet2 to Sheet1, but i don't know how to concatenate this values and how to copy the right values.

My code copies but it's not working as i was expecting.

Here is my code.

Code: 
Sub copyvalues()

'Declaration of variables
Dim lastRowDestination  As Long
Dim lastrowSource       As Long
Dim wsDestination       As Worksheet
Dim wsSource            As Worksheet
Dim b                   As Range
Dim c                   As Range


'Setting worksheets
Set wsDestination = Sheets("Sheet1")
Set wsSource = Sheets("Sheet2")

'Counting the last row of wsSource worksheet
With ActiveWorkbook.Sheets("Sheet1").Activate
lastrowSource = Range("A" & Rows.Count).End(xlUp).Row
End With

'Counting the last row of wsDestination worksheet
With ActiveWorkbook.Sheets("Sheet2").Activate
lastRowDestination = Range("A" & Rows.Count).End(xlUp).Row
End With

'The Idea of this for is to hold one value of wsSource to compare with all values from wsSource, if the values of the
'are equal then I copy from the source and paste into the wsDestination.
With wsDestination
  .Activate
  For Each c In Range("A2:A" & lastRowDestination)
    Set b = wsSource.Columns(2)
        For Each b In Range("A2:A" & lastrowSource)
            If c = b Then
                .Cells(c.Row, 2).Value = wsSource.Cells(b.Row, 2).Offset(rowoffset:=1, columnoffset:=0).Value
            End If
        Next b
     
  Next c
End With

End Sub

This is my destination worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]What I have
[/TD]
[TD]What I was expecting
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 121"]
<colgroup><col width="121"></colgroup><tbody>[TR]
[TD="class: xl63, width: 121"]SPATMOENTR101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<colgroup><col width="141"></colgroup><tbody>[TR]
[TD="class: xl65, width: 141, align: right"]16/02/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]16,17
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 121"]
<colgroup><col width="121"></colgroup><tbody>[TR]
[TD="class: xl65, width: 121"]SPBTV2ENTR101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<colgroup><col width="141"></colgroup><tbody>[TR]
[TD="class: xl65, width: 141, align: right"]16/02/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<colgroup><col width="141"></colgroup><tbody>[TR]
[TD="class: xl65, width: 141"]3,10,12,15,17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 143"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Products
[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPATMOENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
[TR]
[TD]SPBTV2ENTR101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 142"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Days
[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16
[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My source worksheet that's where i'm copying from.































































































If anyone have an idea to give me i will be very grateful.



I use Windows Seven,Office 2010. here it's a link with my file .xlsm https://www.dropbox.com/s/mirzp11rk5ebad5/copy from other worksheet.xlsm?dl=0


Thanks :) and Hava a Nice Day!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Modify this line:
Code: 
.Cells(c.Row, 2).Value = wsSource.Cells(b.Row, 2).Offset(rowoffset:=1, columnoffset:=0).Value
\
To this
Code: 
.Cells(c.Row, 2).Value = .Cells(c.Row, 2).Value & ", " & wsSource.Cells(b.Row, 2).Offset(rowoffset:=1, columnoffset:=0).Value
You also will need to re-format your column 2 number format to General to get rid of the dates.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Code Issue
Replies
1
Views
175
RoryA
RoryA
M
  • Question Question
Split Excel Workbook to separate .xlsx files for each unique value in Column A, also copy worksheet for Drop Down reference
Replies
9
Views
411
Alex Blakenburg
A
Logit
  • Question Question
Search In Other Workbook - Copy/Paste Offset Data To First Workbook - Repeat Process
Replies
7
Views
262
Logit
Logit
U
  • Question Question
Information overwriting when transferring
Replies
7
Views
467
UTB
U
F
  • Question Question
VBA to copy and insert a row below only if a certain criteria is met
Replies
2
Views
214
feni1388
F

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top