Help with Macro to create workbook and copy data

[JV0710]

Hi
Please can I get some help with this query:

In my excel workbook I have a "Summary" sheet with data in range: A1:Z50
in Cell Z1 I have a "Name1"
in Cell Z3 I have "Name2"

I would like a Macro that will:

Find an Excel file in directory C:\
Name of file must be the name in Cell Z1 "Name1"

Create an additional sheet in that file
The Sheet name must be the name in Cell Z3 "Name2"

Then select data in Range A1:Z50 from my "summary sheet" and
Copy and Paste Data and Format to the sheet created in the file.

****************************************************

If the file with the name in cell Z1 "Name1", cannot be found, then create the file in directory C:\

Create an additional sheet in that file
The Sheet name must be the name in Cell Z3 "Name2"

Then select data in Range A1:Z50 from my "summary sheet" and
Copy and Paste Data and Format to the sheet created in the file.



Thank you

 

[Yard]

Try this:

Sub CopyData()
Dim wbkC As Workbook
Dim wksNew As Worksheet
Dim rngCopy As Range
Dim sFileName As String, sSheetName As String
Const sPath As String = "C:"

With Sheet1
    sFileName = .Range("Name1").Value
    sSheetName = .Range("Name2").Value
    Set rngCopy = .Range("A1:Z50")
End With

On Error Resume Next
Set wbkC = Workbooks.Open(sPath & sFileName)
On Error GoTo 0

If wbkC Is Nothing Then
    Set wbkC = Workbooks.Add
    wbkC.SaveAs sPath & sFileName
End If

On Error Resume Next
Set wksNew = wbkC.Worksheets(sSheetName)
On Error GoTo 0

If Not wksNew Is Nothing Then
    MsgBox "Sheet already exists"
    Exit Sub
Else
    Set wksNew = wbkC.Worksheets.Add
    wksNew.Name = sSheetName
    rngCopy.Copy
    wksNew.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
End If

End Sub

 

[JV0710]

Hi
Thank you for the fast response

I will give it a try and let you know

Thanks

JVN

 

[Yard]

Np

Note that

Sheet1

refers to the sheet's VBA codename rather than the name you see on the worksheet tab. See here for reference.

 

[JV0710]

Hi Yard

I have tried your code

1: The new workbook is created in the correct directory and the sheet is created with the correct name and the data is copied. The number formats are copied but I need the cell formatting to be copied as well.

2: When I have different data in my summary sheet and Name2 is different - When I run the macro, a NEW workbook is created. I do not want a new workbook to be created I need an extra sheet to be added to the workbook that has already been created from the first time I ran the macro. A new workbook must be created ONLY IF a workbook with "Name1" does not exist

Thank you

 

[Yard]

Try this:

Sub CopyData()
Dim wbkC As Workbook
Dim wksNew As Worksheet
Dim rngCopy As Range
Dim sFileName As String, sSheetName As String
Const sPath As String = "C:"

With Sheet1
    sFileName = .Range("Name1").Value
    sSheetName = .Range("Name2").Value
    Set rngCopy = .Range("A1:Z50")
End With

On Error Resume Next
'check if the destination workbook is already open
Set wbkC = Workbooks(sFileName)
On Error GoTo 0

'if it isn't open then
If wbkC Is Nothing Then
    On Error Resume Next
    'check if the workbook does exist
    Set wbkC = Workbooks.Open(sPath & sFileName)
    On Error GoTo 0
    
    'if it doesn't then create it anew
    If wbkC Is Nothing Then
        Set wbkC = Workbooks.Add
        wbkC.SaveAs sPath & sFileName
    End If
End If

On Error Resume Next
'check if the sheet already exists (just to prevent errors)
Set wksNew = wbkC.Worksheets(sSheetName)
On Error GoTo 0

If Not wksNew Is Nothing Then
    'if it already exists then get out
    MsgBox "Sheet already exists"
    Exit Sub
Else
    'otherwise add the new sheet
    Set wksNew = wbkC.Worksheets.Add
    wksNew.Name = sSheetName
    'and copy/paste the data
    rngCopy.Copy
    With wksNew.Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
End If

End Sub

 

[JV0710]

Hi Yard

Thank you

The Formatting issue is now fixed.

When I have different data in my summary and name2 is different - it does not create a new workbook now, but it seems to replace the sheet that was created the first time I ran the macro - This must only happen if there is already a sheet with the same name from my summary "Name2"

If there is no sheet in the workbook with the name from my summary sheet cell Z3 "Name2", then , I would like it to add an additional sheet to the workbook and name it what is now in cell Z3 "Name2"

So eventually there could be many sheets that have been copied into the one file

I am sorry that this is sounding more comlicated as we go

Thank you very much for your help

JVN

 

[Yard]

Confused.com

"seems to replace the sheet that was created the first time I ran the macro"

Unless I'm having an off day, every time you run the code, this happens (I tried to elucidate using comments in the code):

in the destination workbook it tries to refer to the sheet with the name which is is the named cell "Name2"
if it finds that sheet, you get a message saying it already exists
if it doesn't find that sheet, then it creates a new one and gives it that name

I can't see that there are any other branches of logic to explain what you describe!

I can't quite understand from your post whether you are telling me what is actually happening or what you would like to happen.

 

[JV0710]

Hi Yard

Sorry for the misunderstanding

This what is happening.

To test the macro, I have my workbook open to my Summary page.

In cell Z1 ( Named: Name1) I have the name I would like as the destination file name
(Joe - Wedge Tracking Workbook"

In cell Z3 (Named: Name2 ) I have ) reference number ( 12345 )

I run the macro and it creates and opens a file in "C:\" with name
"Joe%20-%20Wedge%20Tracking%20Workbook.xlsx"

and there is a sheet in the file with the name "12345" - with the data from my summary page ( all data correct - including formatting ).

**************************************

The I close the file that was created.

I then change the reference number in cell Z3 of my summary page to ( 12346 ) and run the macro

It opens a file called "Joe%20-%20Wedge%20Tracking%20Workbook.xlsx" with a sheet in it called "12346" with the data copied and pasted.

But the sheet called "12345" is no longer there.

***************************************
It should open the file named "Joe%20-%20Wedge%20Tracking%20Workbook.xlsx"
and it should have two sheets named (12345) & ( 12346 ).

I hope this is making more sense

Thank you

Joe

 

[Yard]

Yes, what you want makes sense. What is happening does not.

When you close the file after sheet 12345 is created, do you save it?

When you look in the C: directory, is there just one file called Joe - Wedge Tracking Workbook.xlsx?