Help with Macros

D

deepak_bhalla

New Member
Joined
Sep 3, 2015
Messages
5
Hi !

I am trying to make a dashboard for a particular excel file in which I have around 30 sheets in similar format. I need to link certain data fields on my dashboard. WHile trying to do that I created the following Macro however it only works for first 3 sheets. I want this to run for all sheet in the file.


Sub Macro1()
'
' Macro1 Macro
'


'
Sheets("ASGS").Select
Range("A3:D3").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("B3").Select
Sheets("ASGS").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("C3").Select
Sheets("ASGS").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D3").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("E3").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("F3").Select
ActiveSheet.Paste Link:=True
Range("G3").Select
Sheets("ASGS").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("H3").Select
ActiveSheet.Paste Link:=True
Range("A4").Select
Sheets("AIS").Select
Range("A3:D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("C4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("E4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("F4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("G4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("H4").Select
ActiveSheet.Paste Link:=True
Range("A5").Select
Sheets("APJ Pitampura").Select
Range("A3:D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("APJ Pitampura").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B5").Select
ActiveSheet.Paste Link:=True
Sheets("APJ Pitampura").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("C5").Select
ActiveSheet.Paste
Sheets("APJ Pitampura").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D5").Select
ActiveSheet.Paste
Range("E5").Select
Sheets("APJ Pitampura").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("F5").Select
Sheets("APJ Pitampura").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("G5").Select
Sheets("APJ Pitampura").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("APJ Pitampura").Select
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("H5").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
ChDir "Y:\Data\E\A\A Reports"
End Sub

Please help.

Thanks and Regards

Deepak Bhalla
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
deepak_bhalla said:
Hi !

I am trying to make a dashboard for a particular excel file in which I have around 30 sheets in similar format. I need to link certain data fields on my dashboard. WHile trying to do that I created the following Macro however it only works for first 3 sheets. I want this to run for all sheet in the file.


Sub Macro1()
'
' Macro1 Macro
'


'
Sheets("ASGS").Select
Range("A3:D3").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("B3").Select
Sheets("ASGS").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("C3").Select
Sheets("ASGS").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D3").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("E3").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("F3").Select
ActiveSheet.Paste Link:=True
Range("G3").Select
Sheets("ASGS").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("H3").Select
ActiveSheet.Paste Link:=True
Range("A4").Select
Sheets("AIS").Select
Range("A3:D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("C4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("E4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("F4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("G4").Select
ActiveSheet.Paste Link:=True
Sheets("AIS").Select
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("H4").Select
ActiveSheet.Paste Link:=True
Range("A5").Select
Sheets("APJ Pitampura").Select
Range("A3:D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("APJ Pitampura").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B5").Select
ActiveSheet.Paste Link:=True
Sheets("APJ Pitampura").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("C5").Select
ActiveSheet.Paste
Sheets("APJ Pitampura").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("D5").Select
ActiveSheet.Paste
Range("E5").Select
Sheets("APJ Pitampura").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("F5").Select
Sheets("APJ Pitampura").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("G5").Select
Sheets("APJ Pitampura").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Sheets("APJ Pitampura").Select
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("H5").Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
ChDir "Y:\Data\E\A\A Reports"
End Sub

Please help.

Thanks and Regards

Deepak Bhalla
Click to expand...


deepak_bhalla,

Hello and welcome to the forum. I reviewed your code and was unclear on one item:
First you copy cells A3:D3 from Sheet ASGS to cells A3:D3 on Sheet1.
Then, with subsequent code, you paste into cells B3, C3, D3 on Sheet1, right over the top of what you just copied.
Perhaps you can explain that logic.

The code below puts the sheet name in the first row, the range A3:D3 on the next row, and the remainder items on the next row.
A blank row is placed between data from each sheet.
I tested the code on two sheets by placing text in the cells you copied to Sheet1.
You should test this on a copy of your workbook. Place the code in a standard module.
Perhaps this will get your going in the right direction. Good Luck!

Perpa

Code: 
Sub Test()
Dim wSheet As Worksheet
Dim rw As Long

rw = 3
Set wb = ActiveWorkbook
For Each wSheet In wb.Worksheets
   If wSheet.Name = "Sheet1" Then GoTo PASS     'This skips Sheet1, you can add similar 'IF' statements if you have other sheets to skip
   Cells(rw - 1, 1) = wSheet.Name
   wSheet.Range("A3:D3").Copy Sheets("Sheet1").Range("A" & rw)
   rw = rw + 1
   wSheet.Range("B6").Copy Sheets("Sheet1").Range("B" & rw)
   wSheet.Range("B7").Copy Sheets("Sheet1").Range("C" & rw)
   wSheet.Range("B8").Copy Sheets("Sheet1").Range("D" & rw)
   wSheet.Range("B12").Copy Sheets("Sheet1").Range("E" & rw)
   wSheet.Range("B13").Copy Sheets("Sheet1").Range("F" & rw)
   wSheet.Range("E12").Copy Sheets("Sheet1").Range("G" & rw)
   wSheet.Range("E13").Copy Sheets("Sheet1").Range("H" & rw)
   rw = rw + 3
PASS:
Next wSheet
End Sub
 
Upvote 0
<!--?xml version="1.0" encoding="UTF-8" ?--> Hi ! Perpa,

Thanks indeed for your reply and the welcome.The cells A3:d3 were being copied as they were merged cells so some duplication was there. Now I have generated a new code and want to run that in all my sheets. Please find the code below.
Sub data2dashboard()
'
' data2dashboard Macro
'
'
Sheets("ASGS").Select
Range("A3").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Sheets("ASGS").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("C3").Select
Sheets("ASGS").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("D3").Select
Sheets("ASGS").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("E3").Select
Sheets("ASGS").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("F3").Select
Sheets("ASGS").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
ActiveWindow.SmallScroll ToRight:=2
Sheets("ASGS").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("G3").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("H3").Select
ActiveSheet.Paste Link:=True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub

Thanks again for your help.

Regards

Deepak Bhalla
 
Upvote 0
deepak_bhalla said:
<!--?xml version="1.0" encoding="UTF-8" ?--> Hi ! Perpa,

Thanks indeed for your reply and the welcome.The cells A3:d3 were being copied as they were merged cells so some duplication was there. Now I have generated a new code and want to run that in all my sheets. Please find the code below.
Sub data2dashboard()
'
' data2dashboard Macro
'
'
Sheets("ASGS").Select
Range("A3").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Sheets("ASGS").Select
Range("B6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("C3").Select
Sheets("ASGS").Select
Range("B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("D3").Select
Sheets("ASGS").Select
Range("B8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("E3").Select
Sheets("ASGS").Select
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
Range("F3").Select
Sheets("ASGS").Select
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste Link:=True
ActiveWindow.SmallScroll ToRight:=2
Sheets("ASGS").Select
Range("E12").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("G3").Select
ActiveSheet.Paste Link:=True
Sheets("ASGS").Select
Range("E13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("H3").Select
ActiveSheet.Paste Link:=True
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub

Thanks again for your help.

Regards

Deepak Bhalla
Click to expand...


Deepak Bhalla,
You didn't say whether or not you tried the code I furnished previously on a copy of your workbook.
I should have asked earlier, what version of Excel and Windows are your running? I am running Excel 7
on a PC with Windows 7.

I have revised the code based on the latest code you have furnished...see below. Perhaps you might
furnish some information on the contents of Sheet "ASGS" in cells A3, B6, B7, B8, B12, B13, E12, & E13
so we can see the type of data/formulae you are working with. As I said earlier, I tested my code with
'text' entries in those cells, and it performed as expected. It will work for as many sheets as are in your
workbook.
Perpa

Code: 
Sub Test2()
Dim wSheet As Worksheet
Dim rw As Long

rw = 3
Set wb = ActiveWorkbook
For Each wSheet In wb.Worksheets
   If wSheet.Name = "Sheet1" Then GoTo PASS     'This skips Sheet1, you can add similar 'IF' statements if you have other sheets to skip
   Cells(rw - 1, 1) = wSheet.Name
   wSheet.Range("A3").Copy Sheets("Sheet1").Range("A" & rw)
   wSheet.Range("B6").Copy Sheets("Sheet1").Range("B" & rw)
   wSheet.Range("B7").Copy Sheets("Sheet1").Range("C" & rw)
   wSheet.Range("B8").Copy Sheets("Sheet1").Range("D" & rw)
   wSheet.Range("B12").Copy Sheets("Sheet1").Range("E" & rw)
   wSheet.Range("B13").Copy Sheets("Sheet1").Range("F" & rw)
   wSheet.Range("E12").Copy Sheets("Sheet1").Range("G" & rw)
   wSheet.Range("E13").Copy Sheets("Sheet1").Range("H" & rw)
   rw = rw + 1
PASS:
Next wSheet
End Sub
 
Upvote 0
Hi ! Perpa,
Thank you very much for your quick response.

Kindly forgive me for not being able to furnish full details earlier.

I tested the earlier code and it brought Name of the Sheets in the row and then the headings I wanted, however instead of linking the cells to my dashboard it copied the formula. The second code pasted values in range of b2 and range of c2 and for rest of the columns it pasted the formula. I am trying to make a user activity report for a site. Sheet 1 is my dashboard where I am trying to link data from activity reports I have made for different categories of user like ASGS. The Data contents of the cells are as follows :

Sheet ASGS
A3 contains the Full Name of that category
B7 contains a numeric value (the total number of type 1 users from Category ASGS)
B8 contains a numeric value (the total number of type 2 users from Category ASGS)
B6 = B7+B8
B12 contains a percentage value for which the base is in B10 and B12 is calculated as =B10/B7 however I don't need the actual number of B10 in my dashboard, I require only the percentage values available in B12.
B13 contains a percentage value for which the base is in B11 and B13 is calculated as =B11/B7 however I don't need the actual number of B11 in my dashboard, I require only the percentage values available in B13.

Cell E12 and E13 contains the same type of percentage values for type 2 users as for B12 & B13 which is for type 1 users.

I am looking to create links from my sheets to my dashboard and I have around 30 sheets in my file. I am using Cross Over Excel 2007 on Xubuntu.

Thanks again for patience and your quick replies.

Best regards

Deepak Bhalla
 
Upvote 0
deepak_bhalla said:
Hi ! Perpa,
Thank you very much for your quick response.

Kindly forgive me for not being able to furnish full details earlier.

I tested the earlier code and it brought Name of the Sheets in the row and then the headings I wanted, however instead of linking the cells to my dashboard it copied the formula. The second code pasted values in range of b2 and range of c2 and for rest of the columns it pasted the formula. I am trying to make a user activity report for a site. Sheet 1 is my dashboard where I am trying to link data from activity reports I have made for different categories of user like ASGS. The Data contents of the cells are as follows :

Sheet ASGS
A3 contains the Full Name of that category
B7 contains a numeric value (the total number of type 1 users from Category ASGS)
B8 contains a numeric value (the total number of type 2 users from Category ASGS)
B6 = B7+B8
B12 contains a percentage value for which the base is in B10 and B12 is calculated as =B10/B7 however I don't need the actual number of B10 in my dashboard, I require only the percentage values available in B12.
B13 contains a percentage value for which the base is in B11 and B13 is calculated as =B11/B7 however I don't need the actual number of B11 in my dashboard, I require only the percentage values available in B13.

Cell E12 and E13 contains the same type of percentage values for type 2 users as for B12 & B13 which is for type 1 users.

I am looking to create links from my sheets to my dashboard and I have around 30 sheets in my file. I am using Cross Over Excel 2007 on Xubuntu.

Thanks again for patience and your quick replies.

Best regards

Deepak Bhalla
Click to expand...


Deepak Bhalla,
Here is some revised code that will give the results you indicated.
I commented out the sheet name since you already get that in cell A3.
It was easier to recalculate the information than to copy it from each sheet.
That is what the 'WITH' statement below is doing. I trust this format will work for you.
Perpa

Code: 
Sub Test3()
    Dim wSheet As Worksheet
    Dim rw As Long
    
    rw = 3
    Set wb = ActiveWorkbook
    For Each wSheet In wb.Worksheets
       If wSheet.Name = "Sheet1" Then GoTo PASS     'This skips Sheet1, you can add similar 'IF' statements if you have other sheets to skip
       
       'Cells(rw - 1, 1) = wSheet.Name              'Uncomment this line if you want the sheet name
       wSheet.Range("A3").Copy Sheets("Sheet1").Range("A" & rw)
       rw = rw + 1
       wSheet.Range("B7").Copy Sheets("Sheet1").Range("C" & rw)
       wSheet.Range("B8").Copy Sheets("Sheet1").Range("D" & rw)
    
            With Sheets("Sheet1")
                 Cells(rw, 2) = Cells(rw, 3) + Cells(rw, 4)         '= B6
                 Cells(rw, 5) = wSheet.Cells(10, 2) / Cells(rw, 3)  	'= B10/B7
                 Cells(rw, 6) = wSheet.Cells(11, 2) / Cells(rw, 3)  	'= B11/B7
                 Cells(rw, 7) = wSheet.Cells(10, 2) / Cells(rw, 4)  	'= B10/B8
                 Cells(rw, 8) = wSheet.Cells(11, 2) / Cells(rw, 4)  	'= B11/B8
                 Columns("E:H").NumberFormat = "0%"
            End With
        rw = rw + 1
PASS:
    Next wSheet
End Sub
 
Upvote 0
Hi ! Perpa,

Thank you very much for your help and your time.

This code works however I am looking to create links rather pasting values or recalculation on dashboard. My sheets are dynamic in nature and I will be mostly working on sheets and the dashboard I am making is for my boss. My idea is that I work on sheets and forget about dashboard let "pasted links" do that for me.

Please see if you can rework this and help me.

Thanks and regards

Deepak Bhalla
 
Upvote 0
deepak_bhalla said:
Hi ! Perpa,

Thank you very much for your help and your time.

This code works however I am looking to create links rather pasting values or recalculation on dashboard. My sheets are dynamic in nature and I will be mostly working on sheets and the dashboard I am making is for my boss. My idea is that I work on sheets and forget about dashboard let "pasted links" do that for me.

Please see if you can rework this and help me.

Thanks and regards

Deepak Bhalla
Click to expand...

Deepak Bhalla,
I used elements of your working code for "ASGS" along with some from my code where 'wSheet'
allows you to go through each of your worksheets. See if this better fits your needs.
Regards,
Perpa


Code: 
Sub data2dashboard()

Dim wSheet As Worksheet
Dim rw As Long
    
    rw = 3
    Set wb = ActiveWorkbook
    
    For Each wSheet In wb.Worksheets
         If wSheet.Name = "Sheet1" Then GoTo PASS

         Sheets("Sheet1").Range("A" & rw).Select     'Perpa added: there must be a selected cell on Sheet1 for this to work
         Sheets(wSheet).Select
         Range("A3").Select
         Selection.Copy
         Sheets("Sheet1").Select
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
        
         Range("B" & rw).Select
         Sheets(wSheet).Select
         Range("B6").Select
         Application.CutCopyMode = False
         Selection.Copy
         Sheets("Sheet1").Select
         ActiveSheet.Paste Link:=True
        
         Range("C" & rw).Select
         Sheets(wSheet).Select
         Range("B7").Select
         Application.CutCopyMode = False
         Selection.Copy
         Sheets("Sheet1").Select
         ActiveSheet.Paste Link:=True
        
         Range("D" & rw).Select
         Sheets(wSheet).Select
         Range("B8").Select
         Application.CutCopyMode = False
         Selection.Copy
         Sheets("Sheet1").Select
         ActiveSheet.Paste Link:=True
         
         Range("E" & rw).Select
         Sheets(wSheet).Select
         Range("B12").Select
         Application.CutCopyMode = False
         Selection.Copy
         Sheets("Sheet1").Select
         ActiveSheet.Paste Link:=True
         
         Range("F" & rw).Select
         Sheets(wSheet).Select
         Range("B13").Select
         Application.CutCopyMode = False
         Selection.Copy
         Sheets("Sheet1").Select
         ActiveSheet.Paste Link:=True
         
         ActiveWindow.SmallScroll ToRight:=2
         Sheets(wSheet).Select
         Range("E12").Select
         Application.CutCopyMode = False
         Selection.Copy
         Sheets("Sheet1").Select
         Range("G" & rw).Select
         ActiveSheet.Paste Link:=True
         
         Sheets(wSheet).Select
         Range("E13").Select
         Application.CutCopyMode = False
         Selection.Copy
         Sheets("Sheet1").Select
         Range("H" & rw).Select
         ActiveSheet.Paste Link:=True
         
         'ActiveWindow.ScrollColumn = 2    'You can uncomment these 2 lines if needed
         'ActiveWindow.ScrollColumn = 1
        
         rw = rw + 1
PASS:
    Next wSheet

End Sub
 
Upvote 0
Hi ! Perpa,

Thanks indeed I really appreciate all your help.

I have not been to run the code as my excel has started giving problems due to some bugs and since I using a cross over version I can't get hotfixes. I have now handed over the issue to our IT.

Thanks again.

Best regards

Deepak Bhalla
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
189
kbk
K
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
245
willow1985
willow1985
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
420
myall_blues
myall_blues
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
392
pennbowl
P
J
  • Question Question
Code stopped working
Replies
1
Views
268
rlv01
R

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

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