Copy data in disperce cells from multiple excel workbooks..?

[midori323]

Hi, there,

I am new to VBA and just learned basics by watching VBA tutorials on Youtube...
I wanted to extract data in our company's weekly income report which is stored in disperse cells in summary income sheet..
There are 52 workbooks to refer to get this specific data from and I would like to paste data onto my master workbook sheet.

Here is the code I wrote:


Sub copyCellsFromMultipleWorkbooks()

Dim FolderPath As String, FilePath As String, FileName As String

FolderPath = "C:\Users\ahozumi\Desktop\Weekly Income Report 2015\"
FilePath = FolderPath & "*xlsx"
FileName = Dir(FilePath)
MySheet = "Report"

Do While FileName <> ""
Workbooks.Open (FolderPath & FileName)
ActiveSheet.Cells(("G26"), ("G40"), ("G51")).copy


ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(3, 0).Row
ActiveSheet.Paste Destination = Worksheets("Sheet1").Range(Cells(erow, 3), Cells(erow, 3).Offset(1, 3).Row, Cells(erow, 3).Offset(2, 3))
FileName = Dir
Loop

End Sub


When I run this program, I get "run-time error 450, Wrong number of arguments or invalid property assignment"
and debug function highlighted the sentence "ActiveSheet.Cells(("G26"),("G40"),("G51")) should be written...
How can I modify this part?

Thanks for your help!

[SamT]

you can't copy and paste three at once like that.

Statements with the Cells keyword must use the (Row, Column) style: Cells(26, 7) or Cells(26, "G")

The Paste Statement in your code is GIGO. I can't even guess where you are wanting to place the data. You might be trying to paste all the data from all the workbooks into Column "C", one piece below the other.

[midori323]

you can't copy and paste three at once like that.

Statements with the Cells keyword must use the (Row, Column) style: Cells(26, 7) or Cells(26, "G")

The Paste Statement in your code is GIGO. I can't even guess where you are wanting to place the data. You might be trying to paste all the data from all the workbooks into Column "C", one piece below the other.

Thank you, Sam.

The area I tried to paste data was originally in column "C" but those block of 3 data should be pasted leaving 3 blank cells from top for some
reason....
But as you suggested, I can just omit those 3 blank cells and chuck everything in one column without leaving any space..

So I rewrote the last code as follows:

erow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(3, 0).Row
ActiveSheet.Paste Destination = Worksheets("Sheet1").Range(Cells(erow, 3))

But I still get syntax error message for the definition of cells I want to copy data from....

ActiveSheet.Cells((28,"G"),(42,"G"),(52,"G"),(55,"G")).Copy

What did I miss...?

[SamT]

You can't copy three ranges at once. (Range("A1:C1") is a single Range)

It was not the three blanks under each paste operation that was a problem, it was trying to paste three separate Ranges at once.

Analyze this code:

Dim DestCell As Range
Dim MySht as worksheet
Set MySht = Sheets("Report")

'Start Loop thru workbooks here
Set DestCell = MyShtCells(Rows.Count, "C").End(xlUp).Offset(3, 0) 'Leave 3 blanks before each Workbook's data

With ActiveSheet
DestCell = .Range("G28")
DestCell.Offset(1)= .Range("G42")
DestCell.Offset(2, 0) = .Range("G52")
DestCell.Offset(3) = .Cells(55, 7)
End With

ActiveWorkbook.Close
'Finish looping thru workbooks here
Note that I showed two ways to write the Row offset and two ways to refer to a single Cell/Range

Note the DOTs before the Keywords Cells and Range inside the With brackets mean that the Ranges after the DOTs belong to the Sheet named by the With keyword.

[midori323]

Hi, Sam,


Thanks again for your tips.

I followed your advice and revised the code by adding declaration of
My sheet and DestCell.

So now the code looks like this:


Sub CopyCellsFromMultipleWorkbooks()
Dim FolderPath As String, FilePath As String, FileName As String
Dim DestCell As Range
Dim MySht As Worksheet
Set MySht = Sheets("Report")
FolderPath = "C:\Users\ahozumi\Desktop\Weekly Income Report 2015\"
FilePath = FolderPath & "*xlsx"
FileName = Dir(FilePath)
Do While FileName <> ""
Set DestCell = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Offset(3, 0)
With ActiveSheet(MySht)
DestCell = .Range("G28").copy
DestCell.Offset(1) = .Range("G42").copy
DestCell.Offset(2, 0) = .Range("G52").copy
DestCell.Offset(3) = .Cells(55, 7).copy
ActiveSheet.Paste DestCell
ActiveWorkbook.Close
End With
FileName = Dir
Loop
End Sub

And now I get run-time error 9, indicating subscript out of range...highlighting the part
'Set MySht=Sheets("Report")'

I wonder it happened because I added copy and paste into the code or some other issues..I searched
what caused run time error 9 and MS Visual Basic reference said it either the code referenced non-existent array
element or it declared an array but didn't specify the number of elements....I wonder what it means...

Sorry I tried to resolve the issue but still made not much progress here...






You can't copy three ranges at once. (Range("A1:C1") is a single Range)

It was not the three blanks under each paste operation that was a problem, it was trying to paste three separate Ranges at once.

Analyze this code:

Dim DestCell As Range
Dim MySht as worksheet
Set MySht = Sheets("Report")

'Start Loop thru workbooks here
Set DestCell = MyShtCells(Rows.Count, "C").End(xlUp).Offset(3, 0) 'Leave 3 blanks before each Workbook's data

With ActiveSheet
DestCell = .Range("G28")
DestCell.Offset(1)= .Range("G42")
DestCell.Offset(2, 0) = .Range("G52")
DestCell.Offset(3) = .Cells(55, 7)
End With

ActiveWorkbook.Close
'Finish looping thru workbooks here
Note that I showed two ways to write the Row offset and two ways to refer to a single Cell/Range

Note the DOTs before the Keywords Cells and Range inside the With brackets mean that the Ranges after the DOTs belong to the Sheet named by the With keyword.

[SamT]

And now I get run-time error 9, indicating subscript out of range...highlighting the part
'Set MySht=Sheets("Report")'That indicates that there is no sheet (in the workbook containing the macro) named exactly "Report". Check if the name is "report ".



DestCell = .Range("G28").copy
DestCell.Offset(1) = .Range("G42").copy
DestCell.Offset(2, 0) = .Range("G52").copy
DestCell.Offset(3) = .Cells(55, 7).copy

Did I use ".Copy"?

The Verbose version of the way I did it is

DestCell.Value = .Range("G28").Value
DestCell.Offset(1).Value = .Range("G42").Value
DestCell.Offset(2, 0).Value = .Range("G52").Value
DestCell.Offset(3).Value = .Cells(55, 7).Value

In order to use the Copy method that you love:
Tersely

.Range("G28").Copy DestCell
.Range("G42").Copy DestCell.Offset(1)
.Range("G52").Copy DestCell.Offset(2, 0)
.Cells(55, 7).Copy DestCell.Offset(3)

Verbosely, that is

.Range("G28").Copy Destination:=DestCell
.Range("G42").copy Destination:=DestCell.Offset(1)
.Range("G52").copy Destination:=DestCell.Offset(2, 0)
.Cells(55, 7).copy Destination:=DestCell.Offset(3)

Assuming that DestCell is "A1":
A1 = .Range("G28")
A2 = .Range("G42")
A3 = .Range("G52")
D1 = .Cells(55, 7)