Related:
- Excel query
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - Guide
5 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 3, 2010 at 08:28 PM
Jan 3, 2010 at 08:28 PM
try this macro (you can park this in the same module of your earlier macro)
Sub test1() Dim j As Integer, k As Integer j = Worksheets.Count 'MsgBox j For k = 1 To j 'MsgBox Sheets(k).Name If Sheets(k).Name = "Sheet1" Then GoTo nnext Sheets(k).Activate NAME OF THE MACRO nnext: Next k End Sub
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 30, 2009 at 11:47 PM
Dec 30, 2009 at 11:47 PM
It is not clear how you import the file into excel
copy the webpage address in excel clipboard
goto your sheet
select any cell from where you want the file to be copied. e.g. A1
ciick data-import extsernal data(or something like this depending upon the version)-new web query
in the new web query window paste the address of the webpage at the top against "address"
click "go"
you will get the whole webpage. whateer table you want click the tick mark and the whole table will be marked
click "import " at the bottom right
it will ask for where do you want to park it. if it is alrady A1 click ok
try the above instruction. Hope I have not missed any step.
copy the webpage address in excel clipboard
goto your sheet
select any cell from where you want the file to be copied. e.g. A1
ciick data-import extsernal data(or something like this depending upon the version)-new web query
in the new web query window paste the address of the webpage at the top against "address"
click "go"
you will get the whole webpage. whateer table you want click the tick mark and the whole table will be marked
click "import " at the bottom right
it will ask for where do you want to park it. if it is alrady A1 click ok
try the above instruction. Hope I have not missed any step.
Thanks for your quick response. Your answer was very helpful. I have another seprate doubt. I have recorded a macro for a particular worksheet. That macro does two works. 1. There are two cols. ColumnA and Column B. In col C it subtracts Col A from B . 2. It finds the weighted average between col A and C.
Now I want to run this macro in all worksheets except the first worksheet. What is the code to do that.
Please guide me.
Thanks in advance.
Now I want to run this macro in all worksheets except the first worksheet. What is the code to do that.
Please guide me.
Thanks in advance.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 12, 2010 at 05:02 AM
Jan 12, 2010 at 05:02 AM
In the code you have to paste values only. the relevant code will be
<range where it is to be copied>.PasteSpecial Paste:=xlPasteValues
<range where it is to be copied>.PasteSpecial Paste:=xlPasteValues
Thanks for such a quick response.I tried what u suggested but I dont know why its not working. However I have found an alternative to that. The following is the code where i wanted to make changes. Apart from the above doubt I have another query.
I have five formulas running on each of the worksheets in my workbook. they calculate the average, median, mode etc respectively. Now i want these results to be listed on final sheet named as summary.For this I have written the below code.
The problem is that it copies the data one below the other. That is median data is below mean data followed by mode . I want them to be in seprate columns.. eg mean in column A, median in Column B and Mode in Column C and so on. How should I modify the below code?
Thanks in advance.
Sub SummurizeSheets()
Dim ws As Worksheet
Application.ScreenUpdating = False
Sheets("Summary").Activate
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Range("C62:C62").Copy
ws.Range("D90:D90").Copy
ws.Range("D92:D92").Copy
ws.Range("D94:D94").Copy
ws.Range("D96:D96").Copy
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
End If
Next ws
End Sub
I have five formulas running on each of the worksheets in my workbook. they calculate the average, median, mode etc respectively. Now i want these results to be listed on final sheet named as summary.For this I have written the below code.
The problem is that it copies the data one below the other. That is median data is below mean data followed by mode . I want them to be in seprate columns.. eg mean in column A, median in Column B and Mode in Column C and so on. How should I modify the below code?
Thanks in advance.
Sub SummurizeSheets()
Dim ws As Worksheet
Application.ScreenUpdating = False
Sheets("Summary").Activate
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Range("C62:C62").Copy
ws.Range("D90:D90").Copy
ws.Range("D92:D92").Copy
ws.Range("D94:D94").Copy
ws.Range("D96:D96").Copy
ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
End If
Next ws
End Sub
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 12, 2010 at 09:15 PM
Jan 12, 2010 at 09:15 PM
It is always a problem to copy non contiguous cells and that too with transposing them
try whether this macro helps you
try whether this macro helps you
Sub test()
Dim dest As Range, ws As Worksheet, j As Integer
For Each ws In Worksheets
If ws.Name <> "summary" Then
j = 0
Set dest = Worksheets("summary").Range("a62556").End(xlUp).Offset(1, 0)
With ws
.Range("c62").Copy dest.Offset(0, j)
j = j + 1
.Range("d90").Copy dest.Offset(0, j)
j = j + 1
.Range("d92").Copy dest.Offset(0, j)
j = j + 1
.Range("d92").Copy dest.Offset(0, j)
j = j + 1
.Range("d94").Copy dest.Offset(0, j)
j = j + 1
.Range("d96").Copy dest.Offset(0, j)
End With
End If
Next ws
End Sub
Didn't find the answer you are looking for?
Ask a question
Jan 11, 2010 at 10:48 AM
Jan 12, 2010 at 02:24 AM
I have written a code for that but the problem is that it copies the formulas instead of the values. Hence it changes the reference and gives invalid result. Please guide me.