Copy-Pasting Data into Columns Dynamically

[ExcelKnight]

Hello all,

I am new to the forum and new to VBA. I am writing a formula to parse through several sheets and workbooks and pull static information from each one, and paste it all into one big database, so later I can work with the data.

Currently I'd like to pull a few data points and add them as columns throughout the Sheet. The first one works, but since the range is not dynamic, the second run does not work. I am not sure how to have the paste be along the new row.

Sub copyData()


    'Copy and Paste Exercise
    'To do: Make sheets dynamic and Workbook dynamic
    Sheets("Data1").Select
    Range("B1").Select
    Selection.Copy
    Sheets("Target Database").Select
    Range("A2:A12").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Data1").Select
    Range("A5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Target Database").Select
    Range("B2:B12").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Data1").Select
    Range("B1").Copy
    Range("A9:G19").Copy
    Sheets("Target Database").Select
    'lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, True).Row
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    
    'Copy Paste Drill
    'Scores are offset and different format, decide on how to deal with
    Sheets("Data1").Select
    Range("A22:G34").Copy
    Sheets("Target Database").Select
    'lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    
    'Copy Paste Water Drill is Offset - Also IF Statement
    
    Sheets("Data1").Select
     Range("A37:G53").Copy
    Sheets("Target Database").Select
    'lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    'selection and paste should be split
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    
End Sub

 

[ExcelKnight]

Attached is what I'd like the code to do. Since I am using a static range, it will only work on the first loop. I am unsure how to also look for the next empty rows and paste them in there. I've tried several different options, but all have failed.

 

[ExcelKnight]

Here is where I am currently at.

Sub copyData()


    'Copy and Paste Exercise
    'To do: Make sheets dynamic and Workbook dynamic
    Sheets("Data1").Select
    Range("B1").Select
    Selection.Copy
    Sheets("Target Database").Select
    Range("A2:A42").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Data1").Select
    Range("A5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Target Database").Select
    Range("B2:B42").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Data1").Select
    Range("B1").Copy
    Range("A9:G19").Copy
    Sheets("Target Database").Select
    'lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, True).Row
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    
    'Copy Paste Drill
    'Scores are offset and different format, decide on how to deal with
    Sheets("Data1").Select
    Range("A22:G34").Copy
    Sheets("Target Database").Select
    'lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    
    'Copy Paste Water Drill is Offset - Also IF Statement
    
    Sheets("Data1").Select
     Range("A37:G53").Copy
    Sheets("Target Database").Select
    'lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    'selection and paste should be split
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    
End Sub

 

[ExcelKnight]

Another attempt, I have it pasting the location in the last column, now how can I have it paste this range into the whole empty column?

Sub copyData()
Dim lr As String

    'Copy and Paste Exercise
    'To do: Make sheets dynamic and Workbook dynamic


    Sheets("Data1").Select
    Range("A9:G19").Copy
    Sheets("Target Database").Select
    lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, True).Row
    Range("C" & lr).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        

    
    'Copy Paste Drill
    'Scores are offset and different format, decide on how to deal with
    Sheets("Data1").Select
    Range("A22:G34").Copy
    Sheets("Target Database").Select
    'lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    Range("C" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
       xlNone, SkipBlanks:=False, Transpose:=False

    lrTarget = Cells.Find("*", Cells(1, 2), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    Sheets("Data1").Select
    Range("B1").Copy
    Sheets("Target Database").Select
    Range("B" & lrTarget).Select
     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    
    'Copy Paste Water Drill is Offset - Also IF Statement
    
    'Sheets("Data1").Select
    ' Range("A37:G53").Copy
    'Sheets("Target Database").Select
    'lrTarget = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row
    'Range("C" & Rows.Count).End(xlUp).Offset(-22).Select
    'selection and paste should be split
    'Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
       ' xlNone, SkipBlanks:=False, Transpose:=False

    
End Sub