VBA for copying a range to new sheet in same workbook

sahrish

New Member
Joined
Sep 7, 2016
Messages
2
I have a sheet im using to create receipts for my business. I have created a macro that captures that info and transfers it to a record of invoices sheet and then deletes it.
Now I want to create a button that will let me copy over a copy of that receipt on the same workbook but I only need certain cells in the receipt, not the entire receipt.
For example. I made a receipt from the tab called "Receipt". I want to move over range A1:J40 to a new tab called Receipt1, then receipt2 then receipt 3....
I can use the following and it copies over the entire sheet. I only want the range.
Also, Is there a way to make the tab called Receipt the active sheet once i run the macro and not the new sheet that was created?

I have the following to create a new tab for the entire sheet

Sub Copypagerange()

Dim WSCount As Long
WSCount = Worksheets.Count

activesheet.Copy after:=Sheets(Sheets.Count)
activesheet.name = "Receipt" & WSCount + -2

End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If I understand right what you're wanting maybe this will do it
VBA Code:
Sub Copypagerange()

Dim i As Long
Dim srcWS As Worksheet

Set srcWS = ActiveSheet
Application.ScreenUpdating = False

srcWS.Range("A1:J40").Copy

For i = 1 To 3      '3 is number of receipt sheets required
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Receipt" & Worksheets.Count - 2
    ActiveSheet.Range("A1:J40").PasteSpecial xlPasteAll
    Range("A1").Select
Next i

srcWS.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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