Code seems to be okay until the first copy line which errors out in yellow.

Opens the four "state named" workbooks okay.

This line, one line up from error line shows lCol4 = 10 when cursor is hovered over it, which is correct.

Set rangeJ = Range("J1:J" & lCol4)

Is my syntax wrong with the copy lines?

Thanks.
Howard


Option Explicit

Sub MondayMornCopy()

'Idaho, Montana, Wyoming, Nebraska
Dim Idaho As Workbook, Montana As Workbook, Wyoming As Workbook, Nebraska As Workbook
Dim lCol1 As Long, lCol2 As Long, lCol3 As Long, lCol4 As Long
Dim rangeA As Range, rangeD As Range, rangeF As Range, rangeJ As Range
Dim copyArr As Variant
Dim i As Long

Workbooks.Open Filename:= _
"C:\Users\Howard Kittle\Documents\Idaho.xlsm"
Workbooks.Open Filename:= _
"C:\Users\Howard Kittle\Documents\Montana.xlsm"
Workbooks.Open Filename:= _
"C:\Users\Howard Kittle\Documents\Wyoming.xlsm"
Workbooks.Open Filename:= _
"C:\Users\Howard Kittle\Documents\Nebraska.xlsm"

lCol1 = Cells(Rows.Count, 1).End(xlUp).Row
lCol2 = Cells(Rows.Count, 4).End(xlUp).Row
lCol3 = Cells(Rows.Count, 6).End(xlUp).Row
lCol4 = Cells(Rows.Count, 10).End(xlUp).Row

Application.ScreenUpdating = False

copyArr = Array(Idaho, Montana, Wyoming, Nebraska)
For i = LBound(copyArr) To UBound(copyArr)

With copyArr(i)
Set rangeA = Range("A1:A" & lCol1)
Set rangeD = Range("D1:D" & lCol2)
Set rangeF = Range("F1:F" & lCol3)
Set rangeJ = Range("J1:J" & lCol4)

Workbooks("Master.xlsm").Sheets("Sheet1").Range("A " & Rows.Count).End(xlUp)(2) = copyArr(i).rangeA
Workbooks("Master.xlsm").Sheets("Sheet1").Range("D " & Rows.Count).End(xlUp)(2) = copyArr(i).rangeD
Workbooks("Master.xlsm").Sheets("Sheet1").Range("F " & Rows.Count).End(xlUp)(2) = copyArr(i).rangeF
Workbooks("Master.xlsm").Sheets("Sheet1").Range("J " & Rows.Count).End(xlUp)(2) = copyArr(i).rangeJ

copyArr(i).Save
copyArr(i).Close
End With

Next
Application.ScreenUpdating = True
End Sub

Hi Howard,

Am Sun, 1 Dec 2013 23:51:19 -0800 (PST) schrieb Howard:

With copyArr(i)
Set rangeA = Range("A1:A" & lCol1)

you did not refer to a sheet.

Try:

Sub MondayMornCopy2()

Dim lCol1 As Long, lCol2 As Long, lCol3 As Long, lCol4 As Long
Dim rangeA As Variant, rangeD As Variant, rangeF As Variant, rangeJ As
Variant
Dim copyArr As Variant
Dim i As Long

Const myPath = "C:\Users\Howard Kittle\Documents\"
copyArr = Array("Idaho", "Montana", "Wyoming", "Nebraska")

Application.ScreenUpdating = False

For i = LBound(copyArr) To UBound(copyArr)
Workbooks.Open myPath & copyArr(i) & ".xlsm"
With ActiveWorkbook.Sheets("Sheet1")
lCol1 = .Cells(.Rows.Count, 1).End(xlUp).Row
lCol2 = .Cells(.Rows.Count, 4).End(xlUp).Row
lCol3 = .Cells(.Rows.Count, 6).End(xlUp).Row
lCol4 = .Cells(.Rows.Count, 10).End(xlUp).Row

rangeA = .Range("A1:A" & lCol1)
rangeD = .Range("D1:D" & lCol2)
rangeF = .Range("F1:F" & lCol3)
rangeJ = .Range("J1:J" & lCol4)

