CODEWITHSUNDEEP
excelvba
Philippa Fenner
Philippa Fenner

Reputation: 3

Macro to copy variable number of rows with fixed columns

I have searched high and low on the internet to find an answer to my macro problem-I don't think what I am trying to so is that difficult, but maybe it is!

In my dataset column K contains my data markers. What I want to do is find "22" in column K and find "23" is column K and copy all the rows of data in columns C to J that fall between the row numbers indicated by the markers contained in column K. The number of rows between markers 22 and 23 is different for each spreadsheet, which is where I am running into problems. I've tried to set the range for these variables but I am basically a macro novice and am not having much luck. Any help greatly appreciated.

Upvotes: 0

Views: 1441

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

Probably the easiest way would just find 22 & 23 and establish where the rows are located

Then you can create the range to copy with those known rows and columns. Name a sheet "Copy to Sheet" for this example.

Sub Button1_Click()
    Dim Rws As Long, Rng As Range
    Dim c1 As String, c2 As String
    Dim FndC1 As Range, FndC2 As Range
    Dim c1C As Integer, c2C As Integer
    Dim ws As Worksheet
    Set ws = Sheets("Copy to Sheet")
    c1 = 22
    c2 = 23
    Rws = Cells(Rows.Count, "K").End(xlUp).Row
    Set Rng = Range(Cells(1, "K"), Cells(Rws, "K"))

    Set FndC1 = Rng.Find(what:=c1, lookat:=xlWhole)
    Set FndC2 = Rng.Find(what:=c2, lookat:=xlWhole)

    If Not FndC1 Is Nothing Then
        c1C = FndC1.Row
    Else: MsgBox c1 & " Not Found"
        Exit Sub
    End If
    If Not FndC2 Is Nothing Then
        c2C = FndC2.Row
    Else: MsgBox c2 & " Not Found"
        Exit Sub
    End If
    Range(Cells(c1C + 1, "C"), Cells(c2C - 1, "J")).Copy _
            ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)


End Sub

Upvotes: 2

Related Questions