Copy and paste from an excel worksheet to another worksheet of a different workbook

sanilmathews

Board Regular
Joined
Jun 28, 2011
Messages
102
Hi All,

I would need some help in getting VBA code for copy and pasting a tabular data (range varies - excluding headers in row 1) in a worksheet named "comm log" of a different workbook to the master workbook (macro enabled). Would like to pick the file using msoFileDialogFolderPicker.

Code would enable user to copy paste worksheets at any point of time which will have the tabular data in a tab named "comm log". However for each instance the code should find the last updated line in the master workbook and start pasting it in the next available blank row (excluding headers).

Thanks in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Question:
1) The files you are copying from will have multiple sheets and one of the sheet named comm log?
2) You will be copying from row 2 to end of row? Copy all columns or specific non-contiguous columns?

Probably you can show how the Master template would be and how the source data sheet looks like.
 
Upvote 0
Q1. Yes, the files that I am copying will have multiple sheets and all these workbook will have a sheet named "comm log".
Q2. Yes, I will be copying from row 2 to end of the row. For columns the range would be specified (Col A - Col Y).

Attached the snapshot of the two files for your reference with dummy information.
 

Attachments

  • SavedFiles.jpg
    SavedFiles.jpg
    125.2 KB · Views: 9
  • Master File.jpg
    Master File.jpg
    117.6 KB · Views: 10
Upvote 0
Try this code with description on steps taken (not tested). The macro is to be installed in Master workbook

VBA Code:
Sub ImportData()

Dim eRowMaster As Long, eRowSource As Long
Dim Fname As Variant
Dim wsMaster As Worksheet, wsSource As Worksheet
Dim wbMaster As Workbook, wbSource As Workbook

Application.ScreenUpdating = False

' Define this Workbook as Master workbook
Set wbMaster = ActiveWorkbook
' Define comm log sheet in wbMaster as Master sheet
Set wsMaster = wbMaster.Sheets("comm log")

' Search for Source Workbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked

' Define opened Workbook as source workbook while opening it.
Set wbSource = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
' Define comm log sheet in Source workbook as Source sheet
Set wsSource = wbSource.Sheets("comm log")

' Search for last data row in wsSource
eRowSource = wsSource.Range("A" & wsSource.Rows.Count).End(xlUp).Row

' Search for next empty row wsMaster
eRowMaster = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1

' Copy data from wsSource to wsMaster
wsSource.Range("A2", "Y" & eRowSource).Copy wsMaster.Range("A" & eRowMaster)

End Sub
 
Upvote 0
This works perfectly fine. I just wanted to close the wsSource after copy. So I have added wsSource.Close.

Thank you so much for your help
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top