#1
|
|||
|
|||
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?
Last edited by cloudforgiven; 01-04-2017 at 09:04 PM. |
#2
|
|||
|
|||
The attachment doesn't have a second sheet.
What exists (if anything) on the sheet you're trying to copy to? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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.
|
#6
|
|||
|
|||
Response removed
Last edited by NoSparks; 01-06-2017 at 11:15 AM. Reason: OP changed their post.... now makes no sense |
#7
|
|||
|
|||
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
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Create a New Sheet from Existing Sheet with Specific Columns | malam | Excel Programming | 1 | 10-17-2014 10:01 PM |
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 |