CODEWITHSUNDEEP
excelvba
user2383423
user2383423

Reputation:

Copy cells based on cell value

I have a spreadsheet with quite a few references (lookups and links to data I extract from system).
In cell B1 I have how many rows with data.

For example:
The sheet is called Raw Data
If B1=100 I need range B2:E102 copied into sheet Master

The value in B1 is dynamic, depending on data in another sheet.

Upvotes: 1

Views: 87

Answers (2)

JD413
JD413

Reputation: 1

I like to create a hard coded starting point for the Range.

Then from there step into getting the last column then the last row.

Something like this:

Sub Main()

strFile = "C:\Users\raw_data.xlsm" 'change this to your file name
Workbooks.Open (strFile)
'Debug.Print strFile

'log the last column for paramters
LastColumn = ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

'log the last rows for components
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

Dim WorksheetStartCell As String

StartCellNum = 2

WorksheetHeadingStartCell = "A" & StartCellNum
WorksheetValueStartCell = "A" & StartCellNum + 1

'Debug.Print "Worksheet Heading Start Cell: " & WorksheetHeadingStartCell
'Debug.Print "Worksheet Value Start Cell: " & WorksheetValueStartCell

WorksheetHeadingEndCell = "G" & StartCellNum
WorksheetValueEndCell = "G" & LastRow

End Sub

That will dynamically give you everything aside from it finding the very first cell of the range, which is easy enough but like you said, you had it hard coded to "B2" so I thought it was relevant.

Once you have the range that you actually want like written above, you can easily copy and paste the range to that sheet.

I assumed you needed more help with the dynamic range than the copying and pasting of the range, if that was not the case, I can reply again to that concern.

I have not replied to many answers on here so sorry if I was not much help. I did try my best to address your concerns. I truly wish this helps you.

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54923

Copy a Range

Option Explicit

Sub CopyRange()
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the Source range.
    Dim sws As Worksheet: Set sws = wb.Sheets("Raw Data")
    Dim slRow As Long: slRow = sws.Range("B1").Value + 2
    Dim srg As Range: Set srg = sws.Range("B2", sws.Cells(slRow, "E"))
    
    ' Reference the first Destination cell.
    Dim dws As Worksheet: Set dws = wb.Sheets("Master")
    Dim dfCell As Range: Set dfCell = dws.Range("A1") ' adjust!?
    
    ' Either copy values, formulas and formats,...
    srg.Copy dfCell
    ' or copy only values (more efficient):
    'dfCell.Resize(srg.Rows.Count, srg.Columns.Count).Value = srg.Value
    
    ' Inform.
    MsgBox "Range copied.", vbInformation
       
End Sub

Upvotes: 0

Related Questions