Edit Macro to copy data to second Excel file 2

[ceil32]

I have a Macro that copies a selected range of data from part on an Excel file into a different Excel file and prints the copied data to the default printer.

I would like to modify the Macro to also copy the data to a second excel file - the same file and worksheet each time - and for this data to be overwritten each time the Macro is ran. The new file is called 'Upload.xls' and is located in the same folder as the other files

Can anyone assist? the VB Code is below

The Macro works on any version of Excel:

Thanks in advance

-----------



Sub only8elements()
'
' only12501 Macro
' Macro recorded 03/09/2007 by X
'

'
Range("A4859").Select
Selection.Copy
Windows("C4SHEET.xls").Activate
Sheets("12501").Select
Range("A25").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B26").Select
ActiveWindow.LargeScroll Down:=-1
Range("A1:J19").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

 

[PHV]

Well, I'm not sure an Excel.Window object exposes a Sheets collection ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ceil32]

I've triple checked and all names match and this is the instance running the code.

 

[SkipVought]

I never use the Windows Collection, but I assumed, perhaps wrongly.

So instead of Windows(Workbook Name)

use Workbooks{/b](Workbook Name)

But this error, seemed to happen on a subsequent macro, where OTHERS WORKED!?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceil32]

I changed all three Macro's from Windows.workbook name to Workbooks.woorbookname

e.g: With Workbooks("C4SHEET.xls").Sheets("Fe base")
.Range("A24").PasteSpecial Paste:=xlValues

All three macros have the same format.


When I run 'Fe base', I get the following error:

"Run-time error '1004'
PasteSpecial method of Range class failed."

The following code is highlighted yellow

".Range("A24").PasteSpecial Paste:=xlValues"


Exact same error for 'NiBSi_CrFe'

And when I run 'only1518', I get the following error:

"Run-time error '9'. Subscript out of range"

The following line of code is highlighted yellow:

"ThisWorkbook.Sheets("1518").Range("A4656").Copy"

 

[SkipVought]

You probably have nothing in your Clipboard.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceil32]

Any way I can fix that in the Macro??

 

[SkipVought]

Copy before you paste.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceil32]

Can anyone else assist??

 

[SkipVought]

Did you TRY to RE COPY the range, before the paste?

Help yourself out!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceil32]

I'm editing an existing macro which is all automated - I do not physically copy anything.

 

[SkipVought]

Did you TRY to RE COPY the range, before the paste, in your macro?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceil32]

I've tried manually copying the range of cells and then running the Macro - I get the exact same error as above:

e.g: With Workbooks("C4SHEET.xls").Sheets("Fe base")
.Range("A24").PasteSpecial Paste:=xlValues

All three macros have the same format.


When I run 'Fe base', I get the following error:

"Run-time error '1004'
PasteSpecial method of Range class failed."

The following code is highlighted yellow

".Range("A24").PasteSpecial Paste:=xlValues"


Exact same error for 'NiBSi_CrFe'

And when I run 'only1518', I get the following error:

"Run-time error '9'. Subscript out of range"

The following line of code is highlighted yellow:

"ThisWorkbook.Sheets("1518").Range("A4656").Copy"

 

[PHV]

Seems like the workbook that contains the only1518 procedure don't have a sheet named 1518.

Anyway, avoid a numeric sheet name.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ceil32]

The workbook '1518' with no leading spaces is definitely there

The code refers to:

Sub only1518()
ThisWorkbook.Sheets("1518").Range("A4656").Copy
With Workbooks("Copy of C4SHEET.XLS").Sheets("only1518")
.Range("A24").PasteSpecial Paste:=xlValues
End With
With Windows("DEMupload.XLS").Sheets("DEM")
.Range("A24").PasteSpecial Paste:=xlValues
.PageSetup.PrintArea = Range("print_1518").Address
.PrintOut Copies:=1
End With
End Sub

---------

So the file 'Copy of C4.xls' has a worksheet called 'only1518' and the range I want to copy exists.

 

[ceil32]

Anyone else got any suggestions??

 

[SkipVought]

   With Workbooks("[b]Copy of C4[red]SHEET[/red].XLS[/b]").Sheets("only1518")

So the file 'Copy of C4.xls' has a worksheet called 'only1518' and the range I want to copy exists.

[red]THIS[/red] is why I have a difficult time believing that it is what you say it is.

"Run-time error '9'. Subscript out of range" comes from a MISSPELLED or missing reference IN YOUR CODE. No amount of "any suggestions" will make the solution any different.

CHECK! RECHECK and CHECK AGAIN...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ceil32]

I've checked over & over - I've renamed the files with no spaces, renamed the worksheets - the files & worksheets exist as in the code

 

[SkipVought]

You're gonna have to be your own sleuth...

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}