HELP wanted to copy data from an Exell sheet to another Excel workbook

[Octonet]

Hi everybody,
I have an Excel workbook with several sheets.
I want to copy a range of data to a certain range in another workbook.
Both workbooks are in the same directory.
Example : If a certain cell in the first workbook has value "1", then copy from sheet LayoutF56 the range from W16 to W69, open the other workbook (F57.xlsx), paste the copied data to G2.
Then save and close F57.xlsx
After this, : return to starting sheet.

The problem is that I always run into "Error 9 - Subscript out of range" at line "Windows("F57.xlsx").Select"
I spend hours trying to fix it....... no luck.

I make the macro and write the code and test it in Office 2019 pro. It has to work in Office 2013 too.

I am no expert like the people over here

Sub CopyF57()
'
' CopyF57 Macro
' Copies data naar F57 workbook
'

'
If Sheets("HomeF56").Range("J8").Value = 1 Then
Application.ScreenUpdating = False
Sheets("LayoutF56").Select
Range("W16:W69").Select
Selection.Copy
Windows("F57.xlsx").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H2").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("HomeF57").Select
End If
End Sub

 

[Dan_W]

Hi, you're almost there. I've made a few slight changes to your code below - I would make a few more, but I figured this would be fine.

I couldn't be certain if the F57.xlsx workbook was already open when this code was being run, but if it isn't, then I have included a part which opens the workbook for you - you just need to delete the apostrophe in the sentence: ' Application.Workbooks......

Let me know if it does what you need it to...

If Sheets("HomeF56").Range("J8").value = 1 Then
    Application.ScreenUpdating = False
    Sheets("LayoutF56").Range("W16:W69").Copy

    ' If the F57.xlsx workbook isn't already open then delete the apostrophe in the following line:
    ' Application.Workbooks.Open "C:\INSERT_PATH_OF_FILE\F57.xlsx"

    Workbooks("F57.xlsx").Range("G2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    Workbooks("F57.xlsx").Save
    Workbooks("F57.xlsx").Close
    Application.ScreenUpdating = True
End If
End Sub

 

[Octonet]

Dan, GREAT
This works perfect.
Question : If both workbooks are in the same directory, must I specify the full path of the destination file ?
This could be ****ty if I have to work and test on another computer.
Is there any work arround ?
If not, just a small problem. I'm happy that my extentions to the workbook work perfect thanks to you.

 

[Dan_W]

Hi.

My apologies - you did clearly say that in your original post ("Both workbooks are in the same directory.").
You're absolutely right - the full path of the destination file does not need to be specified if both workbooks are in the same directory.

Glad that it's working the way you wanted. Did you see what I did with all the .Select statements and understand why I deleted them? Hope it makes sense.

 

[Fluff]

It's always best to declare the full path, but as the files are in the same directory, you can use

     Application.Workbooks.Open ActiveWorkbook.Path & "\F57.xlsx"

 

[Fluff]

Cross posted Problem to copy data from an Excel sheet to another workbook. HELP please

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Octonet]

Thanks Fluff, works perfect for me.

Tread can be closed.

 

[Fluff]

Glad we could help & thanks for the feedback