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

 

[SkipVought]

Hi,

Turn on your macro recorder and record opening the upload workbook and pasting.

In your posted code, I would recommend referencing ALL sheets...

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

'
    [b]Sheets("WhatSheetName")[/b].Range("A48:D59").Copy
    With Workbooks("C4SHEET.xls")
        .Sheets("12501").Range("A25").PasteSpecial xlPasteValues
        .SelectedSheets.PrintOut Copies:=1, Collate:=True
    End With
End Sub

Skip,

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

 

[ceil32]

Sorry for the delay in responding Skip.

I've tried what you suggested but I've had no luck.

There are three different Macro's that need to be changed to do the same thing.

I'll paste the code from them in below:
-------------------

'
' only1518 Macro

'
Sub only1518()
Sheets("1518").Select
Range("A4656").Select
Selection.Copy
Windows("copy of C4SHEET.XLS").Activate
Sheets("only1518").Select
ActiveWindow.LargeScroll Down:=2
Range("A24").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="print_1518"
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub


'
' NiBSi_CrFe Macro
'
'
Sub NiBSi_CrFe()
Selection.Copy
Windows("copy of C4SHEET.XLS").Activate
Sheets("NiBSi--CrFe").Select
ActiveWindow.LargeScroll Down:=1
Range("A24").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Reference:="print_NiBSi"
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SmallScroll Down:=21
Range("C24").Select
End Sub


'
' Fe_base Macro
'
'
Sub Fe_base()
Selection.Copy
Windows("copy of C4SHEET.XLS").Activate
Sheets("Fe base").Select
ActiveWindow.LargeScroll Down:=2
Range("A24").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.Goto Reference:="Fe_base"
Application.CutCopyMode = False
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.LargeScroll Down:=2
Range("C24").Select
End Sub

 

[SkipVought]

Please explain exactly what the problem is.

What should I be looking for?

Skip,

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

 

[ceil32]

There is no problem as such - I just want to modify each of the three macro's to copy the same selection of data to a file that will be saved in the same location called upload.xls

 

[SkipVought]

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

With Windows("copy of C4SHEET.XLS").Sheets("only1518")
.Range("A24").PasteSpecial Paste:=xlValues

.PageSetup.PrintArea = Range("print_1518").Address
.PrintOut Copies:=1
End With
End Sub


Sub NiBSi_CrFe()
'this statement is very dangerous!!!
'Unless the user is knowingly and deliberately making a selection prior to executing this procedure,
'this reference should be explicit
' Selection.Copy

With Windows("copy of C4SHEET.XLS").Sheets("NiBSi--CrFe")
.Range("A24").PasteSpecial Paste:=xlValues

.PageSetup.PrintArea = Range("print_NiBSi").Address
.PrintOut Copies:=1

.Range("C24").Select
End With
End Sub

Skip,

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

 

[ceil32]

Cheers Skip - so if I use this code instead of the existing code then it will copy the data to upload.xls too??

 

[SkipVought]

Not unless you code it to paste it there.

Skip,

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

 

[ceil32]

Can you assist with this??

 

[SkipVought]

If you observed the code I posted, there is a pattern:

A Workbook is referenced, a Worksheet is referenced and a Range is referenced.

Skip,

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

 

[ceil32]

I've edited the three Macro's code as follows:

---

Here's the full thing - when I run it I get

Compile error:

Expected End With

It highlights the final 'End Sub' in yellow

I also get:

I get this error on the red line above:

Compile error:
Expected : list seperator or )

On the first instance of:

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


'
' only1518 Macro
' Macro recorded 28/10/10 by X.
'
'
Sub only1518()
ThisWorkbook.Sheets("1518").Range("A4656).Copy

With Windows("copy of C4SHEET.XLS").Sheets("only1518")
.Range("A24").PasteSpecial Paste:=xlValues

With Windows("DEMupload.XLS").Sheets("DEM")
.Range("A24").PasteSpecial Paste:=xlValues

.PageSetup.PrintArea = Range("print_1518").Address
.PrintOut Copies:=1
End With

End Sub



'
' NiBSi_CrFe Macro
' Macro recorded 28/10/10 by X
'
'
Sub NiBSi_CrFe()
With Windows("copy of C4SHEET.XLS").Sheets("NiBSi--CrFe")
.Range("A24").PasteSpecial Paste:=xlValues

With Windows("DEMupload.XLS").Sheets("DEM")
.Range("A24").PasteSpecial Paste:=xlValues

.PageSetup.PrintArea = Range("print_NiBSi").Address
.PrintOut Copies:=1

.Range("C24").Select
End With

End Sub


'
' Fe_base Macro
' Macro recorded 28/10/10 by x.
'
'
Sub Fe_base()

With Windows("copy of C4SHEET.XLS").Sheets("Fe base")
.Range("A24").PasteSpecial Paste:=xlValues

With Windows("DEMupload.XLS").Sheets("DEM")
.Range("A24").PasteSpecial Paste:=xlValues

.PageSetup.PrintArea = Range("print_Fe base").Address
.PrintOut Copies:=1

.Range("C24").Select
End With

End Sub

 

[PHV]

Sub only1518()
ThisWorkbook.Sheets("1518").Range("A4656[!]"[/!]).Copy
With Windows("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

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

 

[ceil32]

That's fixed the first error, getting this error with the second two Macro's:

"Compile error:

Expected End With"

The Macro name is highlighted in yellow and the last End Sub is highlighted Blue

 

[SkipVought]

Did you FIX the similar coding errors, as PHV highlighted, in those two?

Apparently NOT!

Skip,

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

 

[ceil32]

Edited that.

Now getting:

"Run time error 438.

Object doesn't support this property or method.

 

[SkipVought]

Posting an error message means NOTHING unless the associated code is ALSO posted.

The message also refers to an object, property and method. So you need to share THAT also.

Skip,

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

 

[ceil32]

'
' only1518 Macro
' Macro recorded 28/10/10 by Niall Connaughton.
'
'
Sub only1518()
ThisWorkbook.Sheets("1518").Range("A4656").Copy
With Windows("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


'
' NiBSi_CrFe Macro
' Macro recorded 28/10/10 by Niall Connaughton
'
'
Sub NiBSi_CrFe()
With Windows("copy of C4SHEET.XLS").Sheets("NiBSi--CrFe")
.Range("A24").PasteSpecial Paste:=xlValues
End With
With Windows("DEMupload.XLS").Sheets("DEM")
.Range("A24").PasteSpecial Paste:=xlValues
.PageSetup.PrintArea = Range("print_NiBSi").Address
.PrintOut Copies:=1
.Range("C24").Select
End With

End Sub

'
' Fe_base Macro
' Macro recorded 20/08/97 by Gateway 2000 Licensed User.
'
'
Sub Fe_base()

With Windows("copy of C4SHEET.XLS").Sheets("Fe base")
.Range("A24").PasteSpecial Paste:=xlValues
End With
With Windows("DEMupload.XLS").Sheets("DEM")
.Range("A24").PasteSpecial Paste:=xlValues

.PageSetup.PrintArea = Range("print_Fe base").Address
.PrintOut Copies:=1

.Range("C24").Select

End With

End Sub

---------

The following line is highlighted in yellow

With Windows("copy of C4SHEET.XLS").Sheets("Fe base")

Error:


"Run time error 438.

Object doesn't support this property or method."

 

[SkipVought]

Well it could be one of several things...

1. is copy of C4SHEET.XLS in your current instance of Excel. ie, can you find this workbook in the Windows list in Excel?

2. is copy of C4SHEET.XLS the correct spelling?

3. does that workbook contain sheet Fe base spelled that way exactly?



Skip,

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

 

[ceil32]

1. is copy of C4SHEET.XLS in your current instance of Excel. ie, can you find this workbook in the Windows list in Excel?

Yes - 'copy of C4SHEET.XLS is open & listed in my current instance of Excel

2. is copy of C4SHEET.XLS the correct spelling?

It is spelt 'Copy of C4SHEET.XLS' - I edited the code to include an Uppercase C - same error

3. does that workbook contain sheet Fe base spelled that way exactly?

Workbook 'Fe base' in contained within this file and is spelt this way

Thanks for the ongoing assistance

 

[SkipVought]

Yes - 'copy of C4SHEET.XLS is open & listed in my current instance of Excel

And the current instance of Excel is the instance running the code?

Are you absolutely certain, that the name of the sheet does not have leading or trailing SPACE(S).

These would be the ONLY causes for such an error. SPELLING-EXACT MATCH

Skip,

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