Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from 3rd worksheets and pasting to active worksheet
Hi
I need to run code that will copy data from cells B9:B1000 located on the third worksheet Before (to the left) the activeworksheet ...and then...paste the data into cells F9:F1000 of the Active worksheet. In case this helps....... The name of this third worksheet is "Details"..however, more sets of these worksheets can be added (via macro) to this template file and I will need to run this code from them as well.. for ex:.. when the second set of worksheets are added..the third worksheet before the activeworksheet that is running this code will be named Details (2) ...and so on..as more sets are added. I would use formulas to do this..but I need to be able to determine the last entry in column F of the active worksheet..and formulas count even if the results = nothing...and there could be data in cells B9:B20 or B9:B1000.. it just depends..on that data entered...so the formulas will interfear with this... Any help is greatly appreciated... Thanks in advance, Kimberly .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from 3rd worksheets and pasting to active worksheet
Assume the copies will be moving to the right starting with Column F as you
copy them: Dim sh as Worksheet Dim i as Long Dim sh1 as Worksheet set sh = Activesheet i = 6 ' column F for each sh1 in Worksheets if Instr(1,sh.Name,"detail",vbTextCompare) then sh1.Range("B9:B1000").Copy Destination:= _ sh.Cells(9,1) i = i + 1 End if Next -- Regards, Tom Ogilvy "KimberlyC" wrote in message ... Hi I need to run code that will copy data from cells B9:B1000 located on the third worksheet Before (to the left) the activeworksheet ...and then...paste the data into cells F9:F1000 of the Active worksheet. In case this helps....... The name of this third worksheet is "Details"..however, more sets of these worksheets can be added (via macro) to this template file and I will need to run this code from them as well.. for ex:.. when the second set of worksheets are added..the third worksheet before the activeworksheet that is running this code will be named Details (2) ...and so on..as more sets are added. I would use formulas to do this..but I need to be able to determine the last entry in column F of the active worksheet..and formulas count even if the results = nothing...and there could be data in cells B9:B20 or B9:B1000.. it just depends..on that data entered...so the formulas will interfear with this... Any help is greatly appreciated... Thanks in advance, Kimberly . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from 3rd worksheets and pasting to active worksheet
Typo:
Sub ABCD() Dim sh As Worksheet Dim i As Long Dim sh1 As Worksheet Set sh = ActiveSheet i = 6 ' column F For Each sh1 In Worksheets If InStr(1, sh1.Name, "detail", vbTextCompare) Then sh1.Range("B9:B1000").Copy Destination:= _ sh.Cells(9, i) i = i + 1 End If Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Assume the copies will be moving to the right starting with Column F as you copy them: Dim sh as Worksheet Dim i as Long Dim sh1 as Worksheet set sh = Activesheet i = 6 ' column F for each sh1 in Worksheets if Instr(1,sh.Name,"detail",vbTextCompare) then sh1.Range("B9:B1000").Copy Destination:= _ sh.Cells(9,1) i = i + 1 End if Next -- Regards, Tom Ogilvy "KimberlyC" wrote in message ... Hi I need to run code that will copy data from cells B9:B1000 located on the third worksheet Before (to the left) the activeworksheet ...and then...paste the data into cells F9:F1000 of the Active worksheet. In case this helps....... The name of this third worksheet is "Details"..however, more sets of these worksheets can be added (via macro) to this template file and I will need to run this code from them as well.. for ex:.. when the second set of worksheets are added..the third worksheet before the activeworksheet that is running this code will be named Details (2) ...and so on..as more sets are added. I would use formulas to do this..but I need to be able to determine the last entry in column F of the active worksheet..and formulas count even if the results = nothing...and there could be data in cells B9:B20 or B9:B1000.. it just depends..on that data entered...so the formulas will interfear with this... Any help is greatly appreciated... Thanks in advance, Kimberly . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from 3rd worksheets and pasting to active worksheet
Thanks!!
This is working....Except... When I add another set of the worksheets to the template, this code that now runs on the new active worksheet picks up column B (cells 9:1000) on the both Details (Details, and Details (2)) worksheets and paste the data into col F and G (respectively) on the new active worksheet. I only need the code to pick up the data on the the Details worksheet that is closest to this (counting 3 worksheets over to the left) activeworksheet.....and paste it into column F of this activeworksheet. The Details worksheets will always be three worksheets before the activeworksheet that I need to run this code on. The workbook has the following worksheets in it: Info, OP, DESC, Summary,Adjustments, Details, OW, OT, Deduction, MISC, SUB When the second set of worksheets are added to the workbook, it looks like this: Info, OP, DESC, Summary, Adjustments, Details, OW, OT, Deduction, Summary(2), Adjustments (2), Details (2), OW (2), OT (2), Deduction (2), MISC, SUB And.. more worksheet sets can be added...adding (3) to them. The code below is placed behind a command button on the "Deduction"/s worksheets and needs to only reference the "Details" worksheet that is the closest to it on the left side (which is 3 worksheets over to the left or before it). Not sure if all that info helps!! :) But.. any further help would be greatly appreciated!! Thanks in advance!! Kimberly "Tom Ogilvy" wrote in message ... Typo: Sub ABCD() Dim sh As Worksheet Dim i As Long Dim sh1 As Worksheet Set sh = ActiveSheet i = 6 ' column F For Each sh1 In Worksheets If InStr(1, sh1.Name, "detail", vbTextCompare) Then sh1.Range("B9:B1000").Copy Destination:= _ sh.Cells(9, i) i = i + 1 End If Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Assume the copies will be moving to the right starting with Column F as you copy them: Dim sh as Worksheet Dim i as Long Dim sh1 as Worksheet set sh = Activesheet i = 6 ' column F for each sh1 in Worksheets if Instr(1,sh.Name,"detail",vbTextCompare) then sh1.Range("B9:B1000").Copy Destination:= _ sh.Cells(9,1) i = i + 1 End if Next -- Regards, Tom Ogilvy "KimberlyC" wrote in message ... Hi I need to run code that will copy data from cells B9:B1000 located on the third worksheet Before (to the left) the activeworksheet ...and then...paste the data into cells F9:F1000 of the Active worksheet. In case this helps....... The name of this third worksheet is "Details"..however, more sets of these worksheets can be added (via macro) to this template file and I will need to run this code from them as well.. for ex:.. when the second set of worksheets are added..the third worksheet before the activeworksheet that is running this code will be named Details (2) ...and so on..as more sets are added. I would use formulas to do this..but I need to be able to determine the last entry in column F of the active worksheet..and formulas count even if the results = nothing...and there could be data in cells B9:B20 or B9:B1000.. it just depends..on that data entered...so the formulas will interfear with this... Any help is greatly appreciated... Thanks in advance, Kimberly . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from 3rd worksheets and pasting to active worksheet
Private Sub CommandButton1_Click()
me.previous.previous.previous.Range("B9:B1000").Co py _ Me.Range("F9") End Sub -- Regards, Tom Ogilvy "KimberlyC" wrote in message ... Thanks!! This is working....Except... When I add another set of the worksheets to the template, this code that now runs on the new active worksheet picks up column B (cells 9:1000) on the both Details (Details, and Details (2)) worksheets and paste the data into col F and G (respectively) on the new active worksheet. I only need the code to pick up the data on the the Details worksheet that is closest to this (counting 3 worksheets over to the left) activeworksheet.....and paste it into column F of this activeworksheet. The Details worksheets will always be three worksheets before the activeworksheet that I need to run this code on. The workbook has the following worksheets in it: Info, OP, DESC, Summary,Adjustments, Details, OW, OT, Deduction, MISC, SUB When the second set of worksheets are added to the workbook, it looks like this: Info, OP, DESC, Summary, Adjustments, Details, OW, OT, Deduction, Summary(2), Adjustments (2), Details (2), OW (2), OT (2), Deduction (2), MISC, SUB And.. more worksheet sets can be added...adding (3) to them. The code below is placed behind a command button on the "Deduction"/s worksheets and needs to only reference the "Details" worksheet that is the closest to it on the left side (which is 3 worksheets over to the left or before it). Not sure if all that info helps!! :) But.. any further help would be greatly appreciated!! Thanks in advance!! Kimberly "Tom Ogilvy" wrote in message ... Typo: Sub ABCD() Dim sh As Worksheet Dim i As Long Dim sh1 As Worksheet Set sh = ActiveSheet i = 6 ' column F For Each sh1 In Worksheets If InStr(1, sh1.Name, "detail", vbTextCompare) Then sh1.Range("B9:B1000").Copy Destination:= _ sh.Cells(9, i) i = i + 1 End If Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Assume the copies will be moving to the right starting with Column F as you copy them: Dim sh as Worksheet Dim i as Long Dim sh1 as Worksheet set sh = Activesheet i = 6 ' column F for each sh1 in Worksheets if Instr(1,sh.Name,"detail",vbTextCompare) then sh1.Range("B9:B1000").Copy Destination:= _ sh.Cells(9,1) i = i + 1 End if Next -- Regards, Tom Ogilvy "KimberlyC" wrote in message ... Hi I need to run code that will copy data from cells B9:B1000 located on the third worksheet Before (to the left) the activeworksheet ...and then...paste the data into cells F9:F1000 of the Active worksheet. In case this helps....... The name of this third worksheet is "Details"..however, more sets of these worksheets can be added (via macro) to this template file and I will need to run this code from them as well.. for ex:.. when the second set of worksheets are added..the third worksheet before the activeworksheet that is running this code will be named Details (2) ...and so on..as more sets are added. I would use formulas to do this..but I need to be able to determine the last entry in column F of the active worksheet..and formulas count even if the results = nothing...and there could be data in cells B9:B20 or B9:B1000.. it just depends..on that data entered...so the formulas will interfear with this... Any help is greatly appreciated... Thanks in advance, Kimberly . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy & pasting same cell from different worksheets | Links and Linking in Excel | |||
how to copy data from one worksheet to multiple worksheets at once | Excel Worksheet Functions | |||
Compare worksheets for duplicates and copy data to 1st worksheet | Excel Worksheet Functions | |||
Copy and pasting specific sheet data to a master worksheet | Excel Programming | |||
Copy & Pasting Worksheets | Excel Discussion (Misc queries) |