Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue
So I'm trying to record a macro that refreshes data from an Access database,
filters the table to get unique values, then copy/pastes to another sheet to use those values as column headings (i.e., I'm doing a "Paste Special"--"Transpose" to make the vertically-listed values paste horizontally). Only problem is that the macro isn't working completely. The values refresh goes through and the table updates, but the filtered values don't copy/paste to the new sheet. If I go through the process manually, it works, but otherwise I can't get the macro to cooperate. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue
Post your code...
-- HTH... Jim Thomlinson "thefonz37" wrote: So I'm trying to record a macro that refreshes data from an Access database, filters the table to get unique values, then copy/pastes to another sheet to use those values as column headings (i.e., I'm doing a "Paste Special"--"Transpose" to make the vertically-listed values paste horizontally). Only problem is that the macro isn't working completely. The values refresh goes through and the table updates, but the filtered values don't copy/paste to the new sheet. If I go through the process manually, it works, but otherwise I can't get the macro to cooperate. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue
Here's the macro code:
Sub macUpdate() ' ' macUpdate Macro ' ' ActiveWorkbook.RefreshAll Selection.ClearContents Sheets("Data").Select Range("Z4").Select Range("Table_Query_from_MS_Access_Database_4[[#All],[Station]]"). _ AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("Z4:Z14").Select Selection.Copy Sheets("Scorecard").Select Range("B7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range("A7").Select End Sub "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "thefonz37" wrote: So I'm trying to record a macro that refreshes data from an Access database, filters the table to get unique values, then copy/pastes to another sheet to use those values as column headings (i.e., I'm doing a "Paste Special"--"Transpose" to make the vertically-listed values paste horizontally). Only problem is that the macro isn't working completely. The values refresh goes through and the table updates, but the filtered values don't copy/paste to the new sheet. If I go through the process manually, it works, but otherwise I can't get the macro to cooperate. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue
Thanks for the suggestion in cleaning up the query - I'll have to do this.
I think I figured out what the problem was and I still haven't gotten it to work right. It appears to be doing the processes out of order - it filters and copy/pastes the column titles either before or during the refresh, because I noticed that the data the macro returns is valid...just for the entry prior to the refresh. "Don Guillett" wrote: I have no experience with Access but a brief look suggests that it should work. Try this two liner instead. NO selections. You should use the same idea to remove selections from the first part ''''''''' Range("Z4:Z14").Copy 'second line Sheets("Scorecard").Range("B7"). _ PasteSpecial Paste:=xlPasteValues,Transpose:=True -- Don Guillett Microsoft MVP Excel SalesAid Software "thefonz37" wrote in message ... Here's the macro code: Sub macUpdate() ' ' macUpdate Macro ' ' ActiveWorkbook.RefreshAll Selection.ClearContents Sheets("Data").Select Range("Z4").Select Range("Table_Query_from_MS_Access_Database_4[[#All],[Station]]"). _ AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("Z4:Z14").Select Selection.Copy Sheets("Scorecard").Select Range("B7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range("A7").Select End Sub "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "thefonz37" wrote: So I'm trying to record a macro that refreshes data from an Access database, filters the table to get unique values, then copy/pastes to another sheet to use those values as column headings (i.e., I'm doing a "Paste Special"--"Transpose" to make the vertically-listed values paste horizontally). Only problem is that the macro isn't working completely. The values refresh goes through and the table updates, but the filtered values don't copy/paste to the new sheet. If I go through the process manually, it works, but otherwise I can't get the macro to cooperate. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue
Is there a break command or something that will force the macro to wait until
the refresh completes? "thefonz37" wrote: Thanks for the suggestion in cleaning up the query - I'll have to do this. I think I figured out what the problem was and I still haven't gotten it to work right. It appears to be doing the processes out of order - it filters and copy/pastes the column titles either before or during the refresh, because I noticed that the data the macro returns is valid...just for the entry prior to the refresh. "Don Guillett" wrote: I have no experience with Access but a brief look suggests that it should work. Try this two liner instead. NO selections. You should use the same idea to remove selections from the first part ''''''''' Range("Z4:Z14").Copy 'second line Sheets("Scorecard").Range("B7"). _ PasteSpecial Paste:=xlPasteValues,Transpose:=True -- Don Guillett Microsoft MVP Excel SalesAid Software "thefonz37" wrote in message ... Here's the macro code: Sub macUpdate() ' ' macUpdate Macro ' ' ActiveWorkbook.RefreshAll Selection.ClearContents Sheets("Data").Select Range("Z4").Select Range("Table_Query_from_MS_Access_Database_4[[#All],[Station]]"). _ AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("Z4:Z14").Select Selection.Copy Sheets("Scorecard").Select Range("B7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range("A7").Select End Sub "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "thefonz37" wrote: So I'm trying to record a macro that refreshes data from an Access database, filters the table to get unique values, then copy/pastes to another sheet to use those values as column headings (i.e., I'm doing a "Paste Special"--"Transpose" to make the vertically-listed values paste horizontally). Only problem is that the macro isn't working completely. The values refresh goes through and the table updates, but the filtered values don't copy/paste to the new sheet. If I go through the process manually, it works, but otherwise I can't get the macro to cooperate. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Issue
application.enableevents=false code reset to true rest of code -- Don Guillett Microsoft MVP Excel SalesAid Software "thefonz37" wrote in message ... Thanks for the suggestion in cleaning up the query - I'll have to do this. I think I figured out what the problem was and I still haven't gotten it to work right. It appears to be doing the processes out of order - it filters and copy/pastes the column titles either before or during the refresh, because I noticed that the data the macro returns is valid...just for the entry prior to the refresh. "Don Guillett" wrote: I have no experience with Access but a brief look suggests that it should work. Try this two liner instead. NO selections. You should use the same idea to remove selections from the first part ''''''''' Range("Z4:Z14").Copy 'second line Sheets("Scorecard").Range("B7"). _ PasteSpecial Paste:=xlPasteValues,Transpose:=True -- Don Guillett Microsoft MVP Excel SalesAid Software "thefonz37" wrote in message ... Here's the macro code: Sub macUpdate() ' ' macUpdate Macro ' ' ActiveWorkbook.RefreshAll Selection.ClearContents Sheets("Data").Select Range("Z4").Select Range("Table_Query_from_MS_Access_Database_4[[#All],[Station]]"). _ AdvancedFilter Action:=xlFilterInPlace, Unique:=True Range("Z4:Z14").Select Selection.Copy Sheets("Scorecard").Select Range("B7").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True Range("A7").Select End Sub "Jim Thomlinson" wrote: Post your code... -- HTH... Jim Thomlinson "thefonz37" wrote: So I'm trying to record a macro that refreshes data from an Access database, filters the table to get unique values, then copy/pastes to another sheet to use those values as column headings (i.e., I'm doing a "Paste Special"--"Transpose" to make the vertically-listed values paste horizontally). Only problem is that the macro isn't working completely. The values refresh goes through and the table updates, but the filtered values don't copy/paste to the new sheet. If I go through the process manually, it works, but otherwise I can't get the macro to cooperate. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro issue | Excel Discussion (Misc queries) | |||
Macro name issue | Excel Programming | |||
Macro issue | Excel Programming | |||
Macro issue | Excel Programming | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming |