VBA copy sheet

[jakobt]

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.

 

[Joe4]

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?

 

[jakobt]

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...

 

[Joe4]

After you open the workbook, you can capture the name of the worksheet like this:

Dim ws_name as String
ws_name = ActiveSheet.Name

so then you could select it later like this:

Sheets(ws_name).Select

Alternatively, you could use a worksheet object, i.e.

Dim ws as Worksheet
Set ws = ActiveSheet

so then you could select it later like this:

ws.Select

 

[jakobt]

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

 

[Joe4]

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?

 

[jakobt]

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"

 

[Joe4]

OK, try this (I added comments so you can follow along):

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

 

[jakobt]

Bravo,Bravo,Bravo!!!

I have been missing this macro for a year...It will reduce my work a lot. Thank you so much.

 

[Joe4]

You are welcome.
Glad I was able to help.