Range Resize property
With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count - _
1, .Columns.Count - 7) _
.Name = (sRange & "data")
End With
Range(sRange & "data").Copy
wsNew.Range("B10").Pastespecial Paste:=xlValues
wsNew.Name = sRange & vChanArr(n)
--
Regards,
Tom Ogilvy
"jacqui" wrote in message
...
Tom,
Thank you. I'd just about got there with the XL help but
your explanation was much clearer! Just one final thing.
My source data is now pasting to the "data" sheet as
values, however when I step through the original code as
below I'm still missing a bit. If I do a manual
copy/pastespecial, I'm offered the PasteSpecial dialog box
(unlike before where I had to select a type of file which
means we must have remedied the .CSV bit). By doing it
manually it proves I can get the data across in the right
format but how would I incorporate a pastespecial values
into my Copy Destination line as below. Sorry but I'm
stuck again.
Hope you can help.
Many thanks
Jacqui
With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7) _
.Name = (sRange & "data")
End With
Range(sRange & "data").Copy
Destination:=wsNew.Range("B10")
wsNew.Name = sRange & vChanArr(n)
-----Original Message-----
Pastespecial has two different forms. One is with a
worksheet object, the
other is with a range. They each have different
arguments. You have mixed
the two. (used a worksheet object and range arguments)
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Should work.
--
Regards,
Tom Ogilvy
"Jacqui" wrote in
message
...
Tom
Thank you for your reply. Yeah I take your point, my
Import sub should format it correctly in the first
place.
I've tried to rectify this by adding in a PasteSpecial
xlvalues in place of Paste. However, I'm getting an
Object Defined error . Would you mind having a look at
my
code below because I'm wondering if the change should be
in the OpenText bit instead. Sorry it's a bit lengthy
but
at least you can see the full sub.
Many thanks
Jacqui
Sub import_data()
Dim Openfile
With Application
.DisplayAlerts = False
.StatusBar = " Importing ERS Source
File"
.ScreenUpdating = False
End With
ChDrive "l:\"
ChDir ALT_LOC & "\source data\"
Application.ScreenUpdating = True
Openfile = Application.GetOpenFilename("Text
Files (*.txt),*.txt")
If Openfile < False Then
Application.ScreenUpdating = False
Worksheets("Data").Select
Range("a1").Select
If Not IsEmpty(Range
("a1").Value) Then
Set OldRegion =
ActiveCell.CurrentRegion
Set NewRegion = Range
(OldRegion.Cells(1, 1), OldRegion.Cells
(OldRegion.Rows.Count, 108))
NewRegion.ClearContents
End If
Range("a1").Select
Workbooks.OpenText
FileName:=Openfile,
_
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
Textqualifier:=xlTextQualifierDoubleQuote, _
Comma:=True
Else
MsgBox "You have either not
selected a
file or selected Cancel - the process will be
terminated."
GoTo Reset_Screen
End If
Application.ScreenUpdating = False
ActiveWindow.Caption = "Data File"
Range("a1").Select
Selection.CurrentRegion.Copy
Windows("ERS-Data Management File").Activate
Worksheets("Data").Select
Range("a1").Select
IT'S GOING WRONG ON THE FOLLOWING LINE
ActiveSheet.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("a1").Select
Selection.CurrentRegion.Name = "DatArea"
Worksheets("Menu").Range("e16").Value = Openfile
Worksheets("Menu").Range("e17").Value = Mid
(Worksheets("Data").Range("a2").Value, 16, 9) & " " & _
Mid(Worksheets("Data").Range
("a1").Value, 9, 4)
Worksheets("Menu").Range("e18").Value = Mid
(Worksheets("Data").Range("a3").Value, 1, 2)
Worksheets("Menu").Range("e19").Value = Mid
(Worksheets("Data").Range("a3").Value, 4, 4)
Windows("Data File").Activate
ActiveWindow.Close savechanges:=False
ActiveSheet.Range("A1:A3").Select
Selection.EntireRow.Delete
Sub_Totals
Reset_Screen:
Windows("ERS-Data Management File").Activate
Worksheets("Menu").Select
Range("a1").Select
With Application
.DisplayAlerts = True
.StatusBar = False
.ScreenUpdating = True
End With
End Sub
-----Original Message-----
There is no pastespecial CSV. If the file was properly
parsed in the
original, a simple paste should suffice.
--
Regards,
Tom Ogilvy
"jacqui" wrote in
message
...
Hi Bob
Thank you very much for your reply , didn't think to
use
offset!
Code works great except that my source data
originates
from a CSV file so when I paste it to the target
file I
need to do a paste special, CSV (I tried it first
manually). Sorry forgot to mention this previously.
How would I incorporate a paste special, csv into the
following?
With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7) _
.Name = (sRange & "data")
End With
Range(sRange & "data").Copy
Destination:=wsNew.Range("B10")
'wsNew.Columns("A:IV").AutoFit
wsNew.Name = sRange & vChanArr(n)
Would really appreciate your help.
Many thanks
Jacqui
-----Original Message-----
Morning Jacqui,
Give this a try
With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7).Name =
(sRange & "data")
End With
--
HTH
Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)
"jacqui" wrote in
message
...
I would like to resize an existing range without
having
to
hard code in the new column reference if poss.
The
original size of my range is A1:DG4155. The
range is
called sRange and is originally set using the
CurrentRegion property. I'd like to resize it so
that
the
first column is H instead of A and the first row
is 2
instead of 1.
The code I'm using is...
firstcol = 8
With Range(sRange)
.Resize(, firstcol).Name = (sRange & "data")
End With
However, this produced the wrong result in that it
resized
my range so that it became A to H, ie 8 columns.
Not
very
clever.
Can anyone kindly help with some syntax?
Many thanks
Jacqui
.
.
.
|