VBA: copy & paste values from one worksheet to another.

[inddon]

Hello There,

I would like to have the VBA to do the following:

1. Copy the values from a worksheet 'Sheet 2' column (which is fixed) with the start point (i.e start row) is fixed. Find the last row which has a value. And then from the start point to the end point copy it.

2. Paste it in another worksheet 'Sheet 1' to two different columns at the same time.

I have attached the sample workbook for your reference.

Appreciate your help.

regards
Don

 

[Asheesh]

Hi Don - Check if the below helps..

Sub test()
'Change sheet names as per your requirement
Dim l_Row As Long
Sheets("Sheet2").Activate
l_Row = Cells(Rows.Count, "C").End(xlUp).Row
Range(Range("C2"), Range("C" & l_Row)).Copy
Sheets("Sheet1").Activate
Range("E2, H2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = 0
End Sub

 

[inddon]

Hi Don - Check if the below helps..

Sub test()
'Change sheet names as per your requirement
Dim l_Row As Long
Sheets("Sheet2").Activate
l_Row = Cells(Rows.Count, "C").End(xlUp).Row
Range(Range("C2"), Range("C" & l_Row)).Copy
Sheets("Sheet1").Activate
Range("E2, H2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = 0
End Sub

Hi Asheesh,

At the end the code select 2 rows and does not paste the values.

Regards
Don

 

[Asheesh]

Hi Don- Check the attached..click on the button...

 

[inddon]

Hi Don- Check the attached..click on the button...

Thanks Asheesh, it works wonderful (1 like)

Another question: If I want to copy and paste in blocks.
The maximum value in Sheet2 is '5'. So to make a loop

for x in 1 to 5
copy all values for 1, then loop and copy 2's, then 3's, 4's and last 5

Could you advice how this can be achieved?

regards
Don

 

[Asheesh]

Will your data always have blank cell as separator between two different values..

 

[inddon]

Will your data always have blank cell as separator between two different values..

Yes there will always be a blank separator

regards
Don

 

[Smallman]

Hi Inddon

Will the data be mixed?

1
1
2
2
5

2
2
4
3
1

etc

Can you udate your file and show what it really looks like. I have an idea but need to see some real data.

Take care

Smallman

 

[inddon]

Hi Inddon

Will the data be mixed?

1
1
2
2
5

2
2
4
3
1

etc

Can you udate your file and show what it really looks like. I have an idea but need to see some real data.

Take care

Smallman

Hello Smallman,

The data in the worksheet 'Sheet2' will always have a blank row group separator and will be not be mixed. It should copy & paste and would be nice to clear as well in Sheet 1

eg.
1
1
1

2
2
2

3
3
3
3

4
4
4
4

5
5


attaached is the sample file (solution from Asheesh)

Thanks & regards
Don

 

[Smallman]

Hi Don

If your data is always the same in each group why do you need a loop?

Why not just take what was suggested and do everything without a loop. I can't see why you would want to treat the cells independently.

Take care

Smallman

 

[inddon]

Hi Don

If your data is always the same in each group why do you need a loop?

Why not just take what was suggested and do everything without a loop. I can't see why you would want to treat the cells independently.

Take care

Smallman

Hello Smallman,

There is some kind of a check before the copy to Sheet1 is done.

Eg. if there are 4 rows of value 1's in the sheet2 and there are 5 rows allocated for value 1's in the Sheet1, then there is a mismatch of the rows for group 1.

That is why before copying a check is done, if the number of rows from sheet2 (for individual groups) is equal to the number of rows allocated to it in Sheet1 (for individual groups).


1. Is to first count the max number of value in Sheet1 (i.e. 5)
2. Loop through the max number
3. Some kind of check and validation will be done by some other person (as explained above). If it is equal then go to step 4
4. Paste the copied group values

The same goes for clearing the values in Sheet1. Here there will be no checks.

Regards
Don