VBA Code to copy last worksheet to same worksheet and after copying i need to hide other sheets

A

Abuu Farhaan

New Member
Joined
Jan 7, 2025
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hello,
I need a help on VBA code that will copy last sheet of workbook to the same workbook and after copying it will hide other sheets including the last sheet that i use to create a copy from and only remain the copied sheet and some other sheets in a workbook. Each sheet will be containing a particular information of a single date and after finishing to put information of a particular date e.g. 07 Jan 2025, then i will need to create another sheet of 08 Jan 2025 with template like 07 Jan 2025 and hide the sheets of previous date, and only remain current date sheet

MBEYA SALES JAN 2025.xlsm
ABCDEFGHIJKLMNOPQRST
1GAS SUPPLIER | ALBASWAR SHOP07/01/2025 (Tue)
2#PRODUCTOPEN STOCKSTOCK IN THE STORESTOCK ADDED TODAYSOLD TODAYHOWOMWENGE T516 DQA T367 EHV T379DMZ T277 EEA T571 EAHT396 EENT856 DPDT517 DWYT408 EKBCLOSE STOCKCROSS CHECK
3LPG GASEMPTY - 06KG4763.002478.007.001300.006.0080.0016.0080.0080.00300.0080.00137.00199.004756.000.00
4EMPTY - 15KG222.00131.006.0039.002.004.0010.004.004.0010.004.008.00216.000.00
5EMPTY - 38KG33.0026.000.006.001.0033.000.00
6REFILL - 06KG2523.001265.00280.006.0080.0016.0080.0080.00300.0080.00137.00199.002243.000.00
7REFILL - 15KG164.00112.006.002.004.0010.004.004.0010.004.008.00158.000.00
8REFILL - 38KG20.0019.000.001.0020.000.00
9ACCESSORIESBURNER - KAZKAZ0.000.000.000.000.00
10BURNER KAWAIDA814.00664.000.0030.0020.0030.0050.0020.00814.000.00
11HOSE - CLAMPS182.00182.000.00182.000.00
12HOSE PIPE - FLEXIBLE - HP114.00114.000.00114.000.00
13HOSE PIPE - WIRED0.000.000.000.000.00
14JIKO GLASS29.0029.000.0029.000.00
15JIKO BATI39.0039.000.0039.000.00
16JIKO NIKAI0.000.000.000.000.00
17LIGHTER GAS - ECO0.000.000.000.000.00
18REGULATOR - ADENT - 06KG9.009.000.009.000.00
19REGULATOR - ADENT - 15KG84.0084.000.0084.000.00
20REGULATOR - IGT 38KG0.000.000.000.000.00
21REGULATOR - IGT 15KG GAUGE5.005.000.005.000.00
22TRIVET KAWAIDA869.00719.000.0030.0020.0030.0050.0020.00869.000.00
23TRIVET - CHUMA4.004.000.004.000.00
24
07.01
Cell Formulas
RangeFormula
S3:S23S3=+C3+E3-F3
T3:T23T3=SUM(D3,G3,H3,I3,J3,L3,K3,M3,N3,O3,P3,Q3,R3)-SUM(C3,E3-F3)+E3
D3:D23D3=C3+E3-F3-G3-H3-I3-J3-L3-M3-N3-O3-P3-K3-Q3
F3F3=SUMIF(C26:C129,B3,D26:D129)
F4F4=SUMIF(C26:C129,B4,D26:D129)
F5F5=SUMIF(C26:C129,B5,D26:D129)
F6F6=SUMIF(C26:C129,B6,D26:D129)
F7F7=SUMIF(C26:C129,B7,D26:D129)
F8F8=SUMIF(C26:C129,B8,D26:D129)
F9F9=SUMIF(C26:C129,B9,D26:D129)
F10F10=SUMIF(C26:C129,B10,D26:D129)
F11F11=SUMIF(C26:C129,B11,D26:D129)
F12F12=SUMIF(C26:C129,B12,D26:D129)
F13F13=SUMIF(C26:C129,B13,D26:D129)
F14F14=SUMIF(C26:C129,B14,D26:D129)
F15F15=SUMIF(C26:C129,B15,D26:D129)
F16F16=SUMIF(C26:C129,B16,D26:D129)
F17F17=SUMIF(C26:C129,B17,D26:D129)
F18F18=SUMIF(C26:C129,B18,D26:D129)
F19F19=SUMIF(C26:C129,B19,D26:D129)
F20F20=SUMIF(C26:C129,B20,D26:D129)
F21F21=SUMIF(C26:C129,B21,D26:D129)
F22F22=SUMIF(C26:C129,B22,D26:D129)
F23F23=SUMIF(C26:C129,B23,D26:D129)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F3:F23Cell Value=0textNO
T2:T23Cell Value<0textNO
T2:T23Cell Value>0textNO
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
It would help a bunch if you could post your workbook to a download site (Dropbox.com or similar) and provide the download link here.
Do not include any confidential information in your workbook.
 
