| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cell value from one worksheet to matching worksheet in another workbook
I have 2 workbooks: OLD and NEW. They each have over 100 sheets
(most sheets are the same but some are different). I want to set the value of cell C7 in the NEW workbook to the value from cell G40 in the OLD workbook. I'd like open both workbooks then run a macro that runs through every sheet in NEW and captures the sheet name then matches it to the same sheet in the OLD workbook, copies the value from G40, then pastes it into the NEW workbook C7. Thanks so much for any help!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy cell value from one worksheet to matching worksheet in anotherworkbook
Option Explicit
Sub testme() Dim OldWkbk As Workbook Dim NewWkbk As Workbook Dim oWS As Worksheet Dim nWS As Worksheet Set OldWkbk = Workbooks("youroldworkbooknamehere.xls") Set NewWkbk = Workbooks("yournewworkbooknamehere.xls") For Each oWS In OldWkbk.Worksheets Set nWS = Nothing On Error Resume Next Set nWS = NewWkbk.Worksheets(oWS.Name) On Error GoTo 0 If nWS Is Nothing Then MsgBox oWS.Name & " doesn't exist in " & NewWkbk.Name Else nWS.Range("C7").Value = oWS.Range("g40").Value End If Next oWS End Sub Untested, but it did compile. On 09/26/2011 19:39, rech wrote: I have 2 workbooks: OLD and NEW. They each have over 100 sheets (most sheets are the same but some are different). I want to set the value of cell C7 in the NEW workbook to the value from cell G40 in the OLD workbook. I'd like open both workbooks then run a macro that runs through every sheet in NEW and captures the sheet name then matches it to the same sheet in the OLD workbook, copies the value from G40, then pastes it into the NEW workbook C7. Thanks so much for any help!! -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy cell value from one worksheet to matching worksheet inanother workbook
On Sep 27, 6:21*am, Dave Peterson wrote:
Option Explicit Sub testme() * * *Dim OldWkbk As Workbook * * *Dim NewWkbk As Workbook * * *Dim oWS As Worksheet * * *Dim nWS As Worksheet * * *Set OldWkbk = Workbooks("youroldworkbooknamehere.xls") * * *Set NewWkbk = Workbooks("yournewworkbooknamehere.xls") * * *For Each oWS In OldWkbk.Worksheets * * * *Set nWS = Nothing * * * *On Error Resume Next * * * *Set nWS = NewWkbk.Worksheets(oWS.Name) * * * *On Error GoTo 0 * * * *If nWS Is Nothing Then * * * * * MsgBox oWS.Name & " doesn't exist in " & NewWkbk.Name * * * *Else * * * * * nWS.Range("C7").Value = oWS.Range("g40").Value * * * *End If * * *Next oWS End Sub Untested, but it did compile. On 09/26/2011 19:39, rech wrote: I have 2 workbooks: OLD and NEW. * They each have over 100 sheets (most sheets are the same but some are different). *I want to set the value of cell C7 in the NEW workbook to the value from cell G40 in the OLD workbook. *I'd like open both workbooks then run a macro that runs through every sheet in NEW and captures the sheet name then matches it to the same sheet in the OLD workbook, copies the value from G40, then pastes it into the NEW workbook C7. Thanks so much for any help!! -- Dave Peterson Dave, You're my hero! This worked beautifully!! Is there any way to exclude one or more sheets from this macro. In the OLD and NEW workbooks, I have a Summary sheet and I do not want the macro to apply to this sheet. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy cell value from one worksheet to matching worksheet in anotherworkbook
One way:
Option Explicit Sub testme() Dim OldWkbk As Workbook Dim NewWkbk As Workbook Dim oWS As Worksheet Dim nWS As Worksheet Set OldWkbk = Workbooks("youroldworkbooknamehere.xls") Set NewWkbk = Workbooks("yournewworkbooknamehere.xls") For Each oWS In OldWkbk.Worksheets Select Case UCase(oWS.Name) Case Is = UCase("Summary"), _ UCase("anyother"), _ UCase("another") 'do nothing Case Else Set nWS = Nothing On Error Resume Next Set nWS = NewWkbk.Worksheets(oWS.Name) On Error GoTo 0 If nWS Is Nothing Then MsgBox oWS.Name & " doesn't exist in " & NewWkbk.Name Else nWS.Range("C7").Value = oWS.Range("g40").Value End If End Select Next oWS End Sub (Still untested.) On 09/28/2011 12:50, rech wrote: On Sep 27, 6:21 am, Dave wrote: Option Explicit Sub testme() Dim OldWkbk As Workbook Dim NewWkbk As Workbook Dim oWS As Worksheet Dim nWS As Worksheet Set OldWkbk = Workbooks("youroldworkbooknamehere.xls") Set NewWkbk = Workbooks("yournewworkbooknamehere.xls") For Each oWS In OldWkbk.Worksheets Set nWS = Nothing On Error Resume Next Set nWS = NewWkbk.Worksheets(oWS.Name) On Error GoTo 0 If nWS Is Nothing Then MsgBox oWS.Name& " doesn't exist in "& NewWkbk.Name Else nWS.Range("C7").Value = oWS.Range("g40").Value End If Next oWS End Sub Untested, but it did compile. On 09/26/2011 19:39, rech wrote: I have 2 workbooks: OLD and NEW. They each have over 100 sheets (most sheets are the same but some are different). I want to set the value of cell C7 in the NEW workbook to the value from cell G40 in the OLD workbook. I'd like open both workbooks then run a macro that runs through every sheet in NEW and captures the sheet name then matches it to the same sheet in the OLD workbook, copies the value from G40, then pastes it into the NEW workbook C7. Thanks so much for any help!! -- Dave Peterson Dave, You're my hero! This worked beautifully!! Is there any way to exclude one or more sheets from this macro. In the OLD and NEW workbooks, I have a Summary sheet and I do not want the macro to apply to this sheet. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy cell value from one worksheet to matching worksheet inanother workbook
On Sep 29, 7:26*am, Dave Peterson wrote:
One way: Option Explicit Sub testme() * * *Dim OldWkbk As Workbook * * *Dim NewWkbk As Workbook * * *Dim oWS As Worksheet * * *Dim nWS As Worksheet * * *Set OldWkbk = Workbooks("youroldworkbooknamehere.xls") * * *Set NewWkbk = Workbooks("yournewworkbooknamehere.xls") * * *For Each oWS In OldWkbk.Worksheets * * * *Select Case UCase(oWS.Name) * * * * *Case Is = UCase("Summary"), _ * * * * * * * * * *UCase("anyother"), _ * * * * * * * * * *UCase("another") * * * * * *'do nothing * * * * *Case Else * * * * * *Set nWS = Nothing * * * * * *On Error Resume Next * * * * * *Set nWS = NewWkbk.Worksheets(oWS.Name) * * * * * *On Error GoTo 0 * * * * * *If nWS Is Nothing Then * * * * * * * MsgBox oWS.Name & " doesn't exist in " & NewWkbk.Name * * * * * *Else * * * * * * * nWS.Range("C7").Value = oWS.Range("g40").Value * * * * * *End If * * * * *End Select * * *Next oWS End Sub (Still untested.) On 09/28/2011 12:50, rech wrote: On Sep 27, 6:21 am, Dave *wrote: Option Explicit Sub testme() * * * Dim OldWkbk As Workbook * * * Dim NewWkbk As Workbook * * * Dim oWS As Worksheet * * * Dim nWS As Worksheet * * * Set OldWkbk = Workbooks("youroldworkbooknamehere.xls") * * * Set NewWkbk = Workbooks("yournewworkbooknamehere.xls") * * * For Each oWS In OldWkbk.Worksheets * * * * Set nWS = Nothing * * * * On Error Resume Next * * * * Set nWS = NewWkbk.Worksheets(oWS.Name) * * * * On Error GoTo 0 * * * * If nWS Is Nothing Then * * * * * *MsgBox oWS.Name& *" doesn't exist in "& *NewWkbk.Name * * * * Else * * * * * *nWS.Range("C7").Value = oWS.Range("g40").Value * * * * End If * * * Next oWS End Sub Untested, but it did compile. On 09/26/2011 19:39, rech wrote: I have 2 workbooks: OLD and NEW. * They each have over 100 sheets (most sheets are the same but some are different). *I want to set the value of cell C7 in the NEW workbook to the value from cell G40 in the OLD workbook. *I'd like open both workbooks then run a macro that runs through every sheet in NEW and captures the sheet name then matches it to the same sheet in the OLD workbook, copies the value from G40, then pastes it into the NEW workbook C7. Thanks so much for any help!! -- Dave Peterson Dave, You're my hero! *This worked beautifully!! *Is there any way to exclude one or more sheets from this macro. *In the OLD and NEW workbooks, I have a Summary sheet and I do not want the macro to apply to this sheet. -- Dave Peterson Wonderful!!! Worked perfectly! I appreciate this, Dave!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find matching date in another worksheet, copy and paste data | Excel Discussion (Misc queries) | |||
| Copy Rows From Worksheet Into Another Worksheet Same Workbook | Excel Programming | |||
| Copy Values From Worksheet to another Worksheet same Workbook | Excel Programming | |||
| Comparing two ranges on different sheets and copy matching results to new worksheet | Excel Programming | |||
| How can I link cell colours from worksheet to worksheet/workbook? | Excel Worksheet Functions | |||
Linear Mode
