How to copy a column from one worksheet to another if the Active sheet is different

[kas]

Hi,

I am sure my question is not new, I tried some of the suggestions given before on the Forum, but the codes do not seem to be working in my case. So I have to ask again.

I have a macro like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Column
Case Is = 4, 5, 6, 7
Call Macro2
Call Macro3
End Select
Application.EnableEvents = True
End Sub

Now I want to add something in it. So currently, after the above VBA code is run, the active sheet is 'Sheet 2'.

I would like to copy Column AD (or AD1:AD51 where AD1 has the name of the column and from AD2:AD51 are the numbers) in 'Sheet 2' to Column P in 'Sheet 1', and want 'Sheet 1' to be the active one. In other words, I want the macro to end in 'Sheet 1', so that I don't have to come back to 'Sheet 1' manually.

So I am not sure if to activate 'Sheet 1' first or to copy the column from 'Sheet 2' to 'Sheet 1' first, or whether copying from 'Sheet 2' to 'Sheet 1' will automatically activate 'Sheet 1' anyway.

After Application.EnableEvents-True command above I tried using:

Worksheets("Sheet 1").Range("P1").Value = Worksheets("Sheet 2").Range("AD1").Value

but this is not working. Should it be the other way round?

Any help will be greatly appreciated, I am quite stuck with this.

[vcoolio]

Hello Kas,

See if the following BeforeDoubleClick event works for you:-

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Application.ScreenUpdating = False

If Intersect(Target, Range("AD:AD")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

      Target.EntireColumn.Copy Sheet1.Range("P1")

Application.ScreenUpdating = True

Sheet1.Select

End Sub

Double click anywhere in Column AD, sheet2 to transfer the data to Column P in sheet1. You can call your other two macros by adding them to the code above.

I hope that this helps.

Cheerio,
vcoolio.

[kas]

Hello Vcoolio,

Thank you very much for your reply, and for the code.

I am not sure how to use this code with my existing code that I mentioned since I have to combine the two codes in one code, and I think that is where the problem is. I think separately, these codes work fine but not together.

Can you please help me with combining the two codes, the one that I am using and the one you suggested?

[kas]

Also, the name of Sheet1 is with spaces, something like 'efficiency few values'. The code you suggested is not excepting spaces between the Sheet name here:

Target.EntireColumn.Copy Sheet1.Range("P1")

I am really struggling with this, can anyone please help me that how I can transfer Column AD of Sheet2 to Column P of Sheet1, AFTER I have run the macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Column
Case Is = 4, 5, 6, 7
Call Macro2
Call Macro3
End Select
Application.EnableEvents = True

So after the above macro, I want to transfer Column AD from Sheet2 to Column P in Sheet1, I tried using the Range command but it is giving me an error.

[vcoolio]

Hello Kas,

I think that I may have misunderstood your first post. I now assume that you want to keep the code in your first post and then transfer Column AD data once the original code has run. If I have assumed correctly then, in a standard module, place the following code:-

Sub TransferColData()


Application.ScreenUpdating = False


Range("AD:AD").EntireColumn.Copy Sheet1.Range("P1")


Application.ScreenUpdating = True


Sheet1.Select


End Sub

Now, in your first code , place the name of the above code (TransferColData) just above "End Sub". The transfer will happen once your original code has run.
Where I have referenced Sheet1 in the code, you can change this to your actual sheet name as follows:-

Sheets("Whatever The Tab Name here")........

Better still, use the actual sheet code. So, for example, if the tab name is in Sheet4, then just use Sheet4 as the sheet reference in the code.

Also, as another option, before implementing the above, add these two lines of code:-

Range("AD:AD").EntireColumn.Copy Sheet1.Range("P1")

Sheet1.Select

just after "End Select" in your original code. It should do the task for you without introducing another module. Again, reference the sheet names as previously explained.


I hope that this helps.

Cheerio,
vcoolio.