PDA

View Full Version : [SOLVED] Switch Values Between Sheets



Jim Clayton
07-18-2018, 02:53 PM
I have two workbooks attached. They both have the same macro in them. In Book1, you click on any cell in Column G, press the "Swap" button, and it swaps the Drawing Descriptions (Columns E & H). In Book2, Column H is on Sheet2. I'm trying to adjust the macro so that it works the same way, but pulls the information from Sheet2. I have no clue how to do this. Could really use some help. It would be greatly appreciated. Tks.

Jim Clayton
07-18-2018, 02:54 PM
Here's the macro in it's current form. Tks.


Option Explicit

Sub RoundedRectangle3_Click()
' Swap
' only works if cell in column G is selected
If ActiveCell.Column <> 7 Then Exit Sub
Dim sTemp As String
sTemp = ActiveCell.Offset(0, -2).Value
ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(0, 1).Value = sTemp
End Sub

Paul_Hossler
07-18-2018, 05:42 PM
The first macro is based on yours to get the Sheet2 data, but the second is slightly simpler and doesn't require col G to be selected, just somewhere in the row -- your choice





Option Explicit
Sub RoundedRectangle3_Click_Orig()
Dim sTemp As String
' only works if cell in column G is selected
With ActiveCell
If .Column <> 7 Then Exit Sub
sTemp = .Offset(0, -2).Value
.Offset(0, -2).Value = Worksheets("Sheet2").Cells(.Row, 8).Value
Worksheets("Sheet2").Cells(.Row, 8).Value = sTemp
End With

End Sub





Sub RoundedRectangle3_Click()
Dim sTemp As String
With ActiveCell.EntireRow
sTemp = .Cells(5).Value
.Cells(5) = Worksheets("Sheet2").Cells(.Row, 8).Value
Worksheets("Sheet2").Cells(.Row, 8).Value = sTemp
End With

End Sub

Jim Clayton
07-18-2018, 06:35 PM
I appreciate all the help. Thanks.

Jim Clayton
08-06-2018, 11:34 AM
Hello Again. I was given a solution to the above thread of swapping data between sheets. Works great. I went to apply it to the sheet I'm working on and ran into issues. One of the forum members at excel help forum was able to provide with a solution to the problem. That does the job, BUT it's also throwing a Runtime error 1004/Unable to Get Match Properties. I'm wondering if there's a way to stop this from happening. I've attached two workbooks. The first one (Sample) has the original solution to the above thread. The second one (BuildMyBOMxxx) has the macro that is throwing the error (Sub GTX_Click). Would greatly appreciate if someone could help with this problem. Thanks.

Jim Clayton
08-06-2018, 12:59 PM
Disregard. solved. Tks.