Macro to copy specific rows to another workbook

spill-the-beans

Board Regular
Joined
Feb 7, 2013
Messages
52
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

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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

Well-known Member
Joined
Nov 3, 2008
Messages
581
Hi..

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

Code:
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
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top