VBA copy clear paste

[CR]

Excel 2003
I have a spread sheet that I paste an entire table into from a webpage each
week.
Range A6:V44
The information starts in A6:A44 and expands one column each week except for
the week numbers they are in B7:V7 each week
I am trying to set the print area using .CurrentRegion.Select
I don't want to print out the entire Range only the columns that have the
information in them.
To use the CurrentRegion.Select, I need to get rid of the week numbers while
I select the Print Area and then
paste them back after the print area is selected.

If I manually remove the weeks and run this without the copy, clear, paste
it works fine, but with it, the thing stops on
ActiveSheet.Paste

Any ideas why? Or a better way to do this?
Also why doesn't Range("B7:V7").Cut work?


Private Sub CommandButton2_Click()
'Set Print area.
Range("B7:V7").Select
Selection.Copy
Selection.Clear
ActiveSheet.Range("A6").CurrentRegion.Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range("B7:V7").Select
ActiveSheet.Paste '........Stops running here, Run-time error '1004'
Application-defined or object-defined error
Sheet2.PrintPreview

End Sub

Thanks
CR

 

[CR]

I really am not very good at this, but I got it to work with this code. I
would still like to know why it wouldn't work with the orginal. And if there
is a better way.

Private Sub CommandButton2_Click()
'Set Print area.
Range("B7:V7").Select
x = Selection.Value
Selection.Clear
ActiveSheet.Range("A6").CurrentRegion.Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range("B7:V7").Select
Selection.Value = x
Sheet2.PrintPreview

End Sub

 

[Homey]

I would still like to know why it wouldn't work with the orginal

Selection.Copy << puts range to clipboard
Selection.Clear << clear clipboard

when you do clear it clear clipboard. many things you do clear clipboard.
people dont like this but this how Excel always work.

your way is good but could be neater

Private Sub CommandButton2_Click()
Dim Hold as Variant
With Range("B7:V7")
Hold = .Value
.ClearContents
Range("A6").CurrentRegion.Name="Print_Area"
.Value = Hold
End With
Activesheet.PrintPreview
End Sub




|I really am not very good at this, but I got it to work with this code. I
| would still like to know why it wouldn't work with the orginal. And if
there
| is a better way.
|
| Private Sub CommandButton2_Click()
| 'Set Print area.
| Range("B7:V7").Select
| x = Selection.Value
| Selection.Clear
| ActiveSheet.Range("A6").CurrentRegion.Select
| ActiveSheet.PageSetup.PrintArea = Selection.Address
| Range("B7:V7").Select
| Selection.Value = x
| Sheet2.PrintPreview
|
| End Sub
|
|
|
|
| | > Excel 2003
| > I have a spread sheet that I paste an entire table into from a webpage
| > each week.
| > Range A6:V44
| > The information starts in A6:A44 and expands one column each week except
| > for the week numbers they are in B7:V7 each week
| > I am trying to set the print area using .CurrentRegion.Select
| > I don't want to print out the entire Range only the columns that have
the
| > information in them.
| > To use the CurrentRegion.Select, I need to get rid of the week numbers
| > while I select the Print Area and then
| > paste them back after the print area is selected.
| >
| > If I manually remove the weeks and run this without the copy, clear,
paste
| > it works fine, but with it, the thing stops on
| > ActiveSheet.Paste
| >
| > Any ideas why? Or a better way to do this?
| > Also why doesn't Range("B7:V7").Cut work?
| >
| >
| > Private Sub CommandButton2_Click()
| > 'Set Print area.
| > Range("B7:V7").Select
| > Selection.Copy
| > Selection.Clear
| > ActiveSheet.Range("A6").CurrentRegion.Select
| > ActiveSheet.PageSetup.PrintArea = Selection.Address
| > Range("B7:V7").Select
| > ActiveSheet.Paste '........Stops running here, Run-time error '1004'
| > Application-defined or object-defined error
| > Sheet2.PrintPreview
| >
| > End Sub
| >
| > Thanks
| > CR
| >
|
|

 

[Don Guillett]

I can't figure out what you want.
Sub printcurreg()
ActiveSheet.Range("a5").CurrentRegion.PrintPreview
End Sub
If this doesn't do as desired,
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.

 

[CR]

Selection.Copy << puts range to clipboard

Selection.Clear << clear clipboard

I thought that might be what was happening so I tried to paste to a
different range before I did the Selection.Clear and
I still had the same error. So I guess I'm still baffled.

I'm just glad that I made it work.
I'm sure that someone with more feathers in their cap with VBA could make it
shorter and neater.
I never have known the proper indenting protocol for code or the reasons for
it.

Thanks