Copy data from one tab to another based on cell value on one tab

[brentm]

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?

 

[Stephen Lloyd]

I haven't tested this, but I think it will put you on the right track.

Sub FindWorksheet()

Dim wb As workbook
Dim wsInv As worksheet
Dim wslookup As worksheet
Dim rDate As range

Set wb = Activeworkbook
Set wsInv = wb.Worksheets("Invoice")
Set rDate = wsInv.Range("C10")

For Each wslookup in wb.Worksheets
If rDate.Value <= CDate(wslookup.Name) And rDate.Value >
CDate(wslookup.Name)-7 Then
'Enter Code to pull information
End If
Next wslookup

End Sub

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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]

Rick,

Got it! Thanks for all your help.

Brent

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]

Stephen,

Thanks for your help. I went a different route, but your help got me
started. Thanks.

Brent