Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simple Copy/Paste Question
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 220
Default Simple Copy/Paste Question
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Simple Copy/Paste Question
see the added line below.

--
regards,
Tom Ogilvy


"Dan R." wrote:

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

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

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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 220
Default Simple Copy/Paste Question
On Jan 31, 11:03 am, Tom Ogilvy
wrote:
see the added line below.

--
regards,
Tom Ogilvy





"Dan R." wrote:
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

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



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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Tom,

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

Thanks,
-- Dan

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Simple Copy/Paste Question
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)

--
Regards,
Tom Ogilvy


"Dan R." wrote:

On Jan 31, 11:03 am, Tom Ogilvy
wrote:
see the added line below.

--
regards,
Tom Ogilvy





"Dan R." wrote:
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

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



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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Tom,

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

Thanks,
-- Dan


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Simple Copy/Paste Question
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)

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

--
Regards,
Tom Ogilvy


"Dan R." wrote:

On Jan 31, 11:03 am, Tom Ogilvy
wrote:
see the added line below.

--
regards,
Tom Ogilvy





"Dan R." wrote:
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

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



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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Tom,

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

Thanks,
-- Dan


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 220
Default Simple Copy/Paste Question
On Jan 31, 11:39 am, Tom Ogilvy
wrote:
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)

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

--
Regards,
Tom Ogilvy



"Dan R." wrote:
On Jan 31, 11:03 am, Tom Ogilvy
wrote:
see the added line below.

--
regards,
Tom Ogilvy

"Dan R." wrote:
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

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

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Tom,

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

Thanks,
-- Dan- Hide quoted text -

- Show quoted text -

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

-- Dan

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Line copy - Not just a simple copy paste Ninerref Excel Worksheet Functions 3 September 10th 09 11:44 AM
Simple Paste Question shawn Excel Programming 2 September 23rd 04 02:40 PM
Simple Copy & Paste Bob Barnes[_3_] Excel Programming 3 February 20th 04 11:09 PM
Simple copy paste question Z[_2_] Excel Programming 3 February 5th 04 09:03 PM
Simple copy and paste Fritz[_2_] Excel Programming 2 November 22nd 03 01:56 PM


All times are GMT +1. The time now is 10:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement