Macro to copy specific rows to another workbook

[spill-the-beans]

Hi everyone,

I'm trying to automate my data organisation process, so what I'm looking for is a macro template with the lines of code I need, but for me to change the names of the files. So, what I'm trying to do, is:

1. tell it to look in "workbook 1 name" in "sheet 1 name".

2. copy row 1 and paste only the values to this "workbook 2 name" on "sheet 2 name" in row 1.

3. look back in "workbook 1 name" in "sheet 1 name".

4. copy all the rows that have "this number" in column P, and paste the rows (only the values, not formulas) in the next empty row in "workbook 2 name" on "sheet 2 name"

5. open a text file with "this name".

6. copy the column with "this header" in row 1 and paste the whole column in the next empty column in "workbook 2 name" on "sheet 3 name"

7. save "workbook 2 name" as "this name" (this save will overwrite an existing file, and if it's possible, I don't want excel to ask me if it's okay to save over)


If anyone could help me work this out it would help so much!

 

[dermie_72]

record the functions you want to do, then review the code that way.

You can adjust it to suit future needs that way. The recording will give you a servicable template. The rest is up to you.

 

[apo]

Hi..

Here's some simple code to get you started..

Private Sub CommandButton1_Click()

Dim LastRow As Long
Dim LastRow2 As Long
Dim MyTxtFile
LastRow = Workbooks("Book1.xlsm").Sheets("Sheet1").Range("O" & Rows.Count).End(xlUp).Row

''''''''''''''''''''''''''''
'1. tell it to look in "workbook 1 name" in "sheet 1 name".
'2. copy row 1 and paste only the values to this "workbook 2 name" on "sheet 2 name" in row 1.
''''''''''''''''''''''''''''

ThisWorkbook.Sheets("Sheet1").Range("A1").EntireRow.Copy
Workbooks("Book2.xlsm").Worksheets("Sheet2").Range("A1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


'''''''''''''''''''''''''''''
'3. look back in "workbook 1 name" in "sheet 1 name".
'4. copy all the rows that have this number (1) in column P, and paste the rows (only the values, not formulas) in the next empty row in "workbook 2 name" on "sheet 2 name"
''''''''''''''''''''''''''''
For i = 1 To LastRow
LastRow2 = Workbooks("Book2.xlsm").Worksheets("Sheet2").Range("O" & Rows.Count).End(xlUp).Row

    If Workbooks("Book1.xlsm").Sheets("Sheet1").Cells(i, 16) = "1" Then
    ThisWorkbook.Sheets("Sheet1").Range("A" & i).EntireRow.Copy
    Workbooks("Book2.xlsm").Worksheets("Sheet2").Range("A" & LastRow2 + 1).PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If

Next i

''''''''''''''''''''''''
'5. open a text file with "this name".
''''''''''''''''''''''''

    MyTxtFile = Shell("C:\WINDOWS\notepad.exe C:\ThisName.txt", 1)

End Sub

 

[spill-the-beans]

Thank you! I'm learning so much from this forum, your time is definitely appreciated