Microsoft Office Forums How to copy excel sheet withe HEADER and Paste into new sheet?

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-04-2017, 01:24 PM
cloudforgiven cloudforgiven is offline How to copy excel sheet withe HEADER and Paste into new sheet? Windows XP How to copy excel sheet withe HEADER and Paste into new sheet? Office 2013
Novice
How to copy excel sheet withe HEADER and Paste into new sheet?
 
Join Date: Nov 2016
Posts: 13
cloudforgiven is on a distinguished road
Default How to copy excel sheet withe HEADER and Paste into new sheet?


Hi I have a table in one sheet(see attachment), and I want to use a button to copy the entire SHEET from that to another sheet. How would I do that? Its for people who are very old I want to make them avoid copying and pasting. I tried doing it but only the table contents get moved over, no lines nor the header. Also I tried recording a macro but it still wont work properly? Any ideas?
Attached Files
File Type: xlsx test.xlsx (11.7 KB, 1 views)

Last edited by cloudforgiven; 01-04-2017 at 09:04 PM.
Reply With Quote
  #2  
Old 01-05-2017, 09:35 AM
NoSparks NoSparks is offline How to copy excel sheet withe HEADER and Paste into new sheet? Windows 7 64bit How to copy excel sheet withe HEADER and Paste into new sheet? Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

The attachment doesn't have a second sheet.
What exists (if anything) on the sheet you're trying to copy to?
Reply With Quote
  #3  
Old 01-05-2017, 11:51 AM
cloudforgiven cloudforgiven is offline How to copy excel sheet withe HEADER and Paste into new sheet? Windows XP How to copy excel sheet withe HEADER and Paste into new sheet? Office 2013
Novice
How to copy excel sheet withe HEADER and Paste into new sheet?
 
Join Date: Nov 2016
Posts: 13
cloudforgiven is on a distinguished road
Default

Ah you can just add a second sheet, also there will never be anything on the new sheet I am pasting to because I have a button that ask the user what they want the new sheet name to be and then generates the NEW sheet named after what the user inputted. However I also want the new sheet that is being generated to have the table you see in the attachment, so Ill just add the code(To copy and paste that SHEET which has the table) to whatever I already have which is :

Code:
Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function
 

Private Sub CommandButton2_Click()

Dim SheetName As String
Dim shExists As Boolean

Do

SheetName = InputBox("Please enter the LAST NAME of the DTS.", "Add Sheet")
If SheetName <> "" Then
shExists = SheetExists(SheetName)
    If Not shExists Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetName
        MsgBox "The " & (SheetName) & " sheet was successfuly made. Please don't forget to CHANGE the name under the field DTS NAME.", , "Result"
    Else
        MsgBox "The last name already exists, please enter a NUMBER at the end of the LAST NAME to distinguish it from the existing one. For example lastname1 or lastname2", vbOKOnly + vbInformation, "Name"
    End If
Else
    MsgBox "You did not enter anything.", vbOKOnly + vbInformation, "Warning"

'Exit Function
End If
End Sub
Reply With Quote
  #4  
Old 01-05-2017, 04:00 PM
NoSparks NoSparks is offline How to copy excel sheet withe HEADER and Paste into new sheet? Windows 7 64bit How to copy excel sheet withe HEADER and Paste into new sheet? Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Your line of thought is off.
You don't copy an entire sheet and paste it to a blank sheet, you just create a copy of the original.

Start the macro recorder
right click the sheet 1 tab
click move or copy
check Create a copy
click OK
rename the sheet
Stop the macro recorder and see what it produced

It can be reduced to something like this to be used in your command button macro.
Code:
    Sheets("Sheet1").Copy before:=Sheets(1)
    ActiveSheet.Name = SheetName
Reply With Quote
  #5  
Old 01-05-2017, 05:46 PM
cloudforgiven cloudforgiven is offline How to copy excel sheet withe HEADER and Paste into new sheet? Windows XP How to copy excel sheet withe HEADER and Paste into new sheet? Office 2013
Novice
How to copy excel sheet withe HEADER and Paste into new sheet?
 
Join Date: Nov 2016
Posts: 13
cloudforgiven is on a distinguished road
Default

I got it thanks. I just had to re-arrange my code with yours and basically do the macro first then have my code change the name of the sheet the macro generated.
Reply With Quote
  #6  
Old 01-05-2017, 07:18 PM
NoSparks NoSparks is offline How to copy excel sheet withe HEADER and Paste into new sheet? Windows 7 64bit How to copy excel sheet withe HEADER and Paste into new sheet? Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 735
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

Response removed

Last edited by NoSparks; 01-06-2017 at 11:15 AM. Reason: OP changed their post.... now makes no sense
Reply With Quote
  #7  
Old 01-05-2017, 07:30 PM
cloudforgiven cloudforgiven is offline How to copy excel sheet withe HEADER and Paste into new sheet? Windows XP How to copy excel sheet withe HEADER and Paste into new sheet? Office 2013
Novice
How to copy excel sheet withe HEADER and Paste into new sheet?
 
Join Date: Nov 2016
Posts: 13
cloudforgiven is on a distinguished road
Default

I am so sorry, I got it 100% working with all the little trinkets I needed, its just I found the answers online, kinda late after I posted it. Thank you for all ur help again
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy excel sheet withe HEADER and Paste into new sheet? Open an existing sheet by clicking on a cell in a master sheet darbybrown Excel 3 09-12-2016 05:12 PM
Populate sheet 3 with data from sheet 1 and sheet 2 speck Excel Programming 0 01-14-2015 07:54 AM
How to copy excel sheet withe HEADER and Paste into new sheet? Create a New Sheet from Existing Sheet with Specific Columns malam Excel Programming 1 10-17-2014 10:01 PM
How to copy excel sheet withe HEADER and Paste into new sheet? Search Multiple strings and create new word sheet subodhgupta Word Tables 1 05-20-2014 08:09 AM
Search for date and then apply mutliple search criteria in huge dataset maxtymo Excel 2 12-01-2013 04:52 AM


All times are GMT -7. The time now is 03:16 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft