Copy/Paste macro from a web query

[schelber]

Hello, I have an easy one for someone , but i am looking for the most concise version.

I have a workbook with 30 worksheets and i import data into each one using a datarefresh of a web query.


I am looking to take the contents of H11:H206 from every worksheet in the book and copy into successive columns starting with 'B'.

The worksheets are named ( i.e. Sam,Wendy etc.....if that matters ).

Does anyone know what the simplest macro would look like to do that.

Regards , Rich

 

[dave3009]

Hi Rich

Does the name of the workbook change when you use datarefresh and is the location you're looking to copy to a master - if so what is it called


Dave

 

[schelber]

Hi Dave, no the name of the workbook itself does not change. Right now it is called Training.xls.

Basically I data imported into every worksheet once and from that point forward i just refresh the data and the client-server link is made.

The 'master' location is simply a 31st worksheet in the workbook called "Report" whereby these columns get written to.

Regards, Rich

 

[dave3009]

This might be a little too simple for your needs but I reckon it can be done with out code.

I assume each column has the title with the name of the individual in question. If not it doesn't really matter.

In B2 (if there is a title B1 if not) Use formula

='Sam'!H11

You would go along each column changing Sam for whatever name is the next sheet.

Once all the columns are done simply copy them all highlight down to row 195 (I think) and paste. Evry time you refresh the data will automatically update.

If this is to simple and you do want code let me know

HTH


Dave

 

[schelber]

ha, i see what you are saying. let me just go with that. thanks.\

Rich

 

[dave3009]

While your trying it why not run the Macro Recorder, it'll spit out the code for you to edit and use at a later date should (for example) the workbook change is't name or you rebuild it.

Dave

 

[dave3009]

Code incase you need it for future use would go something like

Sub FillOutRanges()
With ActiveWorkbook.Sheets("Reports")

    .Range("B1").Value = "='Sam'!H11"
    .Range("C1").Value = "='Wendy'!H11"
' Fill out for all your names remember to change column letter as well
        
        .Range("B1:AE1").Copy
        .Range("B1:AE195").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    End With
End Sub

Dave

 

[schelber]

thanks for your help with this.

Rich