Consulting

Results 1 to 6 of 6

Thread: Data transfer from online excel to offline excel only specfic columns

  1. #1

    Data transfer from online excel to offline excel only specfic columns

    Hi,everyone hope you are doing great
    I want to transfer specific column data from my online excel to offline excel dynamic range.
    In the screenshot, I have shown you online excel data and how I can transfer it offline to excel dynamic range automatically
    for better understanding, I have attached a youtube video link for you.

    https://www.youtube.com/watch?v=Sv1_eZYcd5Q


    here I also attached an example video link what I want you can see

    https://www.youtube.com/watch?v=V6dNzy1RATE


    Can we can do this Excel with Vba code ?oh let suppose i transfer this online excel sheet to offline then run a macro to seperate a specfic columns

    if you need further detail please tell me i will help you
    Attached Images Attached Images
    Last edited by KAMRAN AJ; 03-16-2023 at 04:56 AM.

  2. #2
    you can use Power Query to Import your Online data?

    2023-03-16_20-56-03.png

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Please stop referring us to videos that are woeful to watch. In the first video your mouse is like a moth around a light (very distracting) and the second video you are clearly rambling. Simply take the time to explain in words what you are wanting to achieve.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    sorry brother, but I make a video to try to make you understand in a better way next time I will try my best to explain all in words.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by KAMRAN AJ View Post
    sorry brother, but I make a video to try to make you understand in a better way next time I will try my best to explain all in words.
    Try this time.... so we can assist you. With that in mind you should be aiming to do the following;

    1. Explain in real terms what you have and what you would like to see happen
    2. Supply a sample workbook
    3. Only supply Images if it's very complicated to explain e.g. error messages

    if you don't understand how this can be done, sing out and we will help you.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    VBAX Regular
    Joined
    May 2018
    Location
    Sydney
    Posts
    57
    Location
    I'll give it a go

    Make sure to place this into the "OFFLINE" workbook as the information is going to be copied into it.

    Option Explicit
    
    Sub CopyDynamicColumns()
        Dim sourceWorkbook As Workbook
        Dim offlineWorksheet As Worksheet
        Dim sourceWorksheet As Worksheet
        Dim lastRow As Long
        Dim dynamicColumns() As Variant
        Dim col As Variant
        Dim i As Integer
        Dim destCol As Integer
    
    
        ' Define the columns you want to copy (1-based index)
        dynamicColumns = Array(2, 4, 6) ' Change this array to include the columns you want to copy from. It is currently set to copy columns B, D & F
    
    
        ' Set the offline worksheet where you want to paste the data
        Set offlineWorksheet = ThisWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your OFFLINE (Targets) sheet's name
    
    
        ' Open the online workbook
        Set sourceWorkbook = Workbooks.Open("https://your_online_excel_file_url_here.xlsx") ' Replace with the online Excel file URL
    
    
        ' Set the source worksheet from which you want to copy the data
        Set sourceWorksheet = sourceWorkbook.Worksheets("Sheet1") ' Change "Sheet1" to your ONLINE (Source) sheet's name
    
    
        ' Find the last row with data in the source worksheet
        lastRow = sourceWorksheet.Cells(sourceWorksheet.Rows.Count, "A").End(xlUp).Row
    
    
        ' Loop through each column you want to copy
        destCol = 1
        For Each col In dynamicColumns
            ' Copy the column from the source worksheet
            sourceWorksheet.Range(sourceWorksheet.Cells(1, col), sourceWorksheet.Cells(lastRow, col)).Copy
    
    
            ' Paste the copied data into the offline worksheet
            offlineWorksheet.Cells(1, destCol).PasteSpecial xlPasteValues
    
    
            ' Move to the next destination column
            destCol = destCol + 1
        Next col
    
    
        ' Close the online workbook without saving changes
        sourceWorkbook.Close SaveChanges:=False
    
    
        ' Clean up
        Application.CutCopyMode = False
        Set sourceWorkbook = Nothing
        Set offlineWorksheet = Nothing
        Set sourceWorksheet = Nothing
    End Sub
    If you only ever do what you can , you'll only ever be what you are.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •