0

I am trying to copy all column data from column range A:AU of one excel to another excel in same range of column A:AU in which i have written the code .

Both excel are placed at different locations in my computer and i have written my vba code in the excel where i want to paste data, not in the excel from where i am copying the data.

name of excel from where i am copying the data is "EXRData_08.01.2018.xlsx" and sheet name is "EXR_extract_EX"

& the name of excel where i want to paste the data and where i have written my vba code is "UnattendedData.xlsm" and sheet name is "RawData" .

I am trying to copy entire column content from range A:AU from first excel and paste it into same range of A:AU in another excel where code is present but i am getting error and it is not running . kindly help me regarding this.

below is my code-

Sub panos() Dim r1 As Range, r2 As Range, N As Long Workbooks.Open "\01_Tool\Data\EXRData_08.01.2018.xlsx" N = Sheets("EXR_extract_EX").Cells(Rows.Count, "A:AU").End(xlUp).Row Set r1 = Sheets("EXR_extract_EX").Range("A:AU" & N) Workbooks.Open "_Master\Saurabh\UnattendedData.xlsm" Set r2 = Sheets("RawData").Range("A:AU") r1.Copy r2 End Sub
Improve this question
3
  • What error do you get and in which line? Commented Jan 10, 2018 at 8:22
  • i got error here onwards- N = Sheets("EXR_extract_EX").Cells(Rows.Count, "A:AU").End(xlUp).Row Set r1 = Sheets("EXR_extract_EX").Range("A:AU" & N) --object defined error. @Peh Commented Jan 10, 2018 at 8:23
  • Range("A:AU" & N) is not a valid range it should be either Range("A:AU") for the full column or something like Range("A" & N & ":AU" & N). I also recommend to use the full file path in Workbooks.Open and specify the workbook for Sheets otherwise you will run into issues. Also the UnattendedData.xlsm doesn't need to be opened, because this is already opened as you run the code from this file as you wrote. Commented Jan 10, 2018 at 8:26

1 Answer 1

Reset to default
1
  1. You don'n need to open UnattendedData.xlsm. If this is the workbook you run the code from it is already opened.
  2. Use the full path for Workbooks.Open otherwise you will run into issues.
  3. Use Worksheets instead of Sheets whenever possible. Sheets also contains charts etc.
  4. If you use Worksheets or Sheets always specify the workbook like wb.Worksheets(…). Otherwise Excel guesses a workbook and might fail.

The actual issue in your code is that Range("A:AU" & N) is no valid range. E.g. for N=15 this results in "A:AU15" which is invalid. It should be either "A15:AU15" or "A:AU".

I doubt there is a big advantage in performance using one or the other if the rest of the column is empty anyway, so I would just copy the whole columns.

Example:

Option Explicit 'make sure all variables have to be declared correctly Public Sub CopyRanges() Dim wb As Workbook Set wb = Workbooks.Open("C:\YOUR_FULL_PATH_HERE\01_Tool\Data\EXRData_08.01.2018.xlsx") Dim shtSource As Worksheet Set shtSource = wb.Worksheets("EXR_extract_EX") '^-- Always specify a wb for a sheet Dim shtDestination As Worksheet Set shtDestination = ThisWorkbook.Worksheets("RawData") '^-- here we specify the workbook we are running the code in 'copy the ranges shtSource.Range("A:AU").Copy shtDestination.Range("A:AU") wb.Close SaveChanges:=False 'don't forget to close the workbook End Sub

Note that if you need the path relative to the path of ThisWorkbook you can use something like

ThisWorkbook.Path & "\01_Tool\Data\EXRData_08.01.2018.xlsx"

If it is in the same base path but different sub folders you could walk backwards with \..\ to get out of the subfolders like 

ThisWorkbook.Path & "\..\..\01_Tool\Data\EXRData_08.01.2018.xlsx"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.