Macro to import from one closed workbook

[jermsalerms]

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, D24001, 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, D24001, E2:E4001 F2:F4001 G2:G400

 

[voodooJoe]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

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

 

[Guest]

Hi jermsalerms,

The ranges you've given for the copy and paste (A2:A4001, B2:B4001,
C2:C4001, D24001, 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

 

[voodooJoe]

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

 

[jermsalerms]

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

 

[voodooJoe]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[jermsalerms]

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

 

[jermsalerms]

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.