Upvote 0
Abuu Farhaan said:
Hello,
I need a help on VBA code that will copy last sheet of workbook to the same workbook and after copying it will hide other sheets including the last sheet that i use to create a copy from and only remain the copied sheet and some other sheets in a workbook. Each sheet will be containing a particular information of a single date and after finishing to put information of a particular date e.g. 07 Jan 2025, then i will need to create another sheet of 08 Jan 2025 with template like 07 Jan 2025 and hide the sheets of previous date, and only remain current date sheet

MBEYA SALES JAN 2025.xlsm
ABCDEFGHIJKLMNOPQRST
1GAS SUPPLIER | ALBASWAR SHOP07/01/2025 (Tue)
2#PRODUCTOPEN STOCKSTOCK IN THE STORESTOCK ADDED TODAYSOLD TODAYHOWOMWENGE T516 DQA T367 EHV T379DMZ T277 EEA T571 EAHT396 EENT856 DPDT517 DWYT408 EKBCLOSE STOCKCROSS CHECK
3LPG GASEMPTY - 06KG4763.002478.007.001300.006.0080.0016.0080.0080.00300.0080.00137.00199.004756.000.00
4EMPTY - 15KG222.00131.006.0039.002.004.0010.004.004.0010.004.008.00216.000.00
5EMPTY - 38KG33.0026.000.006.001.0033.000.00
6REFILL - 06KG2523.001265.00280.006.0080.0016.0080.0080.00300.0080.00137.00199.002243.000.00
7REFILL - 15KG164.00112.006.002.004.0010.004.004.0010.004.008.00158.000.00
8REFILL - 38KG20.0019.000.001.0020.000.00
9ACCESSORIESBURNER - KAZKAZ0.000.000.000.000.00
10BURNER KAWAIDA814.00664.000.0030.0020.0030.0050.0020.00814.000.00
11HOSE - CLAMPS182.00182.000.00182.000.00
12HOSE PIPE - FLEXIBLE - HP114.00114.000.00114.000.00
13HOSE PIPE - WIRED0.000.000.000.000.00
14JIKO GLASS29.0029.000.0029.000.00
15JIKO BATI39.0039.000.0039.000.00
16JIKO NIKAI0.000.000.000.000.00
17LIGHTER GAS - ECO0.000.000.000.000.00
18REGULATOR - ADENT - 06KG9.009.000.009.000.00
19REGULATOR - ADENT - 15KG84.0084.000.0084.000.00
20REGULATOR - IGT 38KG0.000.000.000.000.00
21REGULATOR - IGT 15KG GAUGE5.005.000.005.000.00
22TRIVET KAWAIDA869.00719.000.0030.0020.0030.0050.0020.00869.000.00
23TRIVET - CHUMA4.004.000.004.000.00
24
07.01
Cell Formulas
RangeFormula
S3:S23S3=+C3+E3-F3
T3:T23T3=SUM(D3,G3,H3,I3,J3,L3,K3,M3,N3,O3,P3,Q3,R3)-SUM(C3,E3-F3)+E3
D3:D23D3=C3+E3-F3-G3-H3-I3-J3-L3-M3-N3-O3-P3-K3-Q3
F3F3=SUMIF(C26:C129,B3,D26:D129)
F4F4=SUMIF(C26:C129,B4,D26:D129)
F5F5=SUMIF(C26:C129,B5,D26:D129)
F6F6=SUMIF(C26:C129,B6,D26:D129)
F7F7=SUMIF(C26:C129,B7,D26:D129)
F8F8=SUMIF(C26:C129,B8,D26:D129)
F9F9=SUMIF(C26:C129,B9,D26:D129)
F10F10=SUMIF(C26:C129,B10,D26:D129)
F11F11=SUMIF(C26:C129,B11,D26:D129)
F12F12=SUMIF(C26:C129,B12,D26:D129)
F13F13=SUMIF(C26:C129,B13,D26:D129)
F14F14=SUMIF(C26:C129,B14,D26:D129)
F15F15=SUMIF(C26:C129,B15,D26:D129)
F16F16=SUMIF(C26:C129,B16,D26:D129)
F17F17=SUMIF(C26:C129,B17,D26:D129)
F18F18=SUMIF(C26:C129,B18,D26:D129)
F19F19=SUMIF(C26:C129,B19,D26:D129)
F20F20=SUMIF(C26:C129,B20,D26:D129)
F21F21=SUMIF(C26:C129,B21,D26:D129)
F22F22=SUMIF(C26:C129,B22,D26:D129)
F23F23=SUMIF(C26:C129,B23,D26:D129)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F3:F23Cell Value=0textNO
T2:T23Cell Value<0textNO
T2:T23Cell Value>0textNO
Click to expand...
Link to the file

