Macro to copy specific columns from one workbook to another

[tech2]

I'm trying to create something i.e Macro, that will copy all data in specific columns from 'report1' to 'report2'. The 'paste' should start from the first empty row in 'report2'. The target columns to 'report2' are below.

'report1' 'report2'
columns columns
'A' to 'A'
'B' to 'B'
'G' to 'D'
'I' to 'C'

Thanks for your help

 

[asking]

Try this ~

Sub GenerateReport2()
Application.ScreenUpdating = False
  Sheets("Report1").Select
    Range("A1:A1000,B1:B1000,I1:I1000").Select
      Selection.Copy
Sheets("Report2").Select
  If Range("A1") <> ("") Then
    Range("$A65536").End(xlUp).Offset(1, 0).Select
      Selection.PasteSpecial Paste:=xlPasteValues
        Else
          Range("A1").Select
            Selection.PasteSpecial Paste:=xlPasteValues
  End If
Sheets("Report1").Select
    Range("G1:G1000").Select
      Selection.Copy
        Sheets("Report2").Select
  If Range("D1") <> ("") Then
    Range("$D65536").End(xlUp).Offset(1, 0).Select
      Selection.PasteSpecial Paste:=xlPasteValues
        Else
        Range("D1").Select
           Selection.PasteSpecial Paste:=xlPasteValues
  End If
Sheets("Report1").Select
  Range("A1").Select
    Application.CutCopyMode = False
       Application.ScreenUpdating = True
End Sub

 

[tech2]

Hi Asking,

Thanks for your help but I'm getting a;

Run-time error '9'
Subscript out of range

Also, is this macro copying the columns from workbook 'Report1' to workbook 'Report2'? I defined the 'copy' columns from 'Report1' to the 'paste' columns in 'Report2' below. The sheet names in these two workbooks are the same as the name of the workbooks.

thx

 

[asking]

Hi tech2,

I assumed Report1 and Report2 were Sheets in the same workbook.
If you wish to transfer the Data from Workbook Report1, Sheet Report1, to Workbook Report2, Sheet Report2, you will need to Copy and Paste this revised macro. Deleting previous macro Sub GenerateReport2()
Code error 9 is telling you there is no path to workbook or worksheet so you must get the path right.

The revised macro is actually two macros, The first macro tests to see if workbook Report2 is open, if it is it redirects you to the second macro. If the workbook Report2 is closed, it opens workbook Report2 and then redirects you to the second macro.

This line of code colored red in the macro needs to be changed.
Workbooks.Open Filename:="C:\Users\skinman\Documents\Report2.xlsm"
You need to change to your path, the above is my path. If you don't know the path, Open a new workbook, click on the record macro, then from recent documents select Report2, then stop the macro. Go to the macro just recorded and it will show the correct path. Copy that path, select all the red line and click paste.

You will have to delete the macro Sub GenerateReport2(), it will be replaced by this revised macro of the same name.

Sub GenerateBook()
On Error GoTo OpenWorkBook:
Dim BookName As String
BookName = "Report2"
Workbooks("Report2").Activate
GenerateReport2
Exit Sub

OpenWorkBook:
If Err.Number = 9 Then
[Color=Red]Workbooks.Open Filename:="C:\Users\skinman\Documents\Report2.xlsm"[/Color]
    Resume
End If
GenerateReport2
End Sub

Sub GenerateReport2()
Application.ScreenUpdating = False
Workbooks("Report1").Activate
  Sheets("Report1").Select
    Range("A1:A1000,B1:B1000,I1:I1000").Select
      Selection.Copy
 Workbooks("Report2").Activate
Sheets("Report2").Select
  If Range("A1") <> ("") Then
    Range("$A65536").End(xlUp).Offset(1, 0).Select
      Selection.PasteSpecial Paste:=xlPasteValues
        Else
          Range("A1").Select
            Selection.PasteSpecial Paste:=xlPasteValues
  End If
  Workbooks("Report1").Activate
