Copy data from one WorkBOOK to another WorkBOOK using VBA

[ummer]

Hi,

I have some workbooks where information is held and i need to copy this information over to a master spreadsheet where everything is held.

Would be really appreciated if you could support me in creating the VBA code for this, NOTE: i am a beginner so do not know too much, but i am passionate and willing to learn to get his complete.

Thank you in advance.
Ummer

 

[Fishboy]

Hi,

I have some workbooks where information is held and i need to copy this information over to a master spreadsheet where everything is held.

Would be really appreciated if you could support me in creating the VBA code for this, NOTE: i am a beginner so do not know too much, but i am passionate and willing to learn to get his complete.

Thank you in advance.
Ummer

Hi Ummer, welcome to the boards.

You can try out the following in a COPY of your master spreadsheet. The following code is just added to a standard module (press Alt+F11 to open the VBA developer window, find your workbook in the project pane on the left, right-click and select Insert Module), and can either be applied to a button to be run or you can press Alt+F8 and run it from there.

You will need to update the filepath and filename which I have highlighted in bold red for you:

Sub CopyOtherWorkbook()
' Defines variables
Dim Wb1 As Workbook, Wb2 As Workbook
' Disables screen updating to reduce flicker
Application.ScreenUpdating = False
' Sets Wb1 as the current (destination) workbook
    Set Wb1 = ThisWorkbook
' Sets Wb2 as the defined workbook and opens it - Update filepath / filename as required
    Set Wb2 = Workbooks.Open("C:\TestFolder\Test3.xlsm")
' Sets LastRow as the first blank row of Wb1 Sheet1 based on column A (requires at least header if document is otherwise blank)
        LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
' With workbook 2
            With Wb2
' Activate it
                .Activate
' Activate the desired sheet - Currently set to sheet 1, change the number accordingly
                .Sheets(1).Activate
' Copy the used range of the active sheet
                .ActiveSheet.UsedRange.Copy
            End With
' Then with workbook 1
                With Wb1.Sheets(1)
' Activate it
                    .Activate
' Select the first blank row based on column A
                    .Range("A" & LastRow).Select
' Paste the copied data
                    .Paste
                End With
' Close workbook 2
        Wb2.Close
' Re-enables screen updating
Application.ScreenUpdating = False
End Sub

Ultimately, remember to save your master document as a macro enabled workbook (.xlsm format)

 

[Fishboy]

Hi Ummer, I have tried replying to your PM however your inbox is full and you will need to delete some messages to make room for my reply. Drop me another PM to confirm once you have made some room for new messages.

 

[Fishboy]

Hi,

Sorry to bug you.

I have managed to get the copy and paste function to work via a button. I have come across a problem where the code will select the whole range of data and paste it, however i need to select a dynamic range of data because the other spreadsheets data may be added or removed.

Lets say i want to only copy the first block of data out of 4 blocks and paste that into a specific place, how would i go about copying the data? currently the code copies all the blocks, I want to avoid this.

your help would be very much appreciated.

Ummer

Combining the following useful code which allows you to specify the desired range to be copied...

Sub SelectRange()
Dim StartRng As Variant, EndRng As Variant
StartRng = InputBox("Please enter desired starting range")
EndRng = InputBox("Please enter desired ending range")
Range(StartRng, EndRng).Select
End Sub

...with my original code above...

Sub CopyOtherWorkbook()
' Defines variables
Dim Wb1 As Workbook, Wb2 As Workbook
' Disables screen updating to reduce flicker
Application.ScreenUpdating = False
' Sets Wb1 as the current (destination) workbook
    Set Wb1 = ThisWorkbook
' Sets Wb2 as the defined workbook and opens it - Update filepath / filename as required
    Set Wb2 = Workbooks.Open("C:\TestFolder\Test3.xlsm")
' Sets LastRow as the first blank row of Wb1 Sheet1 based on column A (requires at least header if document is otherwise blank)
        LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
' With workbook 2
            With Wb2
' Activate it
                .Activate
' Activate the desired sheet - Currently set to sheet 1, change the number accordingly
                .Sheets(1).Activate
' Input box to select the first cell in the range to copy from
                    StartRng = InputBox("Please enter desired starting range")
' Input box to select the last cell in the range to copy from
                    EndRng = InputBox("Please enter desired ending range")
' Copy the specified range
                        Range(StartRng, EndRng).Copy
            End With
' Then with workbook 1
                With Wb1.Sheets(1)
' Activate it
                    .Activate
' Select the first blank row based on column A
                    .Range("A" & LastRow).Select
' Paste the copied data
                    .Paste
                End With
' Close workbook 2
        Wb2.Close
' Re-enables screen updating
Application.ScreenUpdating = False
End Sub

...should hopefully do what you need. I have highlighted my additional code in blue. The bold red part will still need changing as per my original post.