• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

paste data to a new workbook

Afarag

Member
Hi,

Please i ask for help as i have a sheet that i update daily, with each update an VBA migrate it to a new sheet at the same workbook, this code use a specific cell "contain date with this format ("dd-mmm-yyyy") eg:(17-Jul-2014) as a title to the new sheet.

i want to migrate this sheets update to a new workbook and this workbook title will be the titled sheet month "Jul-2014". With the first sheet in a new month, want to migrate it to a new workbook titled with its month and contain the month update sheets. want new workbook for each new month

the main sheet that the data will copied from is called "Preview", its copied range will be from ("A1:DA500"), this data wil pasted as "Values and number formats". and will save at the Desktop

this is the code that i use to paste to a new sheet

Code:
Sub CopyNew()
    Dim wsNew As Worksheet
    Dim myRange As Range
    Dim strName As String
   
    Application.ScreenUpdating = False
   
    With Worksheets("preview")
        'Can't use / in sheet names, changed format:
        strName = Format(.Range("C2").Value, "dd-mmm-yyyy")
        Set myRange = .Range("A1:DA500")
       
        Set wsNew = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets("preview"))
        wsNew.Name = strName
        myRange.Copy
        wsNew.Cells(1, 1).PasteSpecial xlPasteValues
        wsNew.Cells(1, 1).PasteSpecial xlPasteFormats
        wsNew.Cells(1, 1).PasteSpecial xlPasteColumnWidths
        Application.CutCopyMode = False
       
        Call SortSheets
        .Move Before:=Sheets(1)    'Move the "Final" sheet to the be the first
    End With
   
    Application.ScreenUpdating = True
End Sub

Sub SortSheets()
    Dim i As Long, j As Long
 
    For i = 1 To Sheets.Count
        For j = 1 To Sheets.Count - 1
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
                Sheets(j).Move After:=Sheets(j + 1)
            End If
        Next j
    Next i
End Sub
 
Hello Afarag,

Test if the following code works for you. The workbook which has sheet named "preview" must be your active workbook.

It will create a workbook with month name if not already existing in the same directory as the activeworkbook. If already exists then it will open the workbook and copy paste data.

It is minimum code and you may need to write additional code for cases such as to see if the destination workbook is already open etc.
Code:
Option Explicit
Public Sub MakeNew()
Dim strWbName As String, strWsName As String
Dim wbDest As Workbook, wbSource As Workbook
Dim wsDest As Worksheet

'\\ Assuming the workbook is being kept in the same folder!!
'\\ If not then change the path on the next line to suit
Set wbSource = ActiveWorkbook
strWbName = wbSource.Path & Application.PathSeparator & Format(Now, "mmm-yyyy") & ".xlsx"

'\\ Check workbook for existence and choose action accordingly
If Not blWorkbookExists(strWbName) Then
  Set wbDest = Workbooks.Add
  wbDest.SaveAs strWbName
Else
  Set wbDest = Workbooks.Open(strWbName)
End If

'\\ Check worksheet for existence and inform user
strWbName = Split(strWbName, Application.PathSeparator)(UBound(Split(strWbName, Application.PathSeparator)))
strWsName = Format(wbSource.Sheets("preview").Range("C2").Value, "dd-mmm-yyyy")
If Not blWorksheetExists(strWbName, strWsName) Then
  Set wsDest = wbDest.Sheets.Add(After:=wbDest.Sheets(wbDest.Sheets.Count))
Else
  MsgBox "The destination sheet is already created! Please check", vbExclamation
  Exit Sub
End If

'\\ Perform copy paste
wbSource.Sheets("preview").Range("A1:DA500").Copy
With wsDest.Range("A1")
  .PasteSpecial xlPasteValues
  .PasteSpecial xlPasteFormats
  .PasteSpecial xlPasteColumnWidths
End With
wsDest.Name = strWsName
Application.CutCopyMode = False

End Sub

Public Function blWorkbookExists(strWbName As String) As Boolean
'\\ Function to check if workbook exists
If Dir(strWbName) <> "" Then
  blWorkbookExists = True
End If
End Function

Public Function blWorksheetExists(strWbName As String, strWsName As String) As Boolean
'\\ Function to check if worksheet exists in the specified workbook
Dim strSheet As String
On Error Resume Next
strSheet = Workbooks(strWbName).Sheets(strWsName).Name
If Len(strSheet) > 0 Then
  blWorksheetExists = True
End If
On Error GoTo 0
End Function
 
Hi Shrivallabha,

great work your code is doing. i need to clarify a point hope to add it to this code,
>> as i clarified this migrated sheets is titled with a dates, i ask if every new month will migrate to a new workbook eg: if i have migrate sheets titled 1-6-2014, 2-6-2014, ... 30-6-2014, this sheets migrate to new workbook titled ("Jun-2014"), if i start to migrate sheets titled with a new month as eg: 1-7-2014, 2-7-2014 ...etc, need this sheets migrate to a new work book with title ("Jul-2014"), use the date of sheets in its contained workbook
0

i tested your code it's amazing, hope when migrate a sheets in Jun-2014, the workbook title will be ("Jun-2014"), and when migrate sheet use another month as a title migrate to a new workbook with it's month title

Thanks a lot,
 
Hi Afarag,

Thanks for the feedback. You want the month to be evaluated on the basis of cell C2 in sheet "preview".

Just change following line in the code:
Code:
strWbName = wbSource.Path & Application.PathSeparator & Format(Now, "mmm-yyyy") & ".xlsx"
to
Code:
strWbName = wbSource.Path & Application.PathSeparator & Format(wbSource.Sheets("preview").Range("C2").Value, "mmm-yyyy") & ".xlsx"
Note the change made from "Now" to cell reference.

Hth,
 
Hi Shrivallabha,

the 1st point related if new workbook title is solved. but the 2nd point when migrate a sheets have a new month as a title want to move it to a new workbook but not the same migrated workbook that contain previous month. As per above image i moved to sheets in Jun month in a new workbook called "Jun-2014", but the sheet that called (10-Jul-2014) need it migrated to a new workbook titled (Jul-2014). ask if Every sheet at the same month will migrate to a new workbook use its sheets month title. eg: workbook for Jun sheets, workbook for Jul sheets, for Aug sheets etc...,

Gratefully,
 
Shrivallabha,

yup, you are right when changing the date format it migrated to a new workbook, Now every month in a independence workbook.
Great work you have been provided

Gratefully,
 
Back
Top