copy single worksheet from a workbook to folder

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
647
Hi All

I have a workbook that I need to copy one sheet from to a folder I'm creating as part of the process.

I'm not having problems with the folder creation, just copying the sheet to the correct folder

What I am trying to do is put sheet 1 into "1 PHJ Daybook"

Sheet 1 is "Daybook Import Sheet"

I'm obviously going wrong at the end, I've been going around in circles with this for hours now, any ideas where I am going wrong??

Kind regards

Paul

VBA Code:
Sub make_folders()
Application.ScreenUpdating = False
Dim myPackFolder As String
Dim myFolder As String
Dim Mycustomer As String
Dim myFolders()
Dim myIndex As Integer
Dim myfilepath As String
ReDim myFolders(99)
myFolders(0) = "1 PHJ Daybook"
myFolders(1) = "2 PPS System Import File"
myFolders(2) = "3 Engineers Route Planners"
myFolders(3) = "4 PPS Web App Day Report"
myFolders(4) = "5 Additional Information"
myfilepath = ThisWorkbook.Path
myFolder = myfilepath & "\Daily Reports\"
Mycustomer = VBA.Format(VBA.Now, "dd-MMM-yyyy")
myPackFolder = myFolder & "\" & Mycustomer & " Excel Files"


'check if folders already exist
If Dir(myPackFolder, vbDirectory) = "" Then
MkDir myPackFolder
Else
MsgBox "This date alread exists." & vbCr & vbCr & "Please check and try again.", vbExclamation, "Folder Error"
Exit Sub
End If


For myIndex = 0 To UBound(myFolders)
If myFolders(myIndex) = "" Then
Exit For
End If
MkDir myPackFolder & "\" & Mycustomer & "-" & myFolders(myIndex)
Next myIndex


''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheet1.Activate


Worksheets("This Sheet").SaveAs ThisWorkbook.Path & "\Daily Reports\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & _
" Excel Files" & "\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & "-1 PHJ Daybook" & "\Daybook Import Sheet.xlsm"


MsgBox ("The record folders and PPS Import File have been created for: ") & Mycustomer, vbInformation + vbOKOnly, "Folder Created"




End Sub
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sheet1.Copy

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Daily Reports\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & _
" Excel Files" & "\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & "-1 PHJ Daybook" & "\Daybook Import Sheet.xlsm"
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
647
How about
VBA Code:
Sheet1.Copy

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Daily Reports\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & _
" Excel Files" & "\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & "-1 PHJ Daybook" & "\Daybook Import Sheet.xlsm"
Hi Fluff

I think you have it but I'm getting the message in the attached image
 

Attachments

  • Fluff.PNG
    Fluff.PNG
    8.8 KB · Views: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can hide that notice like
VBA Code:
Sheet1.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Daily Reports\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & _
" Excel Files" & "\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & "-1 PHJ Daybook" & "\Daybook Import Sheet.xlsm"
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
647
You can hide that notice like
VBA Code:
Sheet1.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Daily Reports\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & _
" Excel Files" & "\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & "-1 PHJ Daybook" & "\Daybook Import Sheet.xlsm"
Hi Fluff

it's very close now but I'm getting a different error, see below. I think there is something not quite correct with the path, I'm looking to see if i can figure out what it is
 

Attachments

  • Fluff 2.PNG
    Fluff 2.PNG
    6.8 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What line of code gives that error?
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
647
What line of code gives that error?
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Daily Reports\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & _
" Excel Files" & "\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & "-1 PHJ Daybook" & "\Daybook Import Sheet.xlsm"
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
381
Office Version
  1. 2007
Platform
  1. Windows
You have some errors in creating folders path, so if you still have problem here is complete code...

VBA Code:
Sub make_folders()

    Dim myPackFolder As String
    Dim myFolder As String
    Dim Mycustomer As String
    Dim myFolders()
    Dim myIndex As Integer
    Dim myfilepath As String
   
    Application.ScreenUpdating = False
    ReDim myFolders(99)
    myFolders(0) = "1 PHJ Daybook"
    myFolders(1) = "2 PPS System Import File"
    myFolders(2) = "3 Engineers Route Planners"
    myFolders(3) = "4 PPS Web App Day Report"
    myFolders(4) = "5 Additional Information"
    myfilepath = ThisWorkbook.Path
    myFolder = myfilepath & "\Daily Reports\"
    Mycustomer = VBA.Format(VBA.Now, "dd-MMM-yyyy")
    myPackFolder = myFolder & "\" & Mycustomer & " Excel Files"
    'check if folders already exist
    If Dir(myPackFolder, vbDirectory) = "" Then
         MkDir myPackFolder
    Else
         MsgBox "This date alread exists." & vbCr & vbCr & "Please check and try again.", vbExclamation, "Folder Error"
         Exit Sub
    End If
    For myIndex = 0 To UBound(myFolders)
    If myFolders(myIndex) = "" Then
         Exit For
    End If
    MkDir myPackFolder & "\" & Mycustomer & "-" & myFolders(myIndex)
    Next myIndex
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sheet1.Activate
    Worksheets("This Sheet").Copy
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Daily Reports\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & _
    " Excel Files" & "\" & VBA.Format(VBA.Now, "dd-MMM-yyyy") & "-1 PHJ Daybook" & "\Daybook Import Sheet.xlsx"
    MsgBox ("The record folders and PPS Import File have been created for: ") & Mycustomer, vbInformation + vbOKOnly, "Folder Created"
   
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Not sure why you would get that error there, but try
VBA Code:
ActiveWorkbook.SaveAs myPackFolder & "\" & Mycustomer & "-" & myFolders(0) & "\Daybook Import Sheet.xlsm", 52
 

Watch MrExcel Video

Forum statistics

Threads
1,127,217
Messages
5,623,450
Members
415,970
Latest member
ZorroOP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top