Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one tab to another based on cell value on one tab
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one tab to another based on cell value on one tab
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 "brentm" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one tab to another based on cell value on one tab
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one tab to another based on cell value on one
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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one tab to another based on cell value on one
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" wrote in message ... 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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one tab to another based on cell value on one
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" wrote in message ... 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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one tab to another based on cell value on one
Stephen,
Thanks for your help. I went a different route, but your help got me started. Thanks. Brent "Stephen Lloyd" wrote: 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 "brentm" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cell data from workbook based on user input | Excel Programming | |||
Copy data in column based on cell value | Excel Programming | |||
Copy data in column based on cell value | Excel Programming | |||
Copy data in column based on cell value | Excel Programming | |||
Macro to copy cell data to word document based on an active row? | Excel Programming |