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.

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.

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.

Macro Issue
 

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.

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.

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.

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.