Hi all, I have a requirement like this..In Sheet1 i have data like full page data,when i select a range in sheet1(A1: D22)(range is not constant) and if I click a shortcut maybe control+q or anything it should create a new sheet and paste that data from A2 and should come back to sheet1 and same repeats.
Please help me.
Thanks in advance
Copy & paste range to new sheet
-
harsha6989 -
February 17, 2018 at 3:33 PM -
Thread is marked as Resolved.
-
-
-
Do you want each newly created sheet to have a specific name?
-
[ATTACH=JSON]{"data-align":"none","data-size":"full","data-attachmentid":1199600}[/ATTACH] Thank you so much for the reply! Yes Yes I need a name for every sheet. I will give you a example. In the attached image I will select first blue XXXX and copy the contents the XXXX should be the new sheet name. Then it should come to the original sheet.Now i will select YYYY data and same thing follows.
-
First select the entire range that you want to copy. For example, if you want to copy the first blue XXXX and all the AA AA below it, then select that entire range and run the macro. If I misunderstood what you want to do, attach a copy of your file and include a detailed explanation of what you want to do referring to specific cell, rows and columns.
Code
Display MoreSub CreateSheet() Application.ScreenUpdating = False Dim ws As Worksheet Set ws = Nothing On Error Resume Next Set ws = Worksheets(Selection.Cells(1, 1).Value) On Error GoTo 0 If ws Is Nothing Then Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Selection.Cells(1, 1).Value End If Sheets("Sheet1").Select Selection.Copy Sheets(Selection.Cells(1, 1).Value).Cells(Sheets(Selection.Cells(1, 1).Value).Rows.Count, "A").End(xlUp).Offset(1, 0) Application.ScreenUpdating = True End Sub
-
Wow thank you so much for the help its is working awesome. Only i need a small addition once it is copy pasted to new sheet it should check for blank rows and deleted that.
-
-
Do you want the selection header (XXXX, YYYY or ZZZZ) copied each time or just the data below each header?
-
Try:
Code
Display MoreSub CreateSheet() Application.ScreenUpdating = False Dim ws As Worksheet Set ws = Nothing On Error Resume Next Set ws = Worksheets(Selection.Cells(1, 1).Value) On Error GoTo 0 If ws Is Nothing Then Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Selection.Cells(1, 1).Value End If Sheets("Sheet1").Select Selection.Copy Sheets(Selection.Cells(1, 1).Value).Cells(Sheets(Selection.Cells(1, 1).Value).Rows.Count, "A").End(xlUp).Offset(1, 0) Sheets(Selection.Cells(1, 1).Value).Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete Application.ScreenUpdating = True End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!