VBA copy sheet

jakobt

Active Member
Joined
May 31, 2010
Messages
337
In a workbook, which workbookname and sheet will change, but I want to copy into this workbook/sheet I am in when running the macro.

The code for what I want to copy is already written:

Developed a code:

workbooks.open Filename:="S:\Report.xlsx.

Sheets("Januay").select

Range("A1:z100").copy

How can this code be amended to include the objective mentioned above.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Will the workbook only have one sheet?
If not, and the names may change, what is the logic for determining which sheet we want to copy?
 
Upvote 0
So I have 2 workbooks.
1 workbook I am currently working in. Every month I roll it forward...so the name will change. However I will be in a current selected sheet and cell a1.
Then I run the code already written to open another workbook and copy a range.

I just need to amend the code so the range is copied into workbook 1 and the sheet selected in cell a1...
 
Upvote 0
After you open the workbook, you can capture the name of the worksheet like this:
Code:
Dim ws_name as String
ws_name = ActiveSheet.Name
so then you could select it later like this:
Code:
Sheets(ws_name).Select

Alternatively, you could use a worksheet object, i.e.
Code:
Dim ws as Worksheet
Set ws = ActiveSheet
so then you could select it later like this:
Code:
ws.Select
 
Last edited:
Upvote 0
Thanks but nothing is copied in though:

sub copytb()
dm ws_name as string
ws_name = activesheet.name
workbooks.open Filename:"S\Report.xlsx"
sheets("January").select
range(A1:z1000").copy ws_name

end sub
 
Upvote 0
You have all sorts of typos in the last block of code you posted. Also, you can copy/paste across two different workbooks with a single command like that.

Let's flesh this out a bit. Please answer ALL the questions below:

You are opening a new file, and copy data from that file into the file that contains the macro, is that correct?

In the macro file that you are copying to, how many sheets are in it?
Should it always paste the copied data into whatever sheet is active when you run the macro?

In the file you are opening and copying from, will it always be copying from the sheet that it opens up to?
If not, how do we find the sheet that we want to copy from?
 
Upvote 0
1) Yes, correct opening a new file copy and copy data into original (active) file..

2) Yes , always copy into the active sheet selected when running the macro.(there are many sheets in the active workbook)

3) Yes, correct sheet will always be called "January"
 
Upvote 0
OK, try this (I added comments so you can follow along):
Code:
Sub copytb()
    
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    
'   Capture current workbook
    Set WB1 = ActiveWorkbook
    
'   Open report
    Workbooks.Open Filename:="S:\Report.xlsx"
    
'   Capture this workbook
    Set WB2 = ActiveWorkbook
    
'   Copy data from January sheet
    Sheets("January").Select
    Range("A1:Z1000").Copy
'   Paste to original workbook
    WB1.Activate
    Range("A1").Select
    ActiveSheet.Paste
    
'   Close original workbook
    WB2.Close

End Sub
 
Last edited:
Upvote 0
Bravo,Bravo,Bravo!!!

I have been missing this macro for a year...It will reduce my work a lot. Thank you so much.
 
Upvote 0
You are welcome.
Glad I was able to help.
:)
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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