Workbooks("Master.xlsm").Sheets("Sheet1") _
.Range("A" & Rows.Count).End(xlUp)(2).Resize(rowsize:=lCol1) =
rangeA
Workbooks("Master.xlsm").Sheets("Sheet1") _
.Range("D" & Rows.Count).End(xlUp)(2).Resize(rowsize:=lCol2) =
rangeD
Workbooks("Master.xlsm").Sheets("Sheet1") _
.Range("F" & Rows.Count).End(xlUp)(2).Resize(rowsize:=lCol3) =
rangeF
Workbooks("Master.xlsm").Sheets("Sheet1") _
.Range("J" & Rows.Count).End(xlUp)(2).Resize(rowsize:=lCol4) =
rangeJ
ActiveWorkbook.Close savechanges:=True
End With
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

That works very nicely.

With this...

With copyArr(i)
Set rangeA = Range("A1:A" & lCol1)
you did not refer to a sheet.

I thought copyArr(i) would be the first sheet in the array, Idaho, and that rangeA had been set to that sheet what I wanted to copy, and the second time it would be the same with Montana etc.

But cannot argue with the success of your code.

This is new to me:

..Resize(rowsize:=lCol1) = rangeA

Resize I understand in some other uses, but the .Resize(rowsize: puzzles me.

Unless to explain it is brief, I will study it off forum and google.

Thanks, Claus.

Howard

One more question, please.

I see code that the four workbooks are opened, but I see nothing that closes them. After the code runs those books are not open...?

Howard

Hi Howard,

Am Mon, 2 Dec 2013 03:23:53 -0800 (PST) schrieb Howard:

.Resize(rowsize:=lCol1) = rangeA

you see that I declared rangeA as variant.
The code reads the values of the range in this array and therefore I
have to resize the output range for the count of rows (the count of
items into this array)


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Howard,

Am Mon, 2 Dec 2013 03:30:30 -0800 (PST) schrieb Howard:

I see code that the four workbooks are opened, but I see nothing that closes them. After the code runs those books are not open...?

the opened workbook is always the active workbook. And after writing the
values to "Master" there is the code line:
ActiveWorkbook.Close savechanges:=True
When the code is through only "Master" is open.


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, December 2, 2013 3:34:45 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Mon, 2 Dec 2013 03:30:30 -0800 (PST) schrieb Howard:



I see code that the four workbooks are opened, but I see nothing that closes them. After the code runs those books are not open...?



the opened workbook is always the active workbook. And after writing the

values to "Master" there is the code line:

ActiveWorkbook.Close savechanges:=True

When the code is through only "Master" is open.





Regards

Claus B.

I completely overlooked that. Sorry, my bad.

Howard

On Monday, December 2, 2013 3:31:29 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Mon, 2 Dec 2013 03:23:53 -0800 (PST) schrieb Howard:



.Resize(rowsize:=lCol1) = rangeA



you see that I declared rangeA as variant.

The code reads the values of the range in this array and therefore I

have to resize the output range for the count of rows (the count of

items into this array)





Regards

Claus B.


Okay, thanks. I'll need to make some notes to myself on that, plus some study time.

Howard

Hi Howard,

Am Mon, 2 Dec 2013 04:12:42 -0800 (PST) schrieb Howard:

I'll need to make some notes to myself on that, plus some study time.

may I write comments next time?


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Howard,

Am Mon, 2 Dec 2013 04:12:42 -0800 (PST) schrieb Howard:

I'll need to make some notes to myself on that, plus some study time.

with a second loop the code will be more compact:

Sub MondayMornCopy3()

Dim LRow As Long 'Last row
Dim varCol As Variant 'Array of columns
Dim varOut As Variant 'Array of data
Dim copyArr As Variant 'Array of workbooks
Dim i As Long 'Counter for workbook array
Dim j As Integer 'Counter for columns array

Const myPath = "C:\Users\Howard Kittle\Documents\"
copyArr = Array("Idaho", "Montana", "Wyoming", "Nebraska")
varCol = Array(1, 4, 6, 10)

Application.ScreenUpdating = False

For i = LBound(copyArr) To UBound(copyArr)
Workbooks.Open myPath & copyArr(i) & ".xlsm"
With ActiveWorkbook.Sheets("Sheet1")
For j = LBound(varCol) To UBound(varCol)
LRow = .Cells(.Rows.Count, varCol(j)).End(xlUp).Row
varOut = .Range(.Cells(1, varCol(j)), .Cells(LRow, varCol(j)))
Workbooks("Master.xlsm").Sheets("Sheet1") _
.Cells(Rows.Count, varCol(j)).End(xlUp)(2) _
.Resize(rowsize:=LRow) = varOut
Next j
ActiveWorkbook.Close savechanges:=True
End With
Next i
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I'm inclined to go a different way when pulling data from more than 1
unopen file. ADODB allows you to pull data in recordsets from closed
workbooks, and so is how I would perform this task.

This approach, of course, requires that the columns in the source files
have headings (field names) that you can use in the SQL statement.
Otherwise, Claus' example is an excellent alternative, IMO!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Monday, December 2, 2013 7:33:26 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Mon, 2 Dec 2013 04:12:42 -0800 (PST) schrieb Howard:



I'll need to make some notes to myself on that, plus some study time.



with a second loop the code will be more compact:



Sub MondayMornCopy3()



Dim LRow As Long 'Last row

Dim varCol As Variant 'Array of columns

Dim varOut As Variant 'Array of data

Dim copyArr As Variant 'Array of workbooks

Dim i As Long 'Counter for workbook array

Dim j As Integer 'Counter for columns array



Const myPath = "C:\Users\Howard Kittle\Documents\"

copyArr = Array("Idaho", "Montana", "Wyoming", "Nebraska")

varCol = Array(1, 4, 6, 10)



Application.ScreenUpdating = False



For i = LBound(copyArr) To UBound(copyArr)

Workbooks.Open myPath & copyArr(i) & ".xlsm"

With ActiveWorkbook.Sheets("Sheet1")

For j = LBound(varCol) To UBound(varCol)

LRow = .Cells(.Rows.Count, varCol(j)).End(xlUp).Row

varOut = .Range(.Cells(1, varCol(j)), .Cells(LRow, varCol(j)))

Workbooks("Master.xlsm").Sheets("Sheet1") _

.Cells(Rows.Count, varCol(j)).End(xlUp)(2) _

.Resize(rowsize:=LRow) = varOut

Next j

ActiveWorkbook.Close savechanges:=True

End With

Next i

Application.ScreenUpdating = True

End Sub





Regards

Claus B.

Well, for what its worth I did indeed think at the beginning of this little project the use of an array inside an array (if that is the way to say it) might be a way to go.

But I could never get beyond thinking it was a way to go.

I'll give this a go, and see if I can make some sense out of the code structure.

Many thanks.
Howard

On Monday, December 2, 2013 6:53:21 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Mon, 2 Dec 2013 04:12:42 -0800 (PST) schrieb Howard:



I'll need to make some notes to myself on that, plus some study time.



may I write comments next time?





Regards

Claus B.


For sure. I take it you mean within the code as to what this line does and why it is important to do such and such etc. I try to google stuff a lot and sometimes there is tons of info and sometimes very little.

That would also be a help to me when I revisit my archived code suggestions of yours and find I don't remember what the code is doing.

And all to the extent you have the time to make the notes, either in the code or following it. As long as it is not a burden to you.

Thanks,
Howard

On Sunday, December 1, 2013 11:51:19 PM UTC-8, Howard wrote:
Code seems to be okay until the first copy line which errors out in yellow.



Opens the four "state named" workbooks okay.



This line, one line up from error line shows lCol4 = 10 when cursor is hovered over it, which is correct.



Set rangeJ = Range("J1:J" & lCol4)



Is my syntax wrong with the copy lines?



Thanks.

Howard





Option Explicit



Sub MondayMornCopy()



'Idaho, Montana, Wyoming, Nebraska

Dim Idaho As Workbook, Montana As Workbook, Wyoming As Workbook, Nebraska As Workbook

Dim lCol1 As Long, lCol2 As Long, lCol3 As Long, lCol4 As Long

Dim rangeA As Range, rangeD As Range, rangeF As Range, rangeJ As Range

Dim copyArr As Variant

Dim i As Long



Workbooks.Open Filename:= _

"C:\Users\Howard Kittle\Documents\Idaho.xlsm"

Workbooks.Open Filename:= _

"C:\Users\Howard Kittle\Documents\Montana.xlsm"

Workbooks.Open Filename:= _

"C:\Users\Howard Kittle\Documents\Wyoming.xlsm"

Workbooks.Open Filename:= _

"C:\Users\Howard Kittle\Documents\Nebraska.xlsm"



lCol1 = Cells(Rows.Count, 1).End(xlUp).Row

lCol2 = Cells(Rows.Count, 4).End(xlUp).Row

lCol3 = Cells(Rows.Count, 6).End(xlUp).Row

lCol4 = Cells(Rows.Count, 10).End(xlUp).Row



Application.ScreenUpdating = False



copyArr = Array(Idaho, Montana, Wyoming, Nebraska)

For i = LBound(copyArr) To UBound(copyArr)



With copyArr(i)

Set rangeA = Range("A1:A" & lCol1)

Set rangeD = Range("D1:D" & lCol2)

Set rangeF = Range("F1:F" & lCol3)

Set rangeJ = Range("J1:J" & lCol4)



Workbooks("Master.xlsm").Sheets("Sheet1").Range("A " & Rows.Count).End(xlUp)(2) = copyArr(i).rangeA

Workbooks("Master.xlsm").Sheets("Sheet1").Range("D " & Rows.Count).End(xlUp)(2) = copyArr(i).rangeD

Workbooks("Master.xlsm").Sheets("Sheet1").Range("F " & Rows.Count).End(xlUp)(2) = copyArr(i).rangeF

Workbooks("Master.xlsm").Sheets("Sheet1").Range("J " & Rows.Count).End(xlUp)(2) = copyArr(i).rangeJ



copyArr(i).Save

copyArr(i).Close

End With



Next

Application.ScreenUpdating = True

End Sub

On Monday, December 2, 2013 9:37:02 AM UTC-8, GS wrote:
I'm inclined to go a different way when pulling data from more than 1

unopen file. ADODB allows you to pull data in recordsets from closed

workbooks, and so is how I would perform this task.



This approach, of course, requires that the columns in the source files

have headings (field names) that you can use in the SQL statement.

Otherwise, Claus' example is an excellent alternative, IMO!



--

Garry


Hi Garry,

I, for sure, know your stuff works well also.

I don't have a clue what ADODB is, and I see SQL a lot, but also don't know what it is either. I might be using it and don't know it.

As you know, I can be in over my head in just about no time with some of this.

I'll keep plugging away and try not to be too frustrating.

Howard

On Monday, December 2, 2013 9:37:02 AM UTC-8, GS wrote:
I'm inclined to go a different way when pulling data from more than
1

unopen file. ADODB allows you to pull data in recordsets from closed

workbooks, and so is how I would perform this task.



This approach, of course, requires that the columns in the source
files

have headings (field names) that you can use in the SQL statement.

Otherwise, Claus' example is an excellent alternative, IMO!



--

Garry


Hi Garry,

I, for sure, know your stuff works well also.

I don't have a clue what ADODB is, and I see SQL a lot, but also
don't know what it is either. I might be using it and don't know it.

As you know, I can be in over my head in just about no time with some
of this.

I'll keep plugging away and try not to be too frustrating.

Howard

Here's a good 'primer' with example code...

http://www.appspro.com/conference/Da...rogramming.zip

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

On Monday, December 2, 2013 10:00:31 AM UTC-8, GS wrote:
On Monday, December 2, 2013 9:37:02 AM UTC-8, GS wrote:

I'm inclined to go a different way when pulling data from more than

1



unopen file. ADODB allows you to pull data in recordsets from closed



workbooks, and so is how I would perform this task.







This approach, of course, requires that the columns in the source

files



have headings (field names) that you can use in the SQL statement.



Otherwise, Claus' example is an excellent alternative, IMO!







--



Garry





Hi Garry,



I, for sure, know your stuff works well also.



I don't have a clue what ADODB is, and I see SQL a lot, but also

don't know what it is either. I might be using it and don't know it.



As you know, I can be in over my head in just about no time with some

of this.



I'll keep plugging away and try not to be too frustrating.



Howard



Here's a good 'primer' with example code...



http://www.appspro.com/conference/Da...rogramming.zip



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com

Okay, thanks.

Howard