Copy cells based on cell value-CodePudding

I have a spreadseet with quite a few refrences (lookups and links to data I extract from system) In cell B1 I have how many actually rows with data I have. i.e The sheet is called Raw Data and if B1=100 I need range B2:E102 copied into sheet Master The value in B1 is dynamic, depending on data in another sheet). Could someone please help me with this? Thanks

CodePudding user response：

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

CodePudding user response：

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 just 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.