Dropbox

www.dropbox.com www.dropbox.com
 
Upvote 0
The portion of the sheet you want copied is this ?

Sheet Copy.jpg
 
Upvote 0
Here is the working macro :

VBA Code: 
Sub CopyPage1AndHideOthers()
    Dim wsNew As Worksheet
    Dim ws As Worksheet
    Dim currentDate As String
    Dim sourceSheet As Worksheet
    Dim printRange As Range

    ' Get the current date in "MM.DD" format
    currentDate = Format(Date, "MM.DD")

    ' Check if the view is "Page Break Preview"
    If ActiveWindow.View <> xlPageBreakPreview Then
        MsgBox "Please switch to Page Break Preview view before running this macro.", vbExclamation
        Exit Sub
    End If

    ' Create a new worksheet and name it with the current date
    On Error Resume Next
    Set wsNew = ThisWorkbook.Sheets(currentDate)
    On Error GoTo 0

    If wsNew Is Nothing Then
        Set wsNew = ThisWorkbook.Sheets.Add
        wsNew.Name = currentDate
    Else
        MsgBox "A worksheet with the name '" & currentDate & "' already exists.", vbExclamation
        Exit Sub
    End If

    ' Set the source sheet as the active sheet
    ' Set the source sheet as the DATA SHEET
    Set sourceSheet = Sheets("DATA SHEET")

    ' Identify the range for "Page 1"
    Set printRange = sourceSheet.Range("A1:D24")

    ' Copy the data from Page 1
    printRange.Copy

    ' Activate the new sheet and paste the data
    wsNew.Activate
    wsNew.Range("A1").Select
    wsNew.Paste

    ' Alternative paste method using PasteSpecial
    wsNew.Range("A1").Select
    wsNew.Range("A1") = printRange

    ' Hide all other sheets except the new one
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsNew.Name Then
            ws.Visible = xlSheetHidden
        Else
            ws.Visible = xlSheetVisible
        End If
    Next ws

    ' Clear the clipboard
    Application.CutCopyMode = False

    ' Notify the user
    MsgBox "The range A1:D24 from 'Page 1' has been copied to a new sheet named '" & currentDate & "'. All other sheets are now hidden.", vbInformation
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
247
mumps
mumps
V
  • Question Question
vba - copy specific column data from multiple sheets from source workbook into a destination workbook from where I write the macro
Replies
0
Views
2K
vijkar2008
V
P
  • Solved
VBA to copy non blank cells to new sheet
Replies
6
Views
458
Fluff
Fluff
M
  • Question Question
Copy Cells From One Sheet To Other Sheets With Large Number Of Combinations
Replies
1
Views
408
mlarson
M
B
  • Question Question
VBA: Search for values in a column and copy row to a new sheet for all matching values
Replies
4
Views
28K
jpbcx43
J

Forum statistics

Threads
1,225,399
Messages
6,184,749
Members
453,254
Latest member
topeb

Share this page

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
Back
Top