Macro to update daily tracking [copy data from 1 workbook to another]

**excelnewbster** · 11-07-2012, 03:12 PM

I'm trying to create a macro that would copy certain data points from one workbook (workbook A) and then insert a new row in another workbook (workbook B) and paste in those data points.

I tried the record macro function from the developer tab and everything worked only except that when I close both workbooks and open them up again to replicate it, I get an Error 9 (out of range).
Any ideas?

**scottmoss** · 11-07-2012, 03:40 PM

Hi,

If you could paste the code in (if not too long) or attach the workbook then i'll take a look for you.

Thanks

**excelnewbster** · 11-07-2012, 03:48 PM

Thanks for your reply. I actually think I've located the problem but don't know how to fix it:

Here is the code:
The workbooks are:
1.Windows("ESSN_Attainment_Flash_11062012 (5).xlsx").Activate
2. Windows("Q113 Booking.xlsx").Activate

The problem is that if you close file number 1 and reopen it, it changes the number right before it (5). As in if I close it and open it again it changes to (6) and therefore gives an error. The file is not on my harddrive but I am downloading it from a server. Is there a way for the code to take this into account?

Also for this to work on a daily basis I would need the macro to know the date changes of the file for ESSN. ie; from 11062012 to 11072012 tomorrow and so forth.

Sub ISS()
'
' ISS Macro
'

'
Rows("11:11").Select
Range("B11").Activate
Selection.Insert Shift:=xlDown
Range("B11").Select
ActiveCell.FormulaR1C1 = "11/6/2012"
Range("C10:C11").Select
Selection.FillDown
Range("D11").Select
Windows("ESSN_Attainment_Flash_11062012 (5).xlsx").Activate
Range("AW82,BC82").Select
Range("BC82").Activate
Selection.Copy
Windows("Q113 Booking.xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("ESSN_Attainment_Flash_11062012 (5).xlsx").Activate
Range("CA82").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Q113 Booking.xlsx").Activate
Range("F11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("ESSN_Attainment_Flash_11062012 (5).xlsx").Activate
Range("CM82,CS82").Select
Range("CS82").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("Q113 Booking.xlsx").Activate
Range("G11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I10:K11").Select
Application.CutCopyMode = False
Selection.FillDown
Range("J12").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
Range("J13").Select
End Sub

**Naveed Raza** · 11-08-2012, 03:38 AM

Hi,

VBA Macro will not work in ".xlsx" format , save the workbook in ".xlsm" (Macro Enable Workbook). I have not go through ur code but u hv saved the workbook in ".xlsx" format in this format macro will not work.

if u attached example workbook then its more helpfull to solve ur query bcz in ur code u hv selected rows("11:11") and inserting row before B11 if u run the macro , macro will enter Row always before B11 i think u got my point.

Thanks,
MNR.