Copy and Paste to another Workbook .. Subscript Error?

[Rpax]

Hi,

I was wondering if you can help. I'am trying to open two excel files and copy cells from one to another. The files are opening but I'am getting a "Subscript out of range" when I try to copy the cells. From reading up on this, it seems that I need to break down the code further to a more readable format. Any suggestions so I can avoid the problem? . Thanks - Rpax

MyDateFormat = Year(now) & Right("0" & Month(Now), 2) & Right("0" & Day(now), 2)
Set Excel = CreateObject("Excel.Application")
Set ExcelNew = CreateObject("Excel.Application")

ExcelNew.Application.DisplayAlerts = False
ExcelNew.Application.Visible = True
ExcelNew.Workbooks.Open("C:\Program Files\Tank_Reports\Solvents"& ".xls")

Excel.Application.DisplayAlerts = False
Excel.Application.Visible = True
Excel.Workbooks.Open("C:\Program Files\Tanks.xls")
wscript.sleep(5000) 

[B]Excel.Workbooks("C:\Program Files\Tanks.xls").Worksheets("Sheet1").Range("A1:C27").Copy
ExcelNew.Workbooks("C:\Program Files\Tank_Reports\Solvents"& ".xls").Worksheets("Sheet1").Range("A1")[/B]

ExcelNew.Activeworkbook.SaveAs "C:\Program Files\Tank_Reports\Solvents_" & MyDateFormat & ".xls"

Excel.Activeworkbook.Close
ExcelNew.Activeworkbook.Close
Excel.Quit
WScript.Quit

 

[NdNoviceHlp]

You only need 1 XL application which can be used to open 2 wbs. As for your code, U seem to be missing the underscore after the copy line. HTH. Dave

Excel.Workbooks("C:\Program Files\Tanks.xls").Worksheets("Sheet1").Range("A1:C27").Copy _
ExcelNew.Workbooks("C:\Program Files\Tank_Reports\Solvents" & ".xls").Worksheets("Sheet1").Range("A1")

 

[Rpax]

Hi Dave,

Thanks for replying. I've changed as you have suggested (see below) but unfortunately, I'am still getting the error.

When I check excel, I don't have the path Excel.Application.Workbooks().Worksheets .. is this the problem?. I've been changing this around to get it working and sometimes I get the VBS error "don't have the Method".

In general, when I have a worksheet open, what is the path to get access to the cells i.e. Excel.Application.ActiveWorkbook?? Also, do I need to put in a .paste at then end when pasting the cells?

Thanks again for the help - Rpax

MyDateFormat = Year(now) & Right("0" & Month(Now), 2) & Right("0" & Day(now), 2)
Set Excel = CreateObject("Excel.Application")
Set ExcelNew = CreateObject("Excel.Application")

Excel.Application.DisplayAlerts = False
Excel.Application.Visible = True
Excel.Workbooks.Open("C:\Program Files\Solvent_Tank_Reports\Solvents"& ".xls")

Excel.Application.DisplayAlerts = False
Excel.Application.Visible = True
Excel.Workbooks.Open("C:\Program Files\Solvent_Tanks.xls")
wscript.sleep(5000)

Excel.Workbooks("C:\Program Files\Tanks.xls").Worksheets("Sheet1").Range("A1:C27").Copy _
ExcelNew.Workbooks("C:\Program Files\Tank_Reports\Solvents"& ".xls").Worksheets("Sheet1").Range("A1")

ExcelNew.Activeworkbook.SaveAs "C:\Program Files\Solvent_Tank_Reports\Solvents_" & MyDateFormat & ".xls"

Excel.Activeworkbook.Close
ExcelNew.Activeworkbook.Close
Excel.Quit
WScript.Quit

 

[NdNoviceHlp]

I'm not familiar with VBS coding but you can trial this...

Dim wb1 As Excel.Workbook, wb2 As Excel.Workbook
MyDateFormat = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
Set Excel = CreateObject("Excel.Application")
'Set ExcelNew = CreateObject("Excel.Application")

Excel.Application.DisplayAlerts = False
'Excel.Application.Visible = True
Set wb1 = Excel.Workbooks.Open("C:\Program Files\Solvent_Tank_Reports\Solvents" & ".xls")

'Excel.Application.DisplayAlerts = False
'Excel.Application.Visible = True
Set wb2 = Excel.Workbooks.Open("C:\Program Files\Solvent_Tanks.xls")
'WScript.sleep (5000)

wb2.Worksheets("Sheet1").Range("A1:C27").Copy _
       Destination:=wb1.Worksheets("Sheet1").Range("A1")

wb1.SaveAs "C:\Program Files\Solvent_Tank_Reports\Solvents_" & MyDateFormat & ".xls"

wb2.Close
wb1.Close
Excel.Quit
WScript.Quit

HTH. Dave

 

[Rpax]

Hi Dave,

Many thanks for the help .. I've used your code and it is now copying the cells across so much appreciate the code above.

Just one last question. When I copy the cells across it takes the formulas etc and is still linked to a database in the first workbook. Do you know if it's possible just to take a snapshot of the raw data without the formulas in the background and remove the link. It doesn't necessarily need to be copied to\saved as a .xls file when I take the data. Any ideas or threads worth reading to overcome this?

Thanks again for the help - Rpax

 

[NdNoviceHlp]

Hi Rpax. I'm glad that sort of worked. U didn't mention that there may be underlying formulas. Anyways this should work. Dave
Replace this code....

wb2.Worksheets("Sheet1").Range("A1:C27").Copy _
       Destination:=wb1.Worksheets("Sheet1").Range("A1")

With this (add the variables to the top of your sub)...

Dim RngArr(1) As Variant, Rng As Range
With wb2.Worksheets("Sheet1")
Set Rng = .Range("A1:C27")
End With
RngArr(0) = Rng
With wb1.Worksheets("Sheet1")
.Range("A1:C27") = RngArr(0)
End With

 

[Rpax]

Hi Dave, just want to say thanks again .. this was spot on a worked a treat.

I have to admit, I'am not sure what's going on with the above code but I will play around with it to see what it's exactly doing. Appreciate the help - Rpax

 

[NdNoviceHlp]

Rpax you are welcome and thanks for posting your outcome. The code just loads the range into an array and then unloads it... it's also much faster than copy/paste. Have a nice day and stay safe. Dave