Results 1 to 6 of 6

Thread: VBA to copy range to different workbook

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to copy range to different workbook

    Hi!

    I have an issue with my new workbook.
    I'd like to copy data from my active workbook to another one, which is closed.
    I have half success.
    The sheet stands from two parts. An info part and a table. I can copy all the data from the info part but not the table.
    I use the range method to copy single cells. But I cannot do the same with a range.
    I need to set the range variables but I don't know how...
    Can somebody compliment or convert my code to a working one?

    Thank you.

    I use this code to the table:

    Code:
    Sub SaveDim()
    
    Dim M1 As Range
    Dim M2 As Range
    Dim M3 As Range
    Dim M4 As Range
    Dim M5 As Range
    Dim M6 As Range
    Dim M7 As Range
    Dim M8 As Range
    Dim ID As Range
    Dim mydata As Workbook
    
    
    Worksheets("Data").Select
    M1 = Range("B4:B54")
    Worksheets("Data").Select
    M2 = Range("C4:C54")
    Worksheets("Data").Select
    M3 = Range("D4:D54")
    Worksheets("Data").Select
    M4 = Range("E4:E54")
    Worksheets("Data").Select
    M5 = Range("F4:F54")
    Worksheets("Data").Select
    M6 = Range("G4:G54")
    Worksheets("Data").Select
    M7 = Range("H4:H54")
    Worksheets("Data").Select
    M8 = Range("I4:I54")
    Worksheets("Data").Select
    ID = Range("J4:J54")
    
    
    Set mydata = Workbooks.Open("Path and name")
    Worksheets("Sheet1").Select
    Worksheets("Sheet1").Range("A1").Select
    RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
    
    
    With Worksheets("Sheet1").Range("A1")
        
        .Offset(RowCount, 0) = M1
        .Offset(RowCount, 1) = M2
        .Offset(RowCount, 2) = M3
        .Offset(RowCount, 3) = M4
        .Offset(RowCount, 4) = M5
        .Offset(RowCount, 5) = M6
        .Offset(RowCount, 6) = M7
        .Offset(RowCount, 7) = M8
        .Offset(RowCount, 8) = ID
    
    
    End With
    
    
    mydata.Save
    mydata.Close
    
    
    End Sub

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,034
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to copy range to different workbook

    Hi,
    welcome to Forum

    See if this update to your code does what you want

    Code:
    Sub SaveDim()
        Dim RowCount As Long
        Dim CopyRange As Range
        Dim mydata As Workbook
        
        Set CopyRange = Worksheets("Data").Range("B4:J54")
        
        Application.ScreenUpdating = False
        Set mydata = Workbooks.Open("Path and name")
        
         With mydata.Worksheets("Sheet1")
            RowCount = .Range("A1").CurrentRegion.Rows.Count + 1
               CopyRange.Copy .Cells(RowCount, 1)
         End With
    'close and save
        mydata.Close True
        Application.ScreenUpdating = True
    End Sub
    You will need to specify a valid FilePath & FileName shown in red.

    Dave

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy range to different workbook

    Hi Dave!

    Thanks for the fast reply.


    Almost good, but there are some bugs.
    Maybe because I forgot to write all of the cicumstances. My mistake.


    I'd like to copy the values only because the table has a conditional formating.
    Another when the table isn't filled fully, data are just from B4 to J36. The code doesn't start filling the new table from the last empty row. It starts from the A55.

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,034
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to copy range to different workbook

    Quote Originally Posted by Rolsu View Post
    Hi Dave!

    Thanks for the fast reply.


    Almost good, but there are some bugs.
    Maybe because I forgot to write all of the cicumstances. My mistake.
    No bugs just changes to needed to suggestion - Always good to fully explain requirements.

    I'd like to copy the values only because the table has a conditional formating.
    Another when the table isn't filled fully, data are just from B4 to J36. The code doesn't start filling the new table from the last empty row. It starts from the A55.
    If you are copying to a table then, untested but try this update

    Code:
    Sub SaveDim()
        Dim RowCount As Long
        Dim CopyRange As Range
        Dim mydata As Workbook
        
        Set CopyRange = Worksheets("Data").Range("B4:J54")
        
        Application.ScreenUpdating = False
        Set mydata = Workbooks.Open("Path and name")
        
        With mydata.Worksheets("Sheet1")
            RowCount = .Cells.Find(What:="*", _
            SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, _
            LookIn:=xlValues).Row + 1
            CopyRange.Copy
    'paste values only
            .Cells(RowCount, 1).PasteSpecial Paste:=xlPasteValues
        End With
    'close and save
        mydata.Close True
        With Application
            .ScreenUpdating = True: .CutCopyMode = False
        End With
    End Sub
    Dave
    Last edited by dmt32; Jul 17th, 2019 at 02:50 AM.

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy range to different workbook

    Hi Dave!

    It works perfectly!
    Thank you so much and sorry again about the missing information.

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,034
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to copy range to different workbook

    Quote Originally Posted by Rolsu View Post
    Hi Dave!

    It works perfectly!
    Thank you so much and sorry again about the missing information.
    No worries just something to keep in mind next time posting - glad update does what you want

    Dave

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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