Simple Copy/Paste Question

  • Thread starter Thread starter Dan R.
  • Start date Start date
D

Dan R.

This code simply copies a range from WB1 and pastes into into the
first empty column in WB2.Sheet2 then saves WB2, but right before WB2
is closed I need it to copy WB2.Sheet2.Cells(A1:J3) and paste it in
the same column that the other range was pasted. Can someone help me
with this please?

Here's my code:

Sub Copy()
Dim SourceRange As Range
Dim DestRange1 As Range
Dim DestRange2 As Range
Dim BANCol As Range
Dim LC1 As Integer
Dim LC2 As Integer
Dim WB1 As Worksheet
Dim WB2 As Workbook

Application.ScreenUpdating = False

Set WB1 = ActiveSheet
Set WB2 = Workbooks.Open("C:\Documents and Settings\Totals.xls")

LC1 = Lastcol(WB2.Sheets(2)) + 1
LC2 = Lastcol(WB2.Sheets(1))

Set SourceRange = WB1.Columns("A:K")
Set DestRange1 = WB2.Sheets(2).Columns(LC1)
Set DestRange2 = WB2.Sheets(1).Columns(LC2)

SourceRange.Copy DestRange1
DestRange2.Insert

Set BANCol = WB2.Sheets(2).Columns(LC1 + 4)

BANCol.EntireColumn.Delete

WB2.Close savechanges:=True
Application.ScreenUpdating = True

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

Thank You,
-- Dan
 
see the added line below.

--
regards,
Tom Ogilvy


Dan R. said:
This code simply copies a range from WB1 and pastes into into the
first empty column in WB2.Sheet2 then saves WB2, but right before WB2
is closed I need it to copy WB2.Sheet2.Cells(A1:J3) and paste it in
the same column that the other range was pasted. Can someone help me
with this please?

Here's my code:

Sub Copy()
Dim SourceRange As Range
Dim DestRange1 As Range
Dim DestRange2 As Range
Dim BANCol As Range
Dim LC1 As Integer
Dim LC2 As Integer
Dim WB1 As Worksheet
Dim WB2 As Workbook

Application.ScreenUpdating = False

Set WB1 = ActiveSheet
Set WB2 = Workbooks.Open("C:\Documents and Settings\Totals.xls")

LC1 = Lastcol(WB2.Sheets(2)) + 1
LC2 = Lastcol(WB2.Sheets(1))

Set SourceRange = WB1.Columns("A:K")
Set DestRange1 = WB2.Sheets(2).Columns(LC1)
Set DestRange2 = WB2.Sheets(1).Columns(LC2)

SourceRange.Copy DestRange1
DestRange2.Insert

Set BANCol = WB2.Sheets(2).Columns(LC1 + 4)

BANCol.EntireColumn.Delete
Click to expand...

' added line
WB2.Sheet2.Cells(A1:J3).copy wb2.Sheet2.Cells(1,lc1)
 
see the added line below.

--
regards,
Tom Ogilvy
















' added line
WB2.Sheet2.Cells(A1:J3).copy wb2.Sheet2.Cells(1,lc1)







- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...

Tom,

I'm getting Run-time Error '438': Object doesnt support this property
or method.

Thanks,
-- Dan
 
my typo:

WB2.Sheet2.Cells(A1:J3).copy wb2.Sheet2.Cells(1,lc1)

should be

WB2.Sheet2.Range("A1:J3").copy wb2.Sheet2.Cells(1,lc1)
 
My bad again

WB2.Sheet2.Cells(A1:J3).copy wb2.Sheet2.Cells(1,lc1)


should actually be

WB2.Sheets(2).Range("A1:J3").copy wb2.Sheets(2).Cells(1,lc1)


I copied your notation:
WB2.Sheet2.Cells(A1:J3)
Click to expand...

so I wouldn't mess it up, but it was already messed up and I didn't pay
attention.
 
My bad again

WB2.Sheet2.Cells(A1:J3).copy wb2.Sheet2.Cells(1,lc1)

should actually be

WB2.Sheets(2).Range("A1:J3").copy wb2.Sheets(2).Cells(1,lc1)

I copied your notation:


so I wouldn't mess it up, but it was already messed up and I didn't pay
attention.

--
Regards,
Tom Ogilvy






- Show quoted text -
Click to expand...

Third times a charm... Thanks a lot Tom it works perfectly.

-- Dan
 
Back
Top