I am getting two run-time errors while running below mentioned VBA code

Question

I am getting the following error "Run-time error '1004' Application-defined or object defined error and highlighting the following line of code newb.sheets(shts(s)).Range("a1").Resize(, cols).Value = wb.sheets(shts(s)).Range(heds(s)).Value

When I skip the above line of code and move to the next one it is showing another error "Run-time error '9' script out of range" and highlighting the following line of code "newb.sheets(shts(s)).Range("a" & rw(s)).Resize(rws, cols).Value = wb.sheets(s).Range(Rng(s) & rws - 1).Value" Please help me what to do.

Can someone please help me to fix above mentioned errors?

 Sub Consolidation()

 Dim newb as workbook
 Dim wb as workbook
 Dim Shts
 Dim rws as long
 Dim rw(2) As Long
 mypath = "C:\Consolidation\"
 shts = Array("Total Consolidation", "State level Consolidation", "District Level Consolidation")

 Set newb = Workbooks.Add
 newb.Sheets(1).Name = shts(0)
 newb.Sheets(2).Name = shts(1)
 newb.Sheets(3).Name = shts(2)

 rw(0) = 1
 rw(1) = 1
 rw(2) = 1

 rng = Array("a6:k", "a2:g", "o2:aa")
 cols = Array("11", "7", "13")
 heds = Array("a1:k1", "a1:g1", "o1:aa1")
 fname = Dir(mypath & "*.xls")

 Do While Len(fname) > 0
     Set Wb = Workbooks.Open(mypath & fname)
 For s = 0 To 2
     rws = Wb.Sheets(shts(s)).UsedRange.Rows.Count - 1

If s = 0 Then rws = rws - 6

If Not headsdone Then
    newb.Sheets(shts(s)).Range("a1").Resize(, cols(s)).value = Wb.Sheets(shts(s)).Range(heds(s)).value
    headsdone = True
End If

newb.Sheets(shts(s)).Range("a" & rw(s)).Resize(rws, cols(s)).value = Wb.Sheets(shts(s)).Range(rng(s) & rws).value

rw(s) = rw(s) + rws - 1

Next

fname = Dir

Loop

newb.SaveAs mypath & "COUNTRY LEVEL CONSOLIDATION.xls"

newb.Close

End Sub

What is rng(s) & rws - 1 supposed to be doing? seems odd that you would be making a range address in this fashion. — Brad
– Brad, Commented Jan 13, 2014 at 15:54
also can you do something like dim rightSide as vaiant, leftSide as vaiant: set rightSide = Wb.Sheets(shts(s)).Range(heds(s)): set leftSide = newb.Sheets(shts(s)).Range("a1").Resize(, cols). What are leftSide and rightSide equal to? — Brad
– Brad, Commented Jan 13, 2014 at 15:57
Those are the rows range in three worksheets that need to be copied — vbalearner
– vbalearner, Commented Jan 13, 2014 at 15:58
I am currently having 55 workbooks in a folder (ex. “C:\Consolidation”) and each workbook contains 11 worksheets. In that I need to consolidate the following three sheets 1. Total Consolidation, 2. State level Consolidation and 3. District Level Consolidation from all 55 workbooks and save it in three different sheets in a new workbook in the same folder (i.e. C:\Consolidation). — vbalearner
– vbalearner, Commented Jan 13, 2014 at 16:00

Siddharth Rout · Accepted Answer · 2014-01-13 15:57:26Z

1

You are getting that error because of Cols in Resize(, cols) . Cols is an array and not an Integer/Long Value.

cols = Array("11", "7", "13")

What you want is to pick up a value from the array like Cols(0) so that it picks up either the 11 or 7 or the 13

For example

newb.Sheets(shts(s)).Range("a1").Resize(, cols(0)).value

answered Jan 13, 2014 at 15:57

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

vbalearner Over a year ago

Thanks. Let me check this.

vbalearner Over a year ago

When I use numbers 7, 11 or 13, I am getting another error "Subscript out of range. Also if I put Cols(s) or Cols(0) no data being copied from the source sheet to new sheet

Siddharth Rout Over a year ago

You are getting Subscript out of range because Excel cannot find the relevant object. For example Sheets(shts(s)). Ensure the sheet name matches with the actual sheet name. Ensure that there are no unwanted spaces etc...

vbalearner Over a year ago

I am completely lost. I have checked the names of the sheets several times and everything is file, but still I am getting this error.

vbalearner Over a year ago

During the step through process, when I place the cursor on the code, it is showing the names correctly, but data not being copied into the new sheets.

|

vbalearner · Accepted Answer · 2014-01-14 08:37:22Z

I could able to fix all errors. Please find the final code below.

 Sub consolidation()

 Dim newb As Workbook
 Dim wb As Workbook
 Dim rws As Long
 Dim shts
 Dim rw(2) As Long
 Dim s As Integer

 mypath = "C:\Consolidation\"
 shts = Array("Total Consolidation", "State level Consolidation", "District Level Consolidation")
 Set newb = Workbooks.Add

 newb.sheets(1).Name = shts(0)
 newb.sheets(2).Name = shts(1)
 newb.sheets(3).Name = shts(2)

 rw(0) = 1
 rw(1) = 1
 rw(2) = 1

 Rng = Array("a7:k", "a2:g", "o2:aa")
 cols = Array("11", "7", "13")
 heds = Array("a6:k6", "a1:g1", "o1:aa1")
 fname = Dir(mypath & "*.xls")

 Do While Len(fname) > 0
 Set wb = Workbooks.Open(mypath & fname)
 For s = 0 To 2
 rws = wb.sheets(shts(s)).UsedRange.Rows.Count

 Headsdone = True

 If Headsdone Then
    newb.sheets(shts(s)).Range("a1").Resize(, cols(s)).Value = wb.sheets(shts(s)).Range(heds(s)).Value
 End If

 If s = 0 Then
 newb.sheets(shts(s)).Range("a" & rw(s) + 1).Resize(-6 + rws, cols(s)).Value = wb.sheets(shts(s)).Range(Rng(s) & rws).Value
 rw(s) = rw(s) + rws - 6
 End If

 If s = 1 Then
 newb.sheets(shts(s)).Range("a" & rw(s) + 1).Resize(-1 + rws, cols(s)).Value = wb.sheets(shts(s)).Range(Rng(s) & rws).Value
 rw(s) = rw(s) + rws - 1
 End If

 If s = 2 Then
 newb.sheets(shts(s)).Range("a" & rw(s) + 1).Resize(-1 + rws, cols(s)).Value = wb.sheets(shts(s)).Range(Rng(s) & rws).Value
 rw(s) = rw(s) + rws - 1
 End If

 Next

 wb.Close False
 fname = Dir

 Loop
 newb.SaveAs mypath & "COUNTRY LEVEL CONSOLIDATION.xlsx"

 newb.Close

 End Sub

Collectives™ on Stack Overflow

I am getting two run-time errors while running below mentioned VBA code

2 Answers 2

22 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

22 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related