CODEWITHSUNDEEP
excelexcel-formula
trebleCode
trebleCode

Reputation: 2308

Copying Dynamic Range Between Excel Worksheets

I have an Excel sheet that contains some AD and server information on one sheet, and a separate sheet that summarizes some of that information.

One the AD/server sheet, the server information does not always start in the same cell in Column A, but does always have a cell directly above it (effectively a label) that contains the same value.

On the summary sheet, the range value where I want to copy/paste the dynamic range from the first sheet always starts in the same cell.

So assuming I have X amount of servers per-document listed under the label that is consistent, how can I dynamically reference those values on the Summary sheet using a formula? I'm not an Excel guru, but have tried playing with the RANGE function and haven't had much success.

The goal here is to add a little bit of automation to a template file.

Ex.

Sheet1
Col1
some
varying
levels
of
information
CONSISTENTLABEL
Server1
Server2
Server3

Sheet2
Column1
LABEL1
LABEL2
LABEL3
LABEL4
Blank Cell (starting point where I want to copy from Sheet1)

Any tips from the Excel jedi's out there would be greatly appreciated!

Upvotes: 0

Views: 254

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Assuming your start point is in column A, and the label you are referring to is a unique label, or appears first at the point you want to start copying you can use something like this:

(note that this is vague code as you haven't supplied the sheet names etc...) 

Sub MacroMan()

Const searchLabel As String = "LABEL 4" '// Change as required

With Sheets("Server Info") '// Change as required
    .Range("A" & WorksheetFunction.Match(searchLabel, .Range("A:A"), 0) + 1), _
        .Cells(.Rows.Count, 1).End(xlUp)).Copy Destination:= _
    Sheets("Summary Data").Range("A1") '// Change as required (paste destination)
End With

End Sub

This is using the Match() function to find the row number with your header in, then adding 1 to that (as we want the cell after that) and appending it to "A" to create a cell reference for the starting point of the range. The end cell for the copy range is determined by simply getting the last cell with data in column A (I have made an assumption that you want to copy everything below the header).

Now that we have the copy range, we simply supply the destination argument - this bypasses the clipboard and makes for faster processing/execution.

Upvotes: 1

Related Questions