Macro to mirror x,y data

[bigcdaddy]

I am using a program called Winwedge to input X and Y data into Excel. I need a macro to mirror the data from the A and B column to the C and D column. I will be plotting this data with X,Y scatter. I will be doing this literally hundreds of times a day, hence the need for a macro. I am a bit of a novice so treat me as such. Thank you in advance.

[Alf]

If your sheet only contains information in column A and B it’s possible to use the ”Activesheet.UsedRange” command.

And a macro like this should do the job.

Sub CopyDiff()

ActiveSheet.UsedRange.Copy
    With ActiveSheet.UsedRange
         .Offset(0, 2).PasteSpecial Paste:=xlPasteAll
    End With

Application.CutCopyMode = False

End Sub

If there are more information on the sheet and data in column A and B are of different “length” then a macro like this would be better.

Sub CopyData()

Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Range("C1").PasteSpecial Paste:=xlAll

Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Copy
Range("D1").PasteSpecial Paste:=xlPasteAll

Application.CutCopyMode = False

End Sub

The last macro assumes your data starts in A1 / B1 if no so change A1 to A where data starts and also chang target C1 to C same as A.

To add macro to sheet right click on sheet tab -> Cilck on "View Code" this takes you to the VB editor. Top left find "Insert" click -> select "Module" and paste code into "Module"

To run select "View" tab and click on "Macros" (top right) or select tab "Developer" and click "Macros" (top left)

Alf

[bigcdaddy]

Thank you I will try these and see if I can make them work. I will let youknow how it goes.

[bigcdaddy]

That worked great!!! Now I have a new request. I have a column of data that I just copied from A2-A40 to C2-C40. I need to take the data that's in C2-C40 and subtract those numbers from .315". All inside that same macro.

Thanks in advance,

[Alf]

There are times when I realise English is not my native language and this is one of the times.

Do you wish to do a subtraction like C2 - 0.315 or 0.315 - C2? I also assume that the 0.315" stands for 0.315 inch but hopfully this do not need to be converted.

Next question will the range allways be C2-C40 or could that change?

Alf

[bigcdaddy]

The .315" is a constant number. It is measured in inches. It does not need to be converted.The data I have collected that is mirrored to C2-C40 is the varible data. I need a macro to subtract the varible data form the constant number and return that data in C2-C40. I will need to apply the same macro to E2-E40 (the collected data, the same as A2-A40) and do the subtract to G2-G40(the same as C2-C40). The C2 and the G2 will be the same as far as starting for the range. It could go past C40 but i Can manually do the ones that go past C40. I hope I didn't confuse you more. I can send my spreadsheet to you if you think it will help.

Thanks for all the help.

[Alf]

As I'm not shure what macro you use I'll just modify both.

Sub CopyDiff()
Dim cell As Range

ActiveSheet.UsedRange.Copy
    With ActiveSheet.UsedRange
         .Offset(0, 2).PasteSpecial Paste:=xlPasteAll
    End With

Application.CutCopyMode = False

For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)

cell.Value = 0.315 - cell.Value

Next cell

End Sub

and the second macro will look like this

Sub CopyData()
Dim cell As Range

Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Range("C1").PasteSpecial Paste:=xlAll

Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Copy
Range("D1").PasteSpecial Paste:=xlPasteAll

Application.CutCopyMode = False

For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)

cell.Value = 0.315 - cell.Value

Next cell

End Sub

What I'm a bit unsure about is that originaly you spoke about copying range A:B to C:D and now you are talking about range E2:E40 and G2:G40? Am I missing something?

There is no problem exstending the copy to range if you need to.

I've also set the range "C2:C" to be dynamic so macro will find the last value in the C column be it C30 or C150.

Alf

[bigcdaddy]

Alf you ae the man. The second macro worked great and did exactly what I needed. I just changed the values as needed and boom there it was.
Thank you Thank you Thank you!!!! That just made my life so much simplier.

[Alf]

Glad to be of help and thanks for feedback!

Alf

Ps If you liked my answer click on my star (bottom left) and add a comment.