Open four Wbooks, copy four columns from each to Master Wbook

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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)
Click to expand...

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.
 
That works very nicely.

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

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
Click to expand...

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.
 
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...?
Click to expand...

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.
 
Hi Howard,



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






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.
Click to expand...

I completely overlooked that. Sorry, my bad.

Howard
 
Hi Howard,



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






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.
Click to expand...

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.
Click to expand...

may I write comments next time?


Regards
Claus B.
 
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.
Click to expand...

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.
 
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
 
Hi Howard,



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






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.
Click to expand...

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
 
Hi Howard,



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






may I write comments next time?





Regards

Claus B.
Click to expand...

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
 
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
Click to expand...
 
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!
Click to expand...

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
 
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
Click to expand...

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

http://www.appspro.com/conference/DatabaseProgramming.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
 
Back
Top