Copy Cell To Another Workbook Sheet

[DCBUS]

Hello,
I am using the below Macro to copy Colomn "C" in sheet1 to Column "D" in sheet 2. I was wondering if it is possible instead of having predetermined copy and paste columns, that I can specify when the Macro is run. Meaning when I run the Macro, I get a dialog box that pops up that allows me to enter the sheet1 column for copy and once I enter that column, the next box pops up for me to enter the sheet2 destination column?

Option Explicit

Sub CtoD()
Dim wsOne As Worksheet, wsTwo As Worksheet
Dim arrOne, arrTwo, j As Long, jj As Long
Dim lrOne As Long, lrTwo As Long
Set wsOne = Worksheets("Sheet1")
Set wsTwo = Worksheets("Sheet2")


lrOne = wsOne.Cells(Rows.Count, "A").End(xlUp).Row
lrTwo = wsTwo.Cells(Rows.Count, "A").End(xlUp).Row


arrOne = wsOne.Range("A1:C" & lrOne).Value2
arrTwo = wsTwo.Range("A1:D" & lrTwo).Value2


For j = LBound(arrOne) To UBound(arrOne)
        For jj = LBound(arrTwo) To UBound(arrTwo)
            If arrTwo(jj, 1) = arrOne(j, 1) Then
                arrTwo(jj, 4) = arrOne(j, 3)
            End If
        Next jj
Next j
wsTwo.Range("A1").Resize(UBound(arrTwo), 4).Value = arrTwo


End Sub

Thanks Jr

 

[offthelip]

something like this should do the trick,:

Sub CtoD()

Dim wsOne As Worksheet, wsTwo As Worksheet
Dim arrOne, arrTwo, j As Long, jj As Long
Dim lrOne As Long, lrTwo As Long
Set wsOne = Worksheets("Sheet1")
Set wsTwo = Worksheets("Sheet2")
Scol = InputBox("type in the source column letter code")


Scol = UCase(Scol)
Dcol = InputBox("type in the destination column letter code")


Dcol = UCase(Dcol)




lrOne = wsOne.Cells(Rows.Count, Scol).End(xlUp).Row
lrTwo = wsTwo.Cells(Rows.Count, Dcol).End(xlUp).Row




arrOne = wsOne.Range(Scol & "1:" & Scol & lrOne).Value2
arrTwo = wsTwo.Range(Dcol & "1:" & Dcol & lrTwo).Value2




For j = LBound(arrOne) To UBound(arrOne)
        For jj = LBound(arrTwo) To UBound(arrTwo)
            If arrTwo(jj, 1) = arrOne(j, 1) Then
                arrTwo(jj, 4) = arrOne(j, 3)
            End If
        Next jj
Next j
wsTwo.Range("A1").Resize(UBound(arrTwo), 4).Value = arrTwo




End Sub

Note I never use option explicit so I haven't declared the variables but they are all obvious

 

[DCBUS]

Hello,

Thank you for the macro. I tried it and the boxes come up and I enter the rows, but then I get a error "Type Mismatch".

Thank you,
JR

 

[offthelip]

Which line does it come up with the message? and what values are there in the variables, use the locals window to see what is what.
It worked fine on my machine!!

 

[DCBUS]

Hello
Thank you again for your time. The error is not giving me a line for the error. So as a test, I have a workbook open with sheet1 and sheet2. In sheet1, I have column "A" with numeric 1-10 and sheet2 empty. When I run the macro, the first box comes up with and I enter "A" being the source column. Box two opens and I enter "C" for being the destination column. At that point I get the "Type Mismatch".

Thank you,
JR

 

[offthelip]

the error is because this line:

[COLOR=#333333]lrTwo = wsTwo.Cells(Rows.Count, Dcol).End(xlUp).Row[/COLOR]

Detects the last row used in sheet2, if sheet 2 is empty then lrtwo will be one, which will cause and error
This is exactly what your original code did. just to check my analysis is correct put some data into the column you have select as the destination

 

[DCBUS]

Hello,

That allowed for sheet2 to get data, but below are the images to what was completed. By adding data to sheet2, the macro did work, but it added "3" to columns A-D. On the original macro I listed, it was hard coated sheet1 C to sheet2 D. But on sheet2 D, this column could be blank, as the data from sheet1 A would just copy over.

I really do appreciate your help on this for me.

Sheet1 Data

https://www.dropbox.com/s/enwrakb9f0mjuyt/sheet1.JPG?dl=0

Sheet2 Data
https://www.dropbox.com/s/py4wpz6n8ejhvz2/sheet2.JPG?dl=0

Output
https://www.dropbox.com/s/7h3nqlppbbej177/final.JPG?dl=0

Thank you

 

[offthelip]

I was getting the macro to do exactly what it did before but using the imput variables
I was just showing you how to input the columns? What do you want the macro to do??

 

[DCBUS]

Hello,
Thank you again. What I need it to do is take what ever data is in sheet1 column, (source) specified by the popup box, and copy to sheet2 column, (destination) specified by the pop box. Sheet2 column may be completely blank with no current data. The original macro I listed took whatever data that was in sheet1 column "C", and copied to sheet2 Column "D", no matter is "D" had data or no data in the column. The only thing I wanted to change was instead of having the the macro have predetermined copy from being sheet1 "C" to sheet2 "D", have the choice of column for sourca and destination like you pope up boxes.

Thank you,
JR

 

[offthelip]

very simple, try this:

Sub CtoD()

Dim wsOne As Worksheet, wsTwo As Worksheet
Dim arrOne, arrTwo
Dim lrOne As Long
Set wsOne = Worksheets("Sheet1")
Set wsTwo = Worksheets("Sheet2")
Scol = InputBox("type in the source column letter code")
Scol = UCase(Scol)
Dcol = InputBox("type in the destination column letter code")
Dcol = UCase(Dcol)
lrOne = wsOne.Cells(Rows.Count, Scol).End(xlUp).Row
arrOne = wsOne.Range(Scol & "1:" & Scol & lrOne).Value2
wsTwo.Range(Dcol & "1:" & Dcol & lrOne).Value2 = arrOne


End Sub