Mirror Columns accross worksheet

[dangerd]

Hi there,

I want column A in sheet 2 to mirror column A in sheet 1 using VBA.

Anybody?

Thanks,

Duncs

 

[Gord Dibben]

"Mirror" generally means left to right reversal.

I.e. 4321 is a mirror of 1234

Is that what you want?


Gord Dibben MS Excel MVP

 

[JLatham]

Insert the following code into a regular VB module, change the 2 sheet names
defined in it as required. Run when desired:

Sub MirrorColumnA()
Const sourceSheetName = "Sheet1" ' change as needed
Dim sourceSheet As Worksheet
Const destinationSheetName = "Sheet2" ' change as needed
Dim destSheet As Worksheet
Dim anyRangeAddress As String
Dim sourceRange As Range
Dim destRange As Range

Set sourceSheet = Worksheets(sourceSheetName)
Set destSheet = Worksheets(destinationSheetName)
anyRangeAddress = "A1:A" & _
sourceSheet.Range("A" & Rows.Count).End(xlUp).Row
Set sourceRange = sourceSheet.Range(anyRangeAddress)
Set destRange = destSheet.Range(anyRangeAddress)
destRange.Value = sourceRange.Value
Set destRange = Nothing
Set destSheet = Nothing
Set sourceRange = Nothing
Set sourceSheet = Nothing
End Sub

 

[JLatham]

Good point, Gord. I hadn't thought of it that way - I was thinking more on
the lines of 'echo' - and after further reflection (no pun intended), I also
realize that the method I offered only mirrors the values, not any formatting
that may have been applied to the original information.

 

[dangerd]

Thanks guys,

I was only after a simple 'echoing' the values between column, not
inverting them.

i.e. column A in sheet two to be EXACTLY the same as column A in sheet
one...which is what i think your code does.

Duncs

 

[JLatham]

It does echo it - as noted in my reply to Gord, it doesn't 'echo' any
formatting you have applied to the cells on the source sheet. If you need
that, we can change the code to use .Copy and .PasteSpecial to capture it all.

 

[Gord Dibben]

Thanks for clearing up the "mirror" for me.

Jerry's code does replicate the data.

You could just as easily copy Column A from sheet1 and "paste link" to column A
of sheet2.


Gord