VBA Copy

[andy_2610]

Hello all,

I want to create a sheet to copy another sheet. Sheet1 will be auto-populated with data. And on sheet2 will have a vba code to copy sheet1 Column D only. I do not want it to catch changes from sheet1. For this reason, I am trying to catch any changes made on sheet1 with sheet2.

If more info is needed please let me know.

Thanks in advance,

Andrew

 

[michaelsmith559]

Try this code:

Sub Copy_ColumnD()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("D:D")
'Change Range("A1") below to whatever range you need
Worksheets("Sheet2").Range("A1").Resize(rng.Rows.Count, rng.Columns.Count). _
    Cells.Value = rng.Cells.Value
End Sub

 

[andy_2610]

I am not quite sure how this works. I copied and pasted into "Sheet1" and "Sheet2" and started entering numbers to test it and nothing is copying over onto "Sheet2" from "Sheet1"

Try this code:

Sub Copy_ColumnD()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("D:D")
'Change Range("A1") below to whatever range you need
Worksheets("Sheet2").Range("A1").Resize(rng.Rows.Count, rng.Columns.Count). _
    Cells.Value = rng.Cells.Value
End Sub

 

[michaelsmith559]

Here is sheet 1 with values in column d:

Excel 2012
	A	B	C	D	E	F
1				1
2				2
3				3
4				4
5				5
6				6
7				7
8				8
9				9
10
Sheet1

Here is the macro:

Sub Copy_ColumnD()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("D:D")
'Change Range("A1") below to whatever range you need
Worksheets("Sheet2").Range("A1").Resize(rng.Rows.Count, rng.Columns.Count). _
    Cells.Value = rng.Cells.Value
End Sub

Here is the results on sheet 2 (I pasted column d sheet 1 values into sheet 2 column a):

Excel 2012
	A	B	C	D	E	F
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10
11
12
Sheet2

 

[andy_2610]

I am quite not sure what I am doing wrong then. On Sheet1, I clicked view code and pasted your code in there. And then I went on to place numbers in Column D as you did. I checked Sheet2 and it is blank. The name of my sheets are exactly the same, well they are the defaulted names.

Here is sheet 1 with values in column d:
Excel 2012

	A	B	C	D	E	F
1				1
2				2
3				3
4				4
5				5
6				6
7				7
8				8
9				9
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Here is the macro:

Sub Copy_ColumnD()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("D:D")
'Change Range("A1") below to whatever range you need
Worksheets("Sheet2").Range("A1").Resize(rng.Rows.Count, rng.Columns.Count). _
    Cells.Value = rng.Cells.Value
End Sub

Here is the results on sheet 2 (I pasted column d sheet 1 values into sheet 2 column a):
Excel 2012

	A	B	C	D	E	F
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10
11
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

 

[michaelsmith559]

I want to make sure I understand what you are wanting to do. Are you wanting to copy column d from sheet 1 and paste it into sheet 2? If so, the above macro should work. You need to open the vbe editor, click on insert, new module, and paste the macro there. Then close the editor. Bring up the macros and click on Copy_ColumnD , run. It should paste column d sheet 1 into sheet 2 column a.

 

[andy_2610]

Oh okay, so I have to create a button for it. It's partially what I want. Now, I want it only copy new data. Say cell on sheet1 cell D1 is "5" and on sheet2 cell A1 is "5". 2 hours later the on sheet1 cell D1 has changed to "2" and on sheet2 cell A1 should remain "5".

This is for me to catch any adjustments made later on. I hope this makes sense.

I want to make sure I understand what you are wanting to do. Are you wanting to copy column d from sheet 1 and paste it into sheet 2? If so, the above macro should work. You need to open the vbe editor, click on insert, new module, and paste the macro there. Then close the editor. Bring up the macros and click on Copy_ColumnD , run. It should paste column d sheet 1 into sheet 2 column a.

 

[michaelsmith559]

Whatever values are on sheet 1, when you run the macro, will be the values on sheet 2. To get new values onto sheet 2 and keep the old values that are in sheet 2 column a, you will have to change the Worksheets("Sheet2").Range("A1") to .Range("B1"). I am assuming you are wanting to keep old values to compare to newer values?

 

[michaelsmith559]

Are you wanting the the whole process automated, such that, when sheet 1 column d values change the macro automatically runs and puts the new values into the next available column on sheet 2? If this is what you are after, I could only do part of it. The macro would have to run by using some type of change event. I am not sure how to do those, yet. Other people on here can.

 

[michaelsmith559]

Here is an untested macro I wrote. You will probably have to have someone here check the macro to see if it correct. I have not used an event to trigger a macro before, so test on a new worksheet. This should automate what you are trying to do.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, c As Long
Set rng = Worksheets("Sheet1").Range("D:D")

If Not Intersect(Target, Range("D:D")) Is Nothing Then
Sheets("Sheet2").Activate

If IsEmpty(Sheets("Sheet2").Range("IV1").End(xlToLeft).Value) = True Then
Worksheets("Sheet2").Range("IV1").End(xlToLeft).Resize(rng.Rows.Count, rng.Columns.Count). _
    Cells.Value = rng.Cells.Value
Else
Worksheets("Sheet2").Range("IV1").End(xlToLeft).Offset(0, 1).Resize(rng.Rows.Count, rng.Columns.Count). _
    Cells.Value = rng.Cells.Value
End If

Sheets("Sheet1").Activate
Range("D1").Select

Application.EnableEvents = False
End Sub