Hi Ray
Untested, Try this one
Sub copy_4()
Dim sourceRange As Range
Dim destrange As Range
Dim Lc As Integer
Dim WB1 As Workbook
Dim WB2 As Workbook
Set WB1 = ActiveWorkbook
Set WB2 = Workbooks("ron.xls")
Lc = Lastcol(WB2.Sheets(1)) + 1
If WB1.Sheets(1).Range("A2") = WB2.Sheets(1).Cells(2, Lc - 1) Then
MsgBox "Values are the same"
Else
Set sourceRange = WB1.Sheets(1).Columns("A:A")
Set destrange = WB2.Sheets(1).Columns(Lc)
sourceRange.Copy destrange
End If
End Sub
Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Ray" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hello again ....
>
> While further developing my Workbook, I came across a scenario that I'd
> like to avoid as it would cause fairly major issues if it occurred....
>
> BEFORE copying ColA from WB1 to WB2, I need to insert code that
> compares the value in Cell A2 (in WB1) with the value in Row2 of the
> last column in WB2. If the values are the same, I'd like a msgbox to
> popup declaring the issue and quitting the process.
>
> I'm quite sure I need to use the Offset properties here, but the code
> to identify & compare the two values is quite confusing for a newbie
> like me.
>
> TIA...
> //ray
>
>
>
> Ray wrote:
>> Hi Ron -
>>
>> Thanks for the code .... it works perfectly and I think I can actually
>> follow what it's doing!
>>
>> Rgds, Ray
>>
>>
>>
>> Ron de Bruin wrote:
>> > Try this
>> >
>> > Copy column A from the activeworkbook to the workbook ron.xls (note: this file must be open)
>> >
>> > Sub copy_4()
>> > Dim sourceRange As Range
>> > Dim destrange As Range
>> > Dim Lc As Integer
>> > Dim WB1 As Workbook
>> > Dim WB2 As Workbook
>> > Set WB1 = ActiveWorkbook
>> > Set WB2 = Workbooks("ron.xls")
>> >
>> > Lc = Lastcol(WB2.Sheets(1)) + 1
>> > Set sourceRange = WB1.Sheets(1).Columns("A:A")
>> > Set destrange = WB2.Sheets(1).Columns(Lc)
>> > sourceRange.Copy destrange
>> > End Sub
>> >
>> > Function Lastcol(sh As Worksheet)
>> > On Error Resume Next
>> > Lastcol = sh.Cells.Find(What:="*", _
>> > After:=sh.Range("A1"), _
>> > Lookat:=xlPart, _
>> > LookIn:=xlFormulas, _
>> > SearchOrder:=xlByColumns, _
>> > SearchDirection:=xlPrevious, _
>> > MatchCase:=False).Column
>> > On Error GoTo 0
>> > End Function
>> >
>> >
>> > --
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> > "Ray" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>> > >I need help before I go crazy .... I've spent the last 2hrs searching
>> > > for code that I could modify to do what I need (and found plenty of
>> > > examples), and it seems simple enough, but i just CANNOT make it work
>> > > for my workbook...Here's my scenario:
>> > >
>> > > I'd like to copy the first column from WB1 to the first empty column in
>> > > WB2 (both books have only one sheet). See what I mean, sounds simple
>> > > .... but I can't figure it out....
>> > >
>> > > TIA,
>> > > Ray
>> > >
>
Similar Threads
