Thread: Copy columns from closed workbook to different columns in open workbook

Hi, sorry if this exists already but the search function isnt working for me. Seriously, it gives me a php error everytime I try to search.

Anyway... I found this code to copy columns from a closed workbook to an open workbook and it works great.

'Get LastRow & LastColumn of the sheet
lr = GetLastRow(True, 1, 2, pathname, filename, sheetname)   'lr = LastRow
lc = GetLastCol(True, 5, pathname, filename, sheetname)       'lc = LastColumn

'Get UsedRange for static columns (T onwards)
MnthDataRange = "$T$4:" & Cells(lr, lc).Address

With Sheets(1).Range(MnthDataRange)
            .FormulaR1C1 = "=IF('" & pathname & "[" & filename & "]" & sheetname & "'!RC="""",NA(),'" & pathname & "[" & filename & "]" & sheetname & "'!RC)"
            'Delete all Error cells (i.e. blank cells)
            On Error Resume Next
            .SpecialCells(xlCellTypeFormulas, xlErrors).Clear
            On Error GoTo 0
End With

The only problem is that in the above example I copy everything from T4:Z25 (in the closed workbook) and it pastes it in T4:Z25 in the open workbook. I need to paste it in S4:Y25.

Does anyone know of a method to copy from one range and paste it in different range?

Thanks in advance.

Try this:

'Get LastRow & LastColumn of the sheet
lr = GetLastRow(True, 1, 2, pathname, Filename, sheetname)   'lr = LastRow
lc = GetLastCol(True, 5, pathname, Filename, sheetname)       'lc = LastColumn

'Get UsedRange for static columns (T onwards)
MnthDataRange = "$S$4:" & Cells(lr, lc - 1).Address

With Sheets(1).Range(MnthDataRange)
            .FormulaR1C1 = "=IF('" & pathname & "[" & Filename & "]" & sheetname & "'!RC[+1]="""",NA(),'" & pathname & "[" & Filename & "]" & sheetname & "'!RC[+1])"
            'Delete all Error cells (i.e. blank cells)
            On Error Resume Next
            .SpecialCells(xlCellTypeFormulas, xlErrors).Clear
            On Error GoTo 0
End With

First of, I'd like to apologize for posting my scenario to this thread as it appears to be the closest to my scenario when I searched the forum.

Hello Excel Experts,

I am new, not just to the forum but to VBA as well. While I have been searching online for some VBA code, I believe this scenario mirrors to what I am currently facing.

I have a "Report" workbook (open) with only one worksheet named "Summary". In this worksheet, column A and column B contain the data that needs to be searched across the "Master" workbook (closed) that holds all the data in various worksheets that needs to be copied/pasted. When the search is found from within the various worksheets of "Master" workbook, copy respective rows unto "Summary" worksheet of "Report" workbook (open) in this order:

= D3 (worksheet from Master workbook) to D3 (Summary worksheet of Report workbook)

= E3 (worksheet from Master workbook) to G3 (Summary worksheet of Report workbook)

= F3 (worksheet from Master workbook) to H3 (Summary worksheet of Report workbook)

= G3 (worksheet from Master workbook) to O3 (Summary worksheet of Report workbook)

= H3 (worksheet from Master workbook) to P3 (Summary worksheet of Report workbook)

= I3 (worksheet from Master workbook) to U3 (Summary worksheet of Report workbook)

= J3 (worksheet from Master workbook) to V3 (Summary worksheet of Report workbook)

= K3 (worksheet from Master workbook) to W3 (Summary worksheet of Report workbook)

= L3 (worksheet from Master workbook) to X3 (Summary worksheet of Report workbook)

= M3 (worksheet from Master workbook) to Y3 (Summary worksheet of Report workbook)

= N3 (worksheet from Master workbook) to Z3 (Summary worksheet of Report workbook)

This same process will continue till all matches have been found. Also, if no match is found - skip that particular search and move to the next one.

I hope I have provided you with some useful information, otherwise please let me know. Thank you for your time and patience mate!

Cheers,


JP