I have data in "Mailer List.xls"
On the sheet labeled "mor01001" of this workbook I need to bring in th
data cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


I need a macro that I can run once a month to import this data int
"Direct Mailer Template.xls"
It would need to be imported to the sheet labeled "Helper Sheet" in th
corresponding cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G400

--
jermsalerm
-----------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...fo&userid=3016
View this thread: http://www.excelforum.com/showthread.php?threadid=49854

Sub c()
srcpath = "C:\SFADB"
srcbook = "random.xls"
srcsheet = "R1"

aydes = Array("c1:c6", "d1:d6", "e1:e6")
aysource = Array("$A$5:$A$10", "$b$5:$b$10", "$b$5:$b$10")

Set wsdes = Sheet

For i = LBound(aydes) To UBound(aydes)
With wsdes.Range(aydes(i))
.FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet &
"'!" & aysource(i)
.Copy
.PasteSpecial xlPasteValues
End With
Next i
Application.CutCopyMode = False
Set wsdes = Nothing
End Sub

edit to fit

- voodooJoe

"jermsalerms"
wrote in message
...

I have data in "Mailer List.xls"
On the sheet labeled "mor01001" of this workbook I need to bring in the
data cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


I need a macro that I can run once a month to import this data into
"Direct Mailer Template.xls"
It would need to be imported to the sheet labeled "Helper Sheet" in the
corresponding cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile:
http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=498548

The following first code below will work when your source is a single
contiguous range.
However, since your Source has several areas you will need to add a loop to
copy one area at a time. See bottom:

Option Explicit

Sub CopyFromTest1()

Dim sFile As String
Dim Wbk As Workbook
Dim Source As Range
Dim Dest As Range


On Error Resume Next
Set Wbk = Workbooks("Test1")

If Wbk Is Nothing Then 'its not open
sFile = ThisWorkbook.Path & "\Test1.xls"
Set Wbk = Workbooks.Open(sFile)
End If
If Wbk Is Nothing Then
MsgBox "Unable to find the file: " & sFile, vbOKOnly, "ERROR"
Exit Sub
End If

Set Source = Wbk.Sheets(1).Range("A1:A2")
Set Dest = ThisWorkbook.Sheets(1).Range("A1:A2")
Source.Copy Dest
Wbk.Close False

End Sub


Redefine the Source and Dest to include all of the areas, keeping them in
the same order.
Add this variable: dim i as integer

Then insert or change the copy part to:
For i=1 to Source.Areas.Count
Source.Area(i).Copy Dest.Area(i)
i=i+1
Next i

"jermsalerms" wrote:


I have data in "Mailer List.xls"
On the sheet labeled "mor01001" of this workbook I need to bring in the
data cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


I need a macro that I can run once a month to import this data into
"Direct Mailer Template.xls"
It would need to be imported to the sheet labeled "Helper Sheet" in the
corresponding cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=498548

Hi jermsalerms,

The ranges you've given for the copy and paste (A2:A4001, B2:B4001,
C2:C4001, D2:D4001, E2:E4001, F2:F4001, G2:G4001) corresponds with the single
range A2:G4001. In which case a single copy-paste is all that's required...

Sub ImportData()
Application.ScreenUpdating = False
On Error Resume Next '< error = Mailer List.xls is already open
Workbooks.Open ("C:\Windows\Desktop\Mailer List.xls")
Sheets("mor01001").Range("A2:G4001").Copy _
Destination:=ThisWorkbook.Sheets("Helper Sheet").Range("A2")
Workbooks("Mailer List.xls").Close False
Application.ScreenUpdating = True
End Sub

HTH,
John
--
The major part of getting the right answer lies in asking the right
question...


"jermsalerms" wrote:


I have data in "Mailer List.xls"
On the sheet labeled "mor01001" of this workbook I need to bring in the
data cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


I need a macro that I can run once a month to import this data into
"Direct Mailer Template.xls"
It would need to be imported to the sheet labeled "Helper Sheet" in the
corresponding cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=498548

I wasn't as observant as John to see that it really is only one range, that
makes it a lot easier.
but there really isn't a need to open the source workbook to get the
values - use a link formula then copy/pastespecial values

Sub X()
srcpath = "C:\SFADB"
srcbook = "random.xls"
srcsheet = "R1"
srcrng = "A2:G4001"

