Repeating VBA code for multiple Sheet names and References

[Jsingh96]

Hi All,

I'm hoping I can get help with the below code, repeating it for a number of sheets.

I am downloading data from a sharepoint, and pasting it into the relevant named sheet, for example retrieve data from X and paste in X_Sheet.

I want to do the above multiple times with a loop, possible referencing a table?

_______________________________________________________________________________________________________
Private Sub CopySheetFromSPOINT()

Application.DisplayAlerts = False

Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("https://websitename.................xlsx")
Set y = ThisWorkbook

x.Sheets("tab_name").Cells.Copy
y.Sheets("tab_namex").Cells.PasteSpecial
y.Sheets("tab_namex").Cells.Copy
y.Sheets("tab_namex").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False


Application.AskToUpdateLinks = False

Application.CutCopyMode = False

Application.AskToUpdateLinks = False



x.Close

Application.DisplayAlerts = True
End Sub


_________________________________________________________________________________

I am hoping that I can reference the download source as column 1 (i.e. the web address in the cell) and the column 2 is the paste source - and loop it

 

[Dan_W]

Hi

So I wasn't sure if your code would work, but (after some tweaking) it appears to. This will loop through a selection of cells, and each of which will presumably contain a URL. It will add a new worksheet each time it goes to access the URL. If you want it to go through a table rather than a selection of cells, that's not difficult.

Private Sub CopySheetFromSPOINT()

Application.DisplayAlerts = False
Dim ws As Worksheet
Dim x As Workbook
Dim y As Workbook
Dim rngURL As Range
Dim cll as Range

Set rngURL = Selection
On Error GoTo errHandler

For Each cll In rngURL

    DoEvents

    Set x = Workbooks.Open(cll.Value)
    Set y = ThisWorkbook
   
    Set ws = y.Sheets.Add
    x.Sheets(1).Cells.Copy
    ws.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Application.CutCopyMode = False
   
    x.Close

Next
errHandler:
If Err.Number <> 0 Then MsgBox "There was an error." & vbNewLine & "Error " & Err.Number & vbTab & Err.Description, vbOKOnly, "Error"
Application.DisplayAlerts = True
Set x = Nothing
Set y = Nothing
Set ws = Nothing
End Sub

 

[Jsingh96]

Hi

So I wasn't sure if your code would work, but (after some tweaking) it appears to. This will loop through a selection of cells, and each of which will presumably contain a URL. It will add a new worksheet each time it goes to access the URL. If you want it to go through a table rather than a selection of cells, that's not difficult.

Private Sub CopySheetFromSPOINT()

Application.DisplayAlerts = False
Dim ws As Worksheet
Dim x As Workbook
Dim y As Workbook
Dim rngURL As Range
Dim cll as Range

Set rngURL = Selection
On Error GoTo errHandler

For Each cll In rngURL

    DoEvents

    Set x = Workbooks.Open(cll.Value)
    Set y = ThisWorkbook
  
    Set ws = y.Sheets.Add
    x.Sheets(1).Cells.Copy
    ws.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Application.CutCopyMode = False
  
    x.Close

Next
errHandler:
If Err.Number <> 0 Then MsgBox "There was an error." & vbNewLine & "Error " & Err.Number & vbTab & Err.Description, vbOKOnly, "Error"
Application.DisplayAlerts = True
Set x = Nothing
Set y = Nothing
Set ws = Nothing
End Sub

Hi Dan,

Thanks a lot for providing the assistance, and I can confirm the code runs correctly however - I need to pull a specific name of a sheet (which repeats for all other workbooks I access) where would I put this in the code? (i.e. SHEET_XYX)


Thanks!

 

[Jsingh96]

Hi

So I wasn't sure if your code would work, but (after some tweaking) it appears to. This will loop through a selection of cells, and each of which will presumably contain a URL. It will add a new worksheet each time it goes to access the URL. If you want it to go through a table rather than a selection of cells, that's not difficult.

Private Sub CopySheetFromSPOINT()

Application.DisplayAlerts = False
Dim ws As Worksheet
Dim x As Workbook
Dim y As Workbook
Dim rngURL As Range
Dim cll as Range

Set rngURL = Selection
On Error GoTo errHandler

For Each cll In rngURL

    DoEvents

    Set x = Workbooks.Open(cll.Value)
    Set y = ThisWorkbook
  
    Set ws = y.Sheets.Add
    x.Sheets(1).Cells.Copy
    ws.Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

    Application.CutCopyMode = False
  
    x.Close

Next
errHandler:
If Err.Number <> 0 Then MsgBox "There was an error." & vbNewLine & "Error " & Err.Number & vbTab & Err.Description, vbOKOnly, "Error"
Application.DisplayAlerts = True
Set x = Nothing
Set y = Nothing
Set ws = Nothing
End Sub

Hi Dan,

Figured out where the referencing of sheet goes for the pull ; Could you point me in the right direction for referencing a range of cells (adjacent to the website column range) to name the sheet created for the pulled worksheet?

Much appreciated, I have never really used VBA; just SQL!


Cheers

 

[Dan_W]

If the cells with the website URLs are in a workbook called MYWORKBOOK.XLSM on SHEET_XYX in (for example) cells B2, B3, B4, then just replace this line:

Set rngURL = Selection

with:

Set rngURL = Workbooks("MYWORKBOOK.XLSM").Sheets("SHEET_XYX").Range("B2:B4")

Does that work?

 

[Jsingh96]

If the cells with the website URLs are in a workbook called MYWORKBOOK.XLSM on SHEET_XYX in (for example) cells B2, B3, B4, then just replace this line:

Set rngURL = Selection

with:

Set rngURL = Workbooks("MYWORKBOOK.XLSM").Sheets("SHEET_XYX").Range("B2:B4")

Does that work?

Hi Dan,

Thanks for the reply, yes I did change this previously and it works great for running through the cells and pulling the data, however, when it creates the new sheet to paste the data in; the names are just Sheet (x) - I need the new sheet to be named with a specific range too... adjacent cells to the URL (e.g. URL WWW.XYX.COM in the first column, and another column with the name XYZ) -

Cheers