VBA loop to activate sheets in order

[Jeyarajm2017]

Hi,
I need your help for a vba code to activate sheets in order. For example i have sheet names like Day1, Day2, Day3....
I am trying to copy some information from Day1 sheet, and paste in a different sheet, likewise from Day2 Sheet, and then paste in a different sheet.
so i need vba to activate Day1 copy information paste in a different sheet, Activate Day2 sheet copy information paste in a different sheet.
Request your help on this please

Thanks,
Jeyaraj M

 

[rahulshewale1]

Hi @Jeyarajm2017 ,

Please upload sample with desire output.


Regard
Rahul Shewale

 

[pecoflyer]

And ask a Mod to move this thread to the correct subforum

 

[Jeyarajm2017]

Hi @Jeyarajm2017 ,

Please upload sample with desire output.

Hi,
I have attached two excel workbook. Days workbook contains 3 sheets (Day1, Day2, Day3 with values in Cell A1. I need to copy Cell A1 value in Day1 sheet of Days workbook and open the Week workbook and select the Sheet "Monday" and paste the value. Simulatenously Copy cell a1 value from Day2 sheet of Days workbook and then paste it in week workbook and select the sheet "Tuesday" and paste the value

Regard
Rahul Shewale

 

[rahulshewale1]

hi @Jeyarajm2017 ,

See if it ok ?

Sub CopyDetails()

Dim ws As Worksheet
Dim i As Long

i = 1

For Each ws In Worksheets
    
    Workbooks("Week.xlsx").Activate
    Worksheets(i).Activate
    ActiveSheet.UsedRange.Clear
    
    ws.Activate
    ws.UsedRange.Copy
    
    Workbooks("Week.xlsx").Activate
    Worksheets(i).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    i = i + 1

Next ws
End Sub

 

[Marc L]

Hi Rahul !​

​

As often a good code does not need any Activate neither Select statements​

so intead of them just use the Destination parameter of the Range.Copy method as described in VBA help​

then only an unique codeline is necessary rather than your 11 codelines !​

​

As a reminder, after using the Macro Recorder, the generated code needs to be optimized …​

​

 

[Jeyarajm2017]

hi @Jeyarajm2017 ,

See if it ok ?

Sub CopyDetails()

Dim ws As Worksheet
Dim i As Long

i = 1

For Each ws In Worksheets
   
    Workbooks("Week.xlsx").Activate
    Worksheets(i).Activate
    ActiveSheet.UsedRange.Clear
   
    ws.Activate
    ws.UsedRange.Copy
   
    Workbooks("Week.xlsx").Activate
    Worksheets(i).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    i = i + 1

Next ws
End Sub

Hi Raghul,
Thanks for the response.
In this code it activates the 1st, 2nd and 3rd sheet. But in my scenario it should look after sheet names like Day1, Day2, Day3 as this sheet may be in different order. So need to specifically look on the sheets names in order. Hope am clear on my expectation.

Kindly help please

Thanks,
Jeyaraj M

 

[Marc L]

Still unclear !​

​

It needs what any forum expects in the initial post : an explanation and an attachment, both smart enough …​

Obviously if the sheets names are the same in both workbooks !​

And as yet explained a good code no needs to activate any sheet to copy some cells …​

​

 

[rahulshewale1]

Hi Raghul,
Thanks for the response.
In this code it activates the 1st, 2nd and 3rd sheet. But in my scenario it should look after sheet names like Day1, Day2, Day3 as this sheet may be in different order. So need to specifically look on the sheets names in order. Hope am clear on my expectation.

Kindly help please

Thanks,
Jeyaraj M

See if is it ok?

 

[Jeyarajm2017]

See if is it ok?

Hi Rahu,
This works perfect... thanks for your help. i have another requirement as well. i am sending you the details with excel attached

I have attached two workbook
1) Source 2) Destination

In source file there are different sheets naming GD 1, GD 2, GD 3 and GD 4
In this four sheets there are details like Check 1, check 2, Check 3 with the values in all the 4 sheets

In my destination file i want the values to be populated for CHECK 1, CHECK 2 AND CHECK 3 next to respective GD 1, GD 2, GD 3 and GD4 from the source file.

Request your help on this please as this is the major activity where i am stuck. Because i have sheets of about 31 sheets similar on this activity.
I have attached sample of 4 sheets for your workings.

Please your support is much appreciated

 

[Marc L]

To Rahul : a smart code needs only an unique codeline in the loop block rather than your 15 codelines !​

​

Jeyarajm2017,​

​

according to your last post - which should be in a new thread according to forum rules ! - but again with a not complete explanation :​

​

1) Where must be located the VBA procedure ?​

​

2) If the Destination workbook already exists as it is in the attachment, is it already opened​

or if not where is it saved, in same folder than Source workbook ?​

If it not exists, so it must be created by the VBA procedure but where to save it, in same folder than Source workbook ?​

​

 

[Jeyarajm2017]

To Rahul : a smart code needs only an unique codeline in the loop block rather than your 15 codelines !​

​

Jeyarajm2017,​

​

according to your last post - which should be in a new thread according to forum rules ! - but again with a not complete explanation :​

​

1) Where must be located the VBA procedure ?​

​

2) If the Destination workbook already exists as it is in the attachment, is it already opened​

or if not where is it saved, in same folder than Source workbook ?​

If it not exists, so it must be created by the VBA procedure but where to save it, in same folder than Source workbook ?​

​

Hi Marc,
Sure this is noted. Sorry for asking my question on the reply mail. I will post in the forum itself

 

[Marc L]

Too late, continue here and just well answer to questions …​

​