VBA script help for copying same data into different tabs

[mlance617]

Hi there

I am looking for help on writing some VBA code for the following. I have an entire data tab (titled "paste 1804") I need copied into 2 different tabs with same format, content, etc..:


I have Macro push button labeled "Run data Macro", on a tab labeled "Marcos"

Source tab to copy: "paste 1804"

paste into tab: "locked 1804"

paste into tab: "edit 1804"

box that pops up stating "1804 data copied complete"

 

[mlance617]

Sub Save_Twice_To_Desktop()
Dim newNm, a As String, dt As String, tm As String, i As Long
a = CreateObject("WScript.Shell").specialfolders("Desktop")
newNm = Array("locked 1804", "edit 1804")    '<---- Change names as required
dt = Format(CStr(Now), "ddmmmyy_hhmm")
Application.ScreenUpdating = False
    For i = LBound(newNm) To UBound(newNm)
        Sheets("paste 1804").Copy
        With ActiveWorkbook
            .SaveAs a & "\" & newNm(i) & " " & dt & ".xlsx"
            .Close
        End With
    Next i
Application.ScreenUpdating = True
End Sub

The code worked with no errors. but looks like it just made a copies of the Paste 1804 tab. If I make changes in the locked 1804 tab, and edit 1804 tab - I dont see the changes made on those tabs in the saved items that went to to the desk top. Just need a carbon copy of locked 1804 and edit 1804 tabs, to the desktop. And apologies on the quotes from before

 

[jolivanes]

Change this line

Sheets("paste 1804").Copy

to this

Sheets(newNm(i)).Copy

You won't see changes unless you save the sheet again as new workbook.

 

[mlance617]

Sub Save_Twice_To_Desktop()
Dim newNm, a As String, dt As String, tm As String, i As Long
a = CreateObject("WScript.Shell").specialfolders("Desktop")
newNm = Array("locked 1804", "edit 1804")    '<---- Change names as required
dt = Format(CStr(Now), "ddmmmyy_hhmm")
Application.ScreenUpdating = False
    For i = LBound(newNm) To UBound(newNm)
        Sheets("paste 1804").Copy
        With ActiveWorkbook
            .SaveAs a & "\" & newNm(i) & " " & dt & ".xlsx"
            .Close
        End With
    Next i
Application.ScreenUpdating = True
End Sub

You're a legend, works great - thanks

 

[jolivanes]

Thank you for the kind words and letting us know that all is well.
Good luck

 

[Peter_SSs]

You're a legend, works great - thanks

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.