Rick,
Got it! Thanks for all your help.
Brent
"Rick Rothstein (MVP - VB)" wrote:
> That's a lot of code to go through. Before doing so, let me point out 2
> things that could possibly cause trouble for you... write back if this
> doesn't solve your problem.
>
> 1) In your first If-Then statement, you reference "G10" but in your original
> posting you said "C10" contained the date you needed to compare against. Is
> "G10" wrong?
>
> 2) You have several unqualified Range statements; I think qualifying them
> would be helpful. For example, in the first If-Then statement referenced in
> 1) above, you should probably qualify the Range("G10").Value, C10 if the G10
> is a mistype, with the worksheet it is on...
>
> Worksheets("Invoice").Range("G10").Value
>
> The first statement inside the above If-Then block is this...
>
> Range("C2:E8").Select
>
> Since the statement is inside the loop, I presume it should be referencing
> the worksheet currently being iterated through. So that it doesn't reference
> the active sheet and keep getting written over as the loop executes, you
> should qualify it with the worksheet currently being iterated on...
>
> WS.Range("C2:E8").Select
>
> You should go through all your code and make sure each range is qualified as
> to the worksheet it should be referencing.
>
> Rick
>
>
> "brentm" <(E-Mail Removed)> wrote in message
> news:6EC77E86-58FE-4DAB-ACE8-(E-Mail Removed)...
> > Rick,
> >
> > Thanks, but here is the code I have. When the macro is run, nothing
> > happens. I do not get any errors either. When I run to debug, there are
> > no
> > problems found. What am I missing?
> >
> > Sub Copy_SolutionB()
> > '
> > ' Copy_SolutionB Macro
> > '
> >
> > '
> > Dim WS As Worksheet
> > For Each WS In Worksheets
> > If WS.Name = Format(Range("G10").Value, "mmmm d") Then
> > Range("C2:E8").Select
> > Selection.Copy
> > Sheets("Invoice").Select
> > Range("C14:E20").Select
> > ActiveSheet.Paste
> > Application.CutCopyMode = False
> > Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> > Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> > With Selection.Borders(xlEdgeLeft)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlEdgeTop)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlEdgeBottom)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlEdgeRight)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlInsideVertical)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlInsideHorizontal)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> > Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> > With Selection.Borders(xlEdgeLeft)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlEdgeTop)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlMedium
> > End With
> > With Selection.Borders(xlEdgeBottom)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlEdgeRight)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlInsideVertical)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > With Selection.Borders(xlInsideHorizontal)
> > .LineStyle = xlContinuous
> > .ColorIndex = 0
> > .TintAndShade = 0
> > .Weight = xlThin
> > End With
> > Range("C14:E14,C16:E16,C18:E18,C20:E20").Select
> > Range("C20").Activate
> > With Selection.Interior
> > .Pattern = xlSolid
> > .PatternColorIndex = xlAutomatic
> > .ThemeColor = xlThemeColorDark1
> > .TintAndShade = -0.249977111117893
> > .PatternTintAndShade = 0
> > End With
> > End If
> > Exit For
> > Next
> >
> > End Sub
> >
> > Thanks a ton for your expert help!
> > Brent
> >
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> You will use a structure similar to this...
> >>
> >> Dim WS As Worksheet
> >> For Each WS In Worksheets
> >> If WS.Name = Format(Range("C10").Value, "mmmm d") Then
> >> '
> >> ' << Do whatever here >>
> >> '
> >> Exit For
> >> End If
> >> Next
> >>
> >> Put the code you say you know how to do where indicated.
> >>
> >> Rick
> >>
> >>
> >> "brentm" <(E-Mail Removed)> wrote in message
> >> news:839DDB59-6903-4923-AC44-(E-Mail Removed)...
> >> >I have a spreadsheet where each tab represents 1 week. the tabs are
> >> >named
> >> > based on the week ending date that tab covers - "June 25", "July 2",
> >> > etc.
> >> > There is a master tab named "Invoice" with the period ending date
> >> > residing
> >> > in
> >> > cell C10. Is there a way I can have a macro go to the appropriate tab
> >> > based
> >> > on the date entered in C10 on the "Invoice" tab? I need the macro to
> >> > copy
> >> > over specific information, which I can do, but I wanted the macro to
> >> > automatically look at the value in C10 on "Invoice", then go get the
> >> > information from the appropriate tab that corrosponds to that cell
> >> > value.
> >> > Anyone have any ideas?
> >>
> >>
>
>
|
Similar Threads
