On Sep 29, 7:26*am, Dave Peterson <peter...@XSPAMverizon.net> 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 Peterson<peter...@XSPAMverizon.net> *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!!
|
Similar Threads
