Excel query [Closed]

Report
-
 sunil -
Hello,


I have a query. Whenever I export a file from internet the data gets saved into a new excel file instead of the excel sheet I am working in.As a result I have to copy the data from that file to the excel file I am working in . This is very time consuming. Is there a shorter method to directly export the data into the working excel file?
Please guide me.


Thanking You,
Best Regards,
Tanya Gulati

5 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2841 users have said thank you to us this month

Thanks a lot... it did work
I have another query if u can help me out. I have recorded a formula using macro to find the weighted average of two columns. I have run this macro on 75 worksheets. Now in another worksheet i want to make a list of the values of weighted averages that i have found in each worksheet.

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.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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.
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.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
In the code you have to paste values only. the relevant code will be

<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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
It is always a problem to copy non contiguous cells and that too with transposing them

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
Thanks a lot. The code worked on the first try itself.
in the same file go to data then import then at source place the web url then open the file you will get the data where you have put the click.