VB Code to copy content of external worksheet to named worksheet in this workbook

[tommill52]

I am looking for some VB code for the following.
I need to copy all of the data (values only) from worksheet 'extworksheet' in workbook 'extworkbook.xlsx' to worksheet 'localworksheet' in this (active) workbook. Literally replicate the 'extworksheet' into worksheet 'localworksheet' from cell $A$1 on. The size of the 'extworksheet' can vary in rows and columns on a day to day basis, so I need something that copies all of the populated rows (ie row 1 to last row).
Can anyone help with this please? I have tried various code snippets I have found but they all come up with errors. I am using Office 365 (Excel 2016).

 

[MrJoosten]

Hello tommill,

This should work for you.

Sub CopyPasteAll()

Dim lastRow As Long
Dim firstRow As Long
Dim lastColumn As Long
Dim firstColumn As Long

'delete current content in sheet 2
sheets("localworksheet").activate
cells.select
selection.delete

Sheets("extworksheet").Activate

'get last row of the data
lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'use 1 if data starts in column 1, 2 for b etc..

'set first row of data
firstRow = 1

'set first column of data
firstColumn = 1

'get last column of data
lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column 'use 1 if data starts in first row, 2 for second etc.. 

'Copy the data
Range(Cells(fr, fc), Cells(lr, lc)).Delete

'activate sheet 2 & paste values
sheets("localworksheet").activate
cells(1,1).PasteSpecial xlPasteValues

end sub

Tell me if this works for you

 

[tommill52]

Thanks for your reply. Am I missing something (not a VB expert), I can see how you activate the external worksheet, but where is the external workbook selected?

 

[MrJoosten]

I thought you had 2 sheets in 1 workbook.

If you have 2 workbooks, open the other with something like this:

'Open excelfile
Workbooks.Open Filename:= "your path/extworksheet.xlsx" , UpdateLinks:=0

I use the following code to activate files (I'm also still learning ) :

Windows("extworksheet.xlsx").Activate

Hope you can tweak the macro with this info.

Let us know if you need any more help.

 

[tommill52]

I thought you had 2 sheets in 1 workbook.

If you have 2 workbooks, open the other with something like this:

'Open excelfile
Workbooks.Open Filename:= "your path/extworksheet.xlsx" , UpdateLinks:=0

I use the following code to activate files (I'm also still learning ) :

Windows("extworksheet.xlsx").Activate

Hope you can tweak the macro with this info.

Let us know if you need any more help.

Thanks for your help. I was testing your code and having a few problems with it (my fault, I think) when somebody else provided me with a set of working.