Copy/Paste macro from a web query

schelber

Board Regular
Joined
Oct 22, 2005
Messages
172
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016
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

Board Regular
Joined
Oct 22, 2005
Messages
172
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016
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

Board Regular
Joined
Oct 22, 2005
Messages
172

ADVERTISEMENT

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

Rich
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016
Code incase you need it for future use would go something like

Code:
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
 

Forum statistics

Threads
1,141,139
Messages
5,704,508
Members
421,353
Latest member
jekoxien15

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
Top