Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bigdaddy3
 
Posts: n/a
Default copy and paste code problem

can somone tell me why the attached code will not work it works for the first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3
  #2   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

if I understand correctly multicple slection cannot be copied andpasted
only contiguous cells can be copied like this
so one by one copy
e.g. worksheets("staffdetails").range("I4:I10").copy
worksheets("payslip").range("B2").pastespecial ETC ETC
worksheets("staffdetails").range("c4").copy
worksheets("payslip").range("C11").pastespecial ETC ETC

simlarly other non contiguous cells.

try this


"bigdaddy3" wrote in message
...
can somone tell me why the attached code will not work it works for the

first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3



  #3   Report Post  
Duke Carey
 
Posts: n/a
Default

Do you really mean WORKBOOKS(Staff Details")? or should that be WORKSHEETS?

Maybe this will work for you
Private Sub Workbook_Open()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = Worksheets("Staff Details")
Set wsSlips = Worksheets("Payslips")

With wsDetails
.Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

.Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

.Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

.Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub


"bigdaddy3" wrote:

can somone tell me why the attached code will not work it works for the first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3

  #4   Report Post  
bigdaddy3
 
Posts: n/a
Default

Hi Duke, it was workbooks named Staff details and what about the last point i
mentioned about clearing the outline when finished,Thanks for your reply
--
BD3


"Duke Carey" wrote:

Do you really mean WORKBOOKS(Staff Details")? or should that be WORKSHEETS?

Maybe this will work for you
Private Sub Workbook_Open()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = Worksheets("Staff Details")
Set wsSlips = Worksheets("Payslips")

With wsDetails
.Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

.Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

.Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

.Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub


"bigdaddy3" wrote:

can somone tell me why the attached code will not work it works for the first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3

  #5   Report Post  
Duke Carey
 
Posts: n/a
Default

Application.CutCopyMode = False

will get rid of the range border that indicates you have copied that range

The code you had in your original post will copy data from whatever sheet in
the Staff Details workbook is active when you open the workbook containing
the code. It'd be better to set a worksheet variable to the specific sheet
you want to use as the source, as my sample code did

"bigdaddy3" wrote:

Hi Duke, it was workbooks named Staff details and what about the last point i
mentioned about clearing the outline when finished,Thanks for your reply
--
BD3


"Duke Carey" wrote:

Do you really mean WORKBOOKS(Staff Details")? or should that be WORKSHEETS?

Maybe this will work for you
Private Sub Workbook_Open()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = Worksheets("Staff Details")
Set wsSlips = Worksheets("Payslips")

With wsDetails
.Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

.Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

.Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

.Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub


"bigdaddy3" wrote:

can somone tell me why the attached code will not work it works for the first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3



  #6   Report Post  
bigdaddy3
 
Posts: n/a
Default

Duke, thanks for that ill try it
--
BD3


"Duke Carey" wrote:

Application.CutCopyMode = False

will get rid of the range border that indicates you have copied that range

The code you had in your original post will copy data from whatever sheet in
the Staff Details workbook is active when you open the workbook containing
the code. It'd be better to set a worksheet variable to the specific sheet
you want to use as the source, as my sample code did

"bigdaddy3" wrote:

Hi Duke, it was workbooks named Staff details and what about the last point i
mentioned about clearing the outline when finished,Thanks for your reply
--
BD3


"Duke Carey" wrote:

Do you really mean WORKBOOKS(Staff Details")? or should that be WORKSHEETS?

Maybe this will work for you
Private Sub Workbook_Open()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = Worksheets("Staff Details")
Set wsSlips = Worksheets("Payslips")

With wsDetails
.Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

.Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

.Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

.Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub


"bigdaddy3" wrote:

can somone tell me why the attached code will not work it works for the first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3

  #7   Report Post  
bigdaddy3
 
Posts: n/a
Default

Hi Duke, i tried that but it doesnt work it brings up runtime error 9
subscript out of range ,but the 2 workbooks in question 1 is open and the
other payslip is opened by a button calling payslip thats when your code
copies and pastes but as i say it doesnt any toughts
--
BD3


"Duke Carey" wrote:

Application.CutCopyMode = False

will get rid of the range border that indicates you have copied that range

The code you had in your original post will copy data from whatever sheet in
the Staff Details workbook is active when you open the workbook containing
the code. It'd be better to set a worksheet variable to the specific sheet
you want to use as the source, as my sample code did

"bigdaddy3" wrote:

Hi Duke, it was workbooks named Staff details and what about the last point i
mentioned about clearing the outline when finished,Thanks for your reply
--
BD3


"Duke Carey" wrote:

Do you really mean WORKBOOKS(Staff Details")? or should that be WORKSHEETS?

Maybe this will work for you
Private Sub Workbook_Open()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = Worksheets("Staff Details")
Set wsSlips = Worksheets("Payslips")

With wsDetails
.Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

.Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

.Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

.Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub


"bigdaddy3" wrote:

can somone tell me why the attached code will not work it works for the first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3

  #8   Report Post  
Duke Carey
 
Posts: n/a
Default

What did you try? It isn't at all clear.

Perhaps posting a description of what you are trying to accomplish, along
with the code that doesn't work, would allow somebody to help you without so
much guesswork involved.

"bigdaddy3" wrote:

Hi Duke, i tried that but it doesnt work it brings up runtime error 9
subscript out of range ,but the 2 workbooks in question 1 is open and the
other payslip is opened by a button calling payslip thats when your code
copies and pastes but as i say it doesnt any toughts
--
BD3


"Duke Carey" wrote:

Application.CutCopyMode = False

will get rid of the range border that indicates you have copied that range

The code you had in your original post will copy data from whatever sheet in
the Staff Details workbook is active when you open the workbook containing
the code. It'd be better to set a worksheet variable to the specific sheet
you want to use as the source, as my sample code did

"bigdaddy3" wrote:

Hi Duke, it was workbooks named Staff details and what about the last point i
mentioned about clearing the outline when finished,Thanks for your reply
--
BD3


"Duke Carey" wrote:

Do you really mean WORKBOOKS(Staff Details")? or should that be WORKSHEETS?

Maybe this will work for you
Private Sub Workbook_Open()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = Worksheets("Staff Details")
Set wsSlips = Worksheets("Payslips")

With wsDetails
.Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

.Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

.Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

.Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub


"bigdaddy3" wrote:

can somone tell me why the attached code will not work it works for the first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3

  #9   Report Post  
bigdaddy3
 
Posts: n/a
Default

Hi Duke,hows this i have a workbook open (staff Details) on that there are
various buttons 1 of which calls up a workbook (Wages) with an active
worksheet (Payslip) onto which i need to copy certain details from the
original workbook (Staff Details). the ranges to be copied from are (I4:I10)
("C4") ("C9") ("G9") they then have to be pasted to("B2:B8", "C11", "K11",
"K12") in that order they are all seperate items,does that make more sense
--
BD3
Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub



"Duke Carey" wrote:

What did you try? It isn't at all clear.

Perhaps posting a description of what you are trying to accomplish, along
with the code that doesn't work, would allow somebody to help you without so
much guesswork involved.

"bigdaddy3" wrote:

Hi Duke, i tried that but it doesnt work it brings up runtime error 9
subscript out of range ,but the 2 workbooks in question 1 is open and the
other payslip is opened by a button calling payslip thats when your code
copies and pastes but as i say it doesnt any toughts
--
BD3


"Duke Carey" wrote:

Application.CutCopyMode = False

will get rid of the range border that indicates you have copied that range

The code you had in your original post will copy data from whatever sheet in
the Staff Details workbook is active when you open the workbook containing
the code. It'd be better to set a worksheet variable to the specific sheet
you want to use as the source, as my sample code did

"bigdaddy3" wrote:

Hi Duke, it was workbooks named Staff details and what about the last point i
mentioned about clearing the outline when finished,Thanks for your reply
--
BD3


"Duke Carey" wrote:

Do you really mean WORKBOOKS(Staff Details")? or should that be WORKSHEETS?

Maybe this will work for you
Private Sub Workbook_Open()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = Worksheets("Staff Details")
Set wsSlips = Worksheets("Payslips")

With wsDetails
.Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

.Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

.Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

.Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub


"bigdaddy3" wrote:

can somone tell me why the attached code will not work it works for the first
range only but when others added it fails also how do you clear outline of
copied cells, i used range ("D9").Select but no good.Could someone help
please

-- Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub
BD3

  #10   Report Post  
Duke Carey
 
Posts: n/a
Default

Put this in your Staff Details workbook & attach it to a button that you
click on after you've opened the wages.xls file

Sub CopyPayslipData()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = activesheet
Set wsSlips = workbooks("Wages.xls").Worksheets("Payslips")

With wsDetails
..Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

..Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

..Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

..Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub

"bigdaddy3" wrote:

Hi Duke,hows this i have a workbook open (staff Details) on that there are
various buttons 1 of which calls up a workbook (Wages) with an active
worksheet (Payslip) onto which i need to copy certain details from the
original workbook (Staff Details). the ranges to be copied from are (I4:I10)
("C4") ("C9") ("G9") they then have to be pasted to("B2:B8", "C11", "K11",
"K12") in that order they are all seperate items,does that make more sense
--
BD3
Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub



  #11   Report Post  
bigdaddy3
 
Posts: n/a
Default

Hi Duke,since i last spoke i have used the attached code nd it all copied
perfect when the payslip workbook opened but it seemed to blink 4 times am i
on the right track as i would rather not use another button but could i use
your code with a macro in the open event or can my code be simplified as far
as just one event


Workbooks("Staff Details").Activate
Range("I4:I10").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Workbooks("Staff Details").Activate
Range("C4").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("C11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Workbooks("Staff Details").Activate
Range("C9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("K11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Workbooks("Staff Details").Activate
Range("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

--
BD3


"Duke Carey" wrote:

Put this in your Staff Details workbook & attach it to a button that you
click on after you've opened the wages.xls file

Sub CopyPayslipData()
Dim wsDetails As Worksheet
Dim wsSlips As Worksheet

Set wsDetails = activesheet
Set wsSlips = workbooks("Wages.xls").Worksheets("Payslips")

With wsDetails
.Range("I4:I10").Copy
wsSlips.Range("B2:B8").PasteSpecial xlPasteValues

.Range("C4").Copy
wsSlips.Range("C11").PasteSpecial xlPasteValues

.Range("C9").Copy
wsSlips.Range("K11").PasteSpecial xlPasteValues

.Range("G9").Copy
wsSlips.Range("K12").PasteSpecial xlPasteValues

End With

End Sub

"bigdaddy3" wrote:

Hi Duke,hows this i have a workbook open (staff Details) on that there are
various buttons 1 of which calls up a workbook (Wages) with an active
worksheet (Payslip) onto which i need to copy certain details from the
original workbook (Staff Details). the ranges to be copied from are (I4:I10)
("C4") ("C9") ("G9") they then have to be pasted to("B2:B8", "C11", "K11",
"K12") in that order they are all seperate items,does that make more sense
--
BD3
Private Sub Workbook_Open()
Workbooks("Staff Details").Activate
Range("I4:I10")("C4")("C9")("G9").Select
Selection.Copy
Worksheets("Payslip").Activate
Range("B2:B8", "C11", "K11", "K12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("D9").Select
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Can't Copy and Paste or Paste Special between Excel Workbooks wllee Excel Discussion (Misc queries) 5 April 29th 23 03:43 AM
copy and paste using code from workbook to workbook bigdaddy3 Excel Discussion (Misc queries) 2 September 14th 05 11:06 AM
copy paste nowfal Excel Discussion (Misc queries) 1 September 3rd 05 01:30 AM
I cannot paste from one workbook to another. Copy works, paste do. JimmyMc Excel Discussion (Misc queries) 1 June 10th 05 03:54 PM
Can't Copy and Paste between Excel 2003 Workbooks wllee Excel Discussion (Misc queries) 6 March 30th 05 02:59 PM


All times are GMT +1. The time now is 12:06 PM.

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"