logo
down
shadow

Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete


Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete

By : Kentucky Fried Chick
Date : November 17 2020, 04:28 AM
Hope that helps Made some syntax tweaks that should help as well as error handling if the sheet name already exists. Didn't make any changes to the QueryTable.Add method though.
code :
Sub CreateSheetsFromAList()

    Dim wb As Workbook
    Dim src As Worksheet
    Dim tgt As Worksheet
    Dim ActSht As Worksheet
    Dim MyCell As Range, MyRange As Range, URLValue As Variant

    Set wb = ThisWorkbook
    Set src = wb.Sheets("AllPlayers")
    Set MyRange = src.Range("A1:A" & src.Range("A" & src.Rows.Count).End(xlUp).Row)

    For Each MyCell In MyRange
        On Error Resume Next
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        If Err.Number > 0 Then
            Err.Clear
            MsgBox MyCell.Value & " sheet name already exists"
            Exit Sub
        End If
        On Error Goto 0
        URLValue = MyCell.Offset(0, 1).Value
        Set ActSht = Sheets(Chr(34) & MyCell.Value & Chr(34))
        With ActSht.QueryTables.Add(Connection:= "URL;" & URLValue, Destination:=ActSht.Range("A2"))
          .Name = "2015"
          .FieldNames = True
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .BackgroundQuery = True
          .RefreshStyle = xlInsertDeleteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .WebSelectionType = xlSpecifiedTables
          .WebFormatting = xlWebFormattingNone
          .WebTables = """pgl_basic"""
          .WebPreFormattedTextToColumns = True
          .WebConsecutiveDelimitersAsOne = True
          .WebSingleBlockTextImport = False
          .WebDisableDateRecognition = False
          .WebDisableRedirections = False
          .Refresh BackgroundQuery:=False
        End With
    Next MyCell
End Sub


Share : facebook icon twitter icon
Rename Excel Sheet with VBA Macro

Rename Excel Sheet with VBA Macro


By : M.l. Shyam
Date : March 29 2020, 07:55 AM
hope this fix your issue I want to ask about rename the excel sheet, i want to rename the sheet with new name : older name + _v1. , This should do it:
code :
WS.Name = WS.Name & "_v1"
Macro loop to pull data from sheet to sheet stopping when no data resent and not entering 0

Macro loop to pull data from sheet to sheet stopping when no data resent and not entering 0


By : Dalton Pulsipher
Date : March 29 2020, 07:55 AM
Does that help I am trying to get the data from rows G,H, and I from row 15 down (stopping when there is no data present)from "order sheet", to be pasted in a sheet called “Data” in columns M,Q and R from row 8 down. , Here is a way to copy all values from G15 downwards to M8 downwards
code :
Dim sourceSht As Worksheet 'sheet you want to copy from 
Dim targetSht As Worksheet 'sheet you want to copy too
Dim sourceRng As Range 'what you want to copy
Dim targetRng As Range 'where you want to copy to
Dim lastRow As Long 'the last row that holds data

Set sourceSht = Sheets("Order Sheet")
Set targetSht = Sheets("Data")

With sourceSht
    lastRow = .Cells(.Rows.Count,7).End(xlUp).Row '7 is the column number
End With

Set sourceRng = sourceSht.Range("G15:G" & lastRow)
Set targetRng = targetSht.Range("M8").Resize(sourceRng.Rows.Count, 1) ' Range("M8:M"& lastRow - 7) would also be possible
targetRng.Value = sourceRng.Value
VBA Macro wanted - Loop copying data from one sheet to another

VBA Macro wanted - Loop copying data from one sheet to another


By : RaviS
Date : March 29 2020, 07:55 AM
hope this fix your issue EDIT - As JvdV pointed out, copy pasting is not really necessary, so I changed the code to report.Sheets[...].Value = data.Sheets[...].Value instead, which is much, much faster. Thank you again, JvdV.
code :
Sub extract()

Dim RNG1 As Range, CL1 As Range
Dim LR1 As Long, LR2 As Long, LC As Long

Set report = Workbooks("Main.xlsm")
Set data = Workbooks("API_NE.EXP.GNFS.CD_DS2_en_excel_v2_9944773.xls")

report.Sheets("Report").Activate
data.Sheets("Data").Activate

LR1 = report.Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = data.Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
RC2 = report.Sheets("Report").Cells(LR1, Columns.Count).End(xlToLeft).Column + 1
RC3 = RC2 + 1

Set RNG1 = data.Sheets("Data").Range(Cells(1, 1), Cells(LR2, 1))
report.Sheets("Report").Cells(1, RC2).Value = data.Sheets("Data").Cells(5, 3).Value
report.Sheets("Report").Cells(1, RC3).Value = "Year"

For X = 2 To LR1
    With RNG1
        Set CL1 = .Find(What:=report.Sheets("Report").Cells(X, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not CL1 Is Nothing Then
            LC1 = data.Sheets("Data").Cells(CL1.Row, Columns.Count).End(xlToLeft).Column
            If IsNumeric(data.Sheets("Data").Cells(CL1.Row, LC1)) Then
                report.Sheets("Report").Cells(LR1, RC2).End(xlUp).Offset(1, 0).Value = data.Sheets("Data").Cells(CL1.Row, LC1).Value
            Else
                report.Sheets("Report").Cells(LR1, RC2).End(xlUp).Offset(1, 0).Value = "N/A"
            End If

            If IsNumeric(data.Sheets("Data").Cells(CL1.Row, LC1)) Then
                report.Sheets("Report").Cells(LR1, RC3).End(xlUp).Offset(1, 0).Value = data.Sheets("Data").Cells(4, LC1).Value
            Else
                report.Sheets("Report").Cells(LR1, RC3).End(xlUp).Offset(1, 0).Value = "N/A"
            End If


        End If
    End With
Next X

report.Sheets("Report").Activate

With Worksheets("Report").Columns(RC2)
    .NumberFormat = "0.00"
    .Value = .Value
End With

With Worksheets("Report").Columns(RC3)
    .NumberFormat = "0"
    .Value = .Value
End With

End Sub
Trim blank data/rows, rename column headers based on sheet names, move data from one sheet to another.

Trim blank data/rows, rename column headers based on sheet names, move data from one sheet to another.


By : stam
Date : March 29 2020, 07:55 AM
it helps some times You can do that without macros using Consolidation. If you're a beginner, I suggest you start trying to solve the problem with existing solutions, before going to macros...
Excel Macro to give Input Box, Create New Sheet, Copy Data from Original Sheet into New Sheet

Excel Macro to give Input Box, Create New Sheet, Copy Data from Original Sheet into New Sheet


By : Bernd Keuning
Date : March 29 2020, 07:55 AM
will be helpful for those in need I am trying to create a Macro that will Present an Input box once a button is clicked, simply saying "What would you like to call the new sheet?", then once the user has entered the name, Excel create a new sheet with that name. , Try the code below:
Related Posts Related Posts :
  • Highlighting the second and more duplicate rows in excel but not first occurrence?
  • Excel add- in focus not returned to the active sheet
  • How to restrict VBA code to a specific worksheet?
  • How to insert column name in the destination table in ssis?
  • Insert text into the background of a cell
  • how do I generate random timestamps in Excel
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Huge excel file... any advice?
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org