reference 2 arrays in a For Loop
 

In the following I am trying to copy and paste data from one sheet to another
but arranged differently (so I can import into a database).

I what to do this by defining the source ranges in the array mySourceMAT and
the destination ranges in the array myDestMAT.

I'm pretty sure I've got the source part right but not the dest part in my
For loop. i.e. Range(myDestMAT(k)).Select as this where it debugs and the
element is empty.

Any ideas?

Bruce



Sub import()

Dim k As Integer
Dim mySourceMAT
ReDim mySourceMAT(1 To 5)
Dim myDestMAT(1 To 5)

'Application.ScreenUpdating = False

'variables
myPeriod = Sheets("Instructions").Range("B16")
myMarket = Sheets("Instructions").Range("B17")
mySourceMAT = Array("B8:B18", "D8:D18", "F8:F18", "H8:H18", "J8:J18")
myDestMAT(1) = Array("E2")
myDestMAT(2) = Array("F2")
myDestMAT(3) = Array("G2")
myDestMAT(4) = Array("H2")
myDestMAT(5) = Array("I2")


myClear = "2:100"
' myDestMAT (1)

'clear contents
With Sheets("Import").Rows(myClear)
.ClearContents
End With

'Start Update
k = 1

For Each a In mySourceMAT

Sheets("Inputs").Select
Range(a).Select
Selection.Copy

Sheets("Import").Select
Range(myDestMAT(k)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

k = k + 1

Next a

Range("A1").Select
Application.ScreenUpdating = True

End Sub

reference 2 arrays in a For Loop
 

Hi,
Remove ARRAY( ..) as below:


myDestMAT(1) = "E2"
myDestMAT(2) = "F2"
myDestMAT(3) = "G2"
myDestMAT(4) = "H2"
myDestMAT(5) = "I2"

Add this before your clear statement

Sheets("Import").Select

HTH

"Bruce" wrote:

In the following I am trying to copy and paste data from one sheet to another
but arranged differently (so I can import into a database).

I what to do this by defining the source ranges in the array mySourceMAT and
the destination ranges in the array myDestMAT.

I'm pretty sure I've got the source part right but not the dest part in my
For loop. i.e. Range(myDestMAT(k)).Select as this where it debugs and the
element is empty.

Any ideas?

Bruce



Sub import()

Dim k As Integer
Dim mySourceMAT
ReDim mySourceMAT(1 To 5)
Dim myDestMAT(1 To 5)

'Application.ScreenUpdating = False

'variables
myPeriod = Sheets("Instructions").Range("B16")
myMarket = Sheets("Instructions").Range("B17")
mySourceMAT = Array("B8:B18", "D8:D18", "F8:F18", "H8:H18", "J8:J18")
myDestMAT(1) = Array("E2")
myDestMAT(2) = Array("F2")
myDestMAT(3) = Array("G2")
myDestMAT(4) = Array("H2")
myDestMAT(5) = Array("I2")


myClear = "2:100"
' myDestMAT (1)

'clear contents
With Sheets("Import").Rows(myClear)
.ClearContents
End With

'Start Update
k = 1

For Each a In mySourceMAT

Sheets("Inputs").Select
Range(a).Select
Selection.Copy

Sheets("Import").Select
Range(myDestMAT(k)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

k = k + 1

Next a

Range("A1").Select
Application.ScreenUpdating = True

End Sub