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!!

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

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.

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

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!!