Error in copy dynamic range of cells

[rohitsaraf15]

I have the following files:

• There are multiple files in a specific folder. No. of files can be more than 40.
• Each file has 3 sheets (Sheet1, Sheet2 and Sheet3).
• Each sheet has multiple rows. Each row representing different month.
• I have to make different files for each month containing that month’s data from all the files available in the folder. I don’t have to copy the entire data of the row. For instance, if there are 6 columns (A to F column), I need only 4 columns (i.e. A to D column).
• I have written code to open all the files and creating as many files as the no. of months for which the data is available.
• However, when I am trying to copy monthly data from the file to new file, there is the following error:
• “Error No. 1004: application-defined or object-defined error”
• The extract of the code is as under:
• 
  
• For i = 1 To no_of_months ‘no_of_months is the total no. of months for which the data is available
• 
  
• 'entering data in bank sheet
• ‘following line is throwing the error
• 
  
• Workbooks(MyFile).Sheets("sheet1").Range(Cells(i + 1, 1), Cells(i + 1, 4)).Copy
• 
  
• ‘when I am giving static range in the above line, the paste special command is working fine.
• Workbooks("Consolidate_" & i).Sheets("bank").Range("C" & (count_file + 2)).PasteSpecial Paste:=xlPasteValues
• 
  
• ‘following lines are working properly
• 
  
• Workbooks("Consolidate_" & i).Sheets("bank").Cells(count_file + 2, 1).Value = count_file
• Workbooks("Consolidate_" & i).Sheets("bank").Cells(count_file + 2, 2).Value = Workbooks(MyFile).Sheets("sheet3").Range("a2").Value
• Workbooks("Consolidate_&i").Sheets("bank").Cells(count_file + 2, 7).Value = "=iferror(round(offset(bank!$a$1,row()-1,column()-2,1,1)/offset(bank!$a$1,row()-1,column()-3,1,1),4),0)"
• Next