The code shown below gives me the correct result that I am looking for when executing out of a commandbutton located on the same worksheet:
it returns this:
However, when I use the same code in a module and execute it that way, I get this as the result (where it copies the first name in A1 and pastes that same name down to the last row instead of capturing each unique name in each cell down to the last row...
Here is the module code for that (I have tried different iterations of the code below, but whatever I try, I just get the same result with only capturing the same first name that appears in A1:
Oh,and I did have to change up the code slightly by identifying the range with the full-name/location:
so instead of:
as it appears (and works) in the commandbutton code, I had to change that to:
to keep from getting an error.
Thanks for any suggestions
VBA Code:
' take the names previously copied-and-pasted and reverse the first name wiht the last:
Range("B1").Select
ActiveCell.FormulaR1C1 = _
"=MID(RC[-1]&"" ""&RC[-1],FIND("" "",RC[-1]),LEN(RC[-1]))"
'takes the copied strings in B1, trim, and then put the result in C1
Range("C1").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
'takes the previous 2 formulas and copies-and-pastes them down the the last row that contains a name:
Range("B1:C1").Select
Selection.AutoFill Destination:=Range(Cells(1, 2), Cells(wCol, 3)), Type:=xlFillDefault
'take the contents from the first row (in column B) and down to the bottom row (column C):
Range(Cells(1, 2), Cells(wCol, 3)).Select
ActiveWindow.SmallScroll Down:=-174
'******
Range(Cells(1, 3), Cells(wCol, 3)).Select 'copies the selection:
Selection.Copy
'pastes the copied data from the above code and pastes it starting at "D1" as VALUES:
ActiveWindow.SmallScroll Down:=-189
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
it returns this:
However, when I use the same code in a module and execute it that way, I get this as the result (where it copies the first name in A1 and pastes that same name down to the last row instead of capturing each unique name in each cell down to the last row...
Here is the module code for that (I have tried different iterations of the code below, but whatever I try, I just get the same result with only capturing the same first name that appears in A1:
VBA Code:
' take the names previously copied-and-pasted and reverse the first name wiht the last:
ActiveWorkbook.Worksheets("SwitchNames").Range(Cells(1, 2), Cells(wCol, 2)).Select
ActiveCell.FormulaR1C1 = _
"=MID(RC[-1]&"" ""&RC[-1],FIND("" "",RC[-1]),LEN(RC[-1]))"
'takes the copied strings in B1, trim, and then put the result in C1
ActiveWorkbook.Worksheets("SwitchNames").Range(Cells(1, 3), Cells(wCol, 3)).Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
'takes the previous 2 formulas and copies-and-pastes them down the the last row that contains a name:
ActiveWorkbook.Worksheets("SwitchNames").Range("B1:C1").Select
Selection.AutoFill Destination:=Range(Cells(1, 2), Cells(wCol, 3)), Type:=xlFillDefault
'take the contents from the first row (in column B) and down to the bottom row (column C):
ActiveWorkbook.Worksheets("SwitchNames").Range(Cells(1, 2), Cells(wCol, 3)).Select
ActiveWindow.SmallScroll Down:=-174
'******
ActiveWorkbook.Worksheets("SwitchNames").Range(Cells(1, 3), Cells(wCol, 3)).Select 'copies the selection:
Selection.Copy
'pastes the copied data from the above code and pastes it starting at "D1" as VALUES:
ActiveWindow.SmallScroll Down:=-189
ActiveWorkbook.Worksheets("SwitchNames").Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Oh,and I did have to change up the code slightly by identifying the range with the full-name/location:
so instead of:
VBA Code:
Range("B1").Select
as it appears (and works) in the commandbutton code, I had to change that to:
VBA Code:
ActiveWorkbook.Worksheets("SwitchNames").Range(Cells(1, 2), Cells(wCol, 2)).Select
to keep from getting an error.
Thanks for any suggestions