Sheets("Report1").Select
    Range("G1:G1000").Select
      Selection.Copy
      Workbooks("Report2").Activate
        Sheets("Report2").Select
  If Range("D1") <> ("") Then
    Range("$D65536").End(xlUp).Offset(1, 0).Select
      Selection.PasteSpecial Paste:=xlPasteValues
        Else
        Range("D1").Select
           Selection.PasteSpecial Paste:=xlPasteValues
  End If
  Workbooks("Report1").Activate
Sheets("Report1").Select
  Range("A1").Select
    Application.CutCopyMode = False
       Application.ScreenUpdating = True
End Sub

The macro to run is GenerateBook()
Cheers.

 

[tech2]

ADVERTISEMENT

Thanks Asking,

I'm getting a 'Compile error' Sub or Function not defined.

On the line ( GenerateReport2 )

Thx

 

[asking]

Both the subs have to be in the same module. Are they?
If all your workbooks are open does the Sub Generate report2(), when run independently,
do what you require?
Open the module get the insertion blinker ~ blinking in the Sub Generate report2()
and click run. If there is a problem running that, step through to locate any errors.

I have tested many times and can't get it to fault running both subs from Sub GenerateBook ()

On the line ( GenerateReport2 ) it is only calling the next Sub "GenerateReport2" in the same module. For clarification you could write Call before GenerateReport2 like this ~
Call GenerateReport2
Cheers

 

[tech2]

ADVERTISEMENT

Hi Asking,

1. I don't know what you mean when you state that both subs have to be in the same module.
2. Both workbooks are open but I don't know how to run independently.
3. When I run the macro it gives me the same message as before and will not continue when I click run.
4. I get a 'syntax error' when I use 'Call GenerateReport2'.

I understand if this getting to be to frustrating.

Thanks for your help!

 

[asking]

Hi tech2,

I can understand frustration if things don't work, I have been there.
First have you got excel 2007? if you have this should work as it works fine in mine.

Lets run through what needs to be done.

1. Open workbook Report1 (make it the only workbook open)
2. If you now go to developer menu, click on it, now click on Visual Basic icon on the far left. when that is open it should show you module icons, Now double clicking on those icons will bring the macro to the front associated with the icon. One of those icons should have the Macro exactly as I posted on April the 5th which has 2 Subs in it.
If this is not the case you need to copy and paste the complete 2 sub macros as per post ~ April 5th into a new module, click insert from the VBA menu, click Module. Paste the macro.
Have you changed the code in red writing as I instructed? If you haven't do that.

Now open Report 2, and if you put the above macro in that workbook, delete it.

Now to test while both workbooks are open, select workbook Report1, go to the module that the macro is in. Anywhere after the code line ~
Sub GenerateReport2()
Click so that the insertion indicator is blinking. Now click the little green triangle icon so that just that sub runs.

If that does not give you the answers you require, step through that sub by doing the same insertion icon blinking as per previous paragraph. To step through, go to the View menu, click on toolbars, click debug, (It may be already open, if it has a tick next to it it is open) now on the right of the 'hand' icon is your step through icon, by clicking on that it takes you to the first line of code, click again goes to second line of code etc...
If there is a problem in the code it will tell you by highlighting the offending code.
If that happens let me know.

If all going well, now run the macro ~
Sub GenerateBook()

Your compile error may have come up because you did not change the red line of code to your path to workbook Report2. The path currently in the red line is my path that I used to test that the macro worked properly.

Good luck.

 

[tech2]

Hi Asking,

I'm using excel 2003.

I followed all of the istructions from your last reply including changing the path for my workbooks, and I continue to get the 'Compile error' Sub or Function not defined, warning message.

It will not allow me to step through each line because the error warning continues to display.

The first line of the code is hi-lited in yellow when it gives this error, which is: 'Sub Generate Book()'

Thx

 

[B.brazilian]

I have a very similar problem that I am trying to figure a way out of it.

I work for a company where we have to sen form to different branches and they fill out those excel forms and email back to us.
Then I have to go thru every form and copy the data and paste into an another workbook here at the corp office.

The fields are the same the format is basically the same, therefore it is a very boring and time consuming task of copying and pasting from one workbook to another.

So i was wondering if it would be possible to create a macro using VBA coding to get the data from the workbooks that we get back on the emails to the main one here at the office?

Thanks in advance.
BR