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!

 

[TheRedCardinal]

That is exactly what I did - I was just checking the solution I put in hadn't stored up issues for me later

Thanks so much for all your help!

 

[mumps]

You are very welcome.