Copying columns from workbooks - making it dynamic

[TheRedCardinal]

I need to do the following steps:

- In the current workbook, find 2 columns in the sheet called Temp
- Copy the contents of those columns
- Paste them into a sheet called Raw Data, at the bottom of two columns with specific names

I had this working "perfectly" when I knew the columns locations, but they are not always the same and so I had to make it dynamic.

I came up with this - sorry you'll probably find this highly inefficient and some of the old code is in there too

'Copy over the 2 columns to bottom of Raw Data


        Dim Range1 As Range
        Dim Range2 As Range
        Dim Range3 As Range
        Dim Range4 As Range
        
        Set WS1 = WBk1.Worksheets("1. Raw Data")
        Set WS2 = WBk1.Worksheets("Temp")
        
        
        With WS1
        
            Set Range3 = Range("A1:Z1").Find("Invoice")
            
        End With
            
        
        With WS2
            
            LRowTemp = .Cells(Rows.Count, 1).End(xlUp).Row
            Set Range1 = Range("A1:Z1").Find("Voucher")
            Set Range2 = Range("A1:Z1").Find("Origin")
            
            If Range1 Is Nothing Then
                MsgBox "There was an error importing the Data - Please check your source file"
                Call TurnOn
                Exit Sub
            End If
            
            If Range2 Is Nothing Then
                MsgBox "There was an error importing the Data - Please check your source file"
                Call TurnOn
                Exit Sub
            End If
            
            Range(Range1, Range1.End(xlDown)).Copy WS1.Range("A" & LRow)
            Range(Range2, Range2.End(xlDown)).Copy WS1.Range(Range3, Range3.End(xlDown))
            
            
        End With

There is a problem with the Final line - which spits out a Object Variable or With Block Variable not set, which I think is because I set Range3 inside a With WS2 section, and now that has ended.

So now I'm lost as to how to correctly set the ranges.
The other issues I have are:

- I don't want to copy over Row 1 from Temp Sheet but can't see how to eliminate it if I've used row 1 to find the header
- Range 2 above gets pasted into the next column after the final one, starting at the last row (Variable LRow) - but how do I do this, using an offset?

Thanks!

 

[mumps]

Try:

Sub CopyCols()
    Dim Range1 As Range, Range2 As Range, Range3 As Range, WS1 As Worksheets, WS2 As Worksheet, lRowTemp As Long
    Set WS1 = Sheets("1. Raw Data")
    Set WS2 = Sheets("Temp")
    Set Range3 = WS1.Range("A1:Z1").Find("Invoice")
    With WS2
        lRowTemp = .Cells(Rows.Count, 1).End(xlUp).Row
        Set Range1 = .Range("A1:Z1").Find("Voucher")
        Set Range2 = .Range("A1:Z1").Find("Origin")
        If Range1 Is Nothing Then
            MsgBox "There was an error importing the Data - Please check your source file"
            Call TurnOn
            Exit Sub
        End If
        If Range2 Is Nothing Then
            MsgBox "There was an error importing the Data - Please check your source file"
            Call TurnOn
            Exit Sub
        End If
        .Range(.Cells(2, Range1.Column), .Cells(lRowTemp, Range1.Column)).Copy WS1.Cells(WS1.Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range(.Cells(2, Range2.Column), .Cells(lRowTemp, Range2.Column)).Copy WS1.Cells(2, Range3.Column)
    End With
End Sub

 

[TheRedCardinal]

Hi - not quite working!

At the start of the routine, Raw Data has data in columns A:M and down to rows 227.

I think your suggestion has missed the difference between LRow (which is defined previously in the routine) and LRowTemp which is only used to find the LRow on the Temp sheet. I think I can fix this quite easily.

The main problem is though that it has copied Range 2 also into the Column A (staring at A1) when it should go into Column N, starting at row LRow.

I realise now looking at my code that it did NOT do that anyway. So my error.

But the process should be:

- look in Temp Sheet
- find "Invoice"
- copy column (ideally leaving out header Invoice)
- copy to the bottom of the column in Raw Data called Voucher

- look in Temp Sheet
- find "Origin"
- copy column (ideally leaving out header "Origin")
- copy into the Raw Data sheet, starting at the same row (LRow) as Invoice, but in one column further to the right than the final column
- put a header "Actual Value" in Row 1 of that new final column

I'll have a go at playing with your formula to achieve this but any help welcomed!

 

[mumps]

In your original macro you didn't define "LRow". Can you explain in detail what you mean by "starting at the same row (LRow) as Invoice"? It would be easier to follow if you could post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[TheRedCardinal]

Yes sorry that was my bad.
The Sub is not an exclusive one - the steps I was taking were part of a bigger sub process.
Earlier in the process I had Dim'd LRow and defined it as the the final Row in WS1, which is the "Raw Data" sheet.
I created LRowTemp specifically for this process.
My mistake for not realising I had included a Variable in my above code that I had defined and set elsewhere.

Anyway I have uploaded the file here:

https://drive.google.com/file/d/17Va92Q0PDrZEi-K_4VIOwm170IEYEyU1/view?usp=sharing

The steps are in highlighted boxes.

You'll see my code is very wayward so any comments you have would be great on that too.

Thanks!

 

[mumps]

Click here to download you file. I have tidied up your code a bit so try all your macros out in this file to make sure they still work properly. The revised "CopyCols" macro is in Module1.

 

[TheRedCardinal]

Hi,
Thanks for this.
I ran and it didn't quite go to plan.
The copying of Voucher to Invoice went fine.
But Origin to VAT Value posted it into the wrong row.

So I changed this:

.Range(.Cells(2, origin.Column), .Cells(lRowTemp, origin.Column)).Copy WS1.Cells(lRowRaw, lCol)

To this:

.Range(.Cells(2, origin.Column), .Cells(lRowTemp, origin.Column)).Copy WS1.Cells(lRowRaw + 1, lCol)

By adding 1 to the LRowRaw it moved it down one row.

Any issue with such a simple suggestion?

Thanks!

 

[mumps]

Could you please upload a copy of the file which shows what the end result should look like?

 

[TheRedCardinal]

I can later, but in the meantime basic summary is that Invoice has been copied into the range starting A228 but Origin has been copied into the range starting A227 so they don't line up.

 

[mumps]

Replace

WS1.Cells(lRowRaw, lCol)

with

WS1.Cells(lRowRaw + 1, lCol)