Macro Issue

  • Thread starter Thread starter thefonz37
  • Start date Start date
T

thefonz37

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.
 
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
 
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
(e-mail address removed)
thefonz37 said:
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 said:
Post your code...
Click to expand...
Click to expand...
 
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 said:
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
(e-mail address removed)
thefonz37 said:
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 said:
Post your code...
--
HTH...

Jim Thomlinson


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Is there a break command or something that will force the macro to wait until
the refresh completes?

thefonz37 said:
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 said:
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
(e-mail address removed)
thefonz37 said:
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


:

Post your code...
--
HTH...

Jim Thomlinson


:

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.
Click to expand...
Click to expand...
Click to expand...
 
application.enableevents=false

code
reset to true
rest of code
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
thefonz37 said:
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 said:
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
(e-mail address removed)
thefonz37 said:
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


:

Post your code...
--
HTH...

Jim Thomlinson


:

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.
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conso macro 0
Macro Related to Paste Values 2
Macro to copy value in field and insert it into filter 2
Run macro from active workbook whose name keeps changing 20
Number stored as text 4
Looping and passing variables to a macro etc. 1
Simple Macro 1
Cleaning up code 2

Back
Top