With ActiveSheet
.Range(srcrange).FormulaArray = "='" & srcpath & "\[" & srcbook &
"]" & srcsheet & "'!" & srcrng
.Copy
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Sub

- vdJ



"John Skewes" wrote in message
...
Hi jermsalerms,

The ranges you've given for the copy and paste (A2:A4001, B2:B4001,
C2:C4001, D2:D4001, E2:E4001, F2:F4001, G2:G4001) corresponds with the
single
range A2:G4001. In which case a single copy-paste is all that's
required...

Sub ImportData()
Application.ScreenUpdating = False
On Error Resume Next '< error = Mailer List.xls is already open
Workbooks.Open ("C:\Windows\Desktop\Mailer List.xls")
Sheets("mor01001").Range("A2:G4001").Copy _
Destination:=ThisWorkbook.Sheets("Helper
Sheet").Range("A2")
Workbooks("Mailer List.xls").Close False
Application.ScreenUpdating = True
End Sub

HTH,
John
--
The major part of getting the right answer lies in asking the right
question...


"jermsalerms" wrote:


I have data in "Mailer List.xls"
On the sheet labeled "mor01001" of this workbook I need to bring in the
data cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


I need a macro that I can run once a month to import this data into
"Direct Mailer Template.xls"
It would need to be imported to the sheet labeled "Helper Sheet" in the
corresponding cells
A2:A4001, B2:B4001, C2:C4001, D2:D4001, E2:E4001 F2:F4001 G2:G4001


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile:
http://www.excelforum.com/member.php...o&userid=30167
View this thread:
http://www.excelforum.com/showthread...hreadid=498548

I edited the formula to fit but it is giving me a "Compile Error: Syntax
Error"

Here is the formula...am I missing something?

Sub X()
srcpath = "C:\Documents and Settings\user\My
Documents\Spreadsheets\Data"
srcbook = "Mailer List.xls"
srcsheet = "mor01001"
srcrng = "A2:G4001"

With ActiveSheet
..Range(srcrange).FormulaArray = "='" & srcpath & "\[" & srcbook &
"]" & srcsheet & "'!" & srcrng
..Copy
..PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Sub


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=498548

minor syntax error(s). edit to suit.

Sub X()
srcpath = "C:\SFADB"
srcbook = "ran dom.xls"
srcsheet = "R1"
srcrng = "d4:e5"

With Sheet1.Range(srcrng) 'correct variable and add to with statement so
it applies to copy and pastespec methods as well
.FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet &
"'!" & srcrng
.Copy
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Sub

cheers - voodooJoe


"jermsalerms"
wrote in message
...

I edited the formula to fit but it is giving me a "Compile Error: Syntax
Error"

Here is the formula...am I missing something?

Sub X()
srcpath = "C:\Documents and Settings\user\My
Documents\Spreadsheets\Data"
srcbook = "Mailer List.xls"
srcsheet = "mor01001"
srcrng = "A2:G4001"

With ActiveSheet
.Range(srcrange).FormulaArray = "='" & srcpath & "\[" & srcbook &
"]" & srcsheet & "'!" & srcrng
.Copy
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Sub


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile:
http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=498548

This is what I have entered into Visual Basic...when I run the macro
get error 400


Sub X()
srcpath = "C:\Documents and Settings\user\M
Documents\Spreadsheets\Data"
srcbook = "InfoUSA List.xls"
srcsheet = "mor01001"
srcrng = "a2:g4001"

With Sheet1.Range(srcrng) 'correct variable and add to with statemen
so it applies to copy and pastespec methods as well
.FormulaArray = "='" & srcpath & "\[" & srcbook & "]" & srcsheet & "'!
& srcrng
.Copy
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Su

--
jermsalerm
-----------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...fo&userid=3016
View this thread: http://www.excelforum.com/showthread.php?threadid=49854

I figured out the error 400 has to do with protected sheets...

now my problem is that everything is supposed to be importing to sheet
two and it goes to sheet one.

The code is written on sheet two...is there something I am doing
wrong...or is there extra coding that needs to be in there to make it
work correctly.

I must have it go to sheet two because sheet one gets imported into a
contact manager that reads the first sheet only.


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=498548