Copying Data to Another Workbook

[jmelinda]

Hello all. I have two files. One is dailyreport.xls and one is monthlyreport.xls and they are in the same folder. I want to create a macro that will copy the data in row A on dailyreport.xls into row B on monthlyreport.xls once a month when I run the code. Both workbooks can be open or closed at the time the macro is run.

I have another macro that I cobbled together from another forum posting to paste from one sheet to another, but I don't know how to make it paste into a different workbook. Here is what I have in the code for the daily report:

Set i = Sheets("March")
Set e = Sheets("Inspection")

Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("A" & j))

If i.Range("A" & j) <> "" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1
Loop

End Sub

Can i use this and change set e = to something to specify the new workbook, or is there another better way to do it?

Thanks!

 

[John Davis]

Hello all. I have two files. One is dailyreport.xls and one is monthlyreport.xls and they are in the same folder. I want to create a macro that will copy the data in row A on dailyreport.xls into row B on monthlyreport.xls once a month when I run the code. Both workbooks can be open or closed at the time the macro is run.

I have another macro that I cobbled together from another forum posting to paste from one sheet to another, but I don't know how to make it paste into a different workbook. Here is what I have in the code for the daily report:

Set i = Sheets("March")
Set e = Sheets("Inspection")

Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("A" & j))

If i.Range("A" & j) <> "" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1
Loop

End Sub

Can i use this and change set e = to something to specify the new workbook, or is there another better way to do it?

Thanks!

Not sure about what you mean by Row A and Row B. With monthlyreport already open maybe this is what you're after?

Set e = Workbooks("monthlyreport.xls").Sheets("Inspection")

 

[jmelinda]

Oh, I meant "Column" A. The formula I was using was actually copying the entire row, but I just need to copy the information in Column A on dailyreport.xls to Column B on monthlyreport.xls, both beginning with Row #2, as long as there is data in Column A. What would be the best way to do this?

 

[John Davis]

Oh, I meant "Column" A. The formula I was using was actually copying the entire row, but I just need to copy the information in Column A on dailyreport.xls to Column B on monthlyreport.xls, both beginning with Row #2, as long as there is data in Column A. What would be the best way to do this?

Do you mean something like this? With both workbooks open.

Sub jmelinda()
Dim lr As Long
Dim lr2 As Long

lr = Workbooks("dailyreport.xls").Sheets("March").Cells(Rows.Count, 1).End(xlUp).Row

lr2 = Workbooks("monthlyreport.xls").Sheets("Inspection").Cells(Rows.Count, 2).End(xlUp).Row + 1

Workbooks("dailyreport.xls").Sheets("March").Range("A2:A" & lr).Copy Workbooks("monthlyreport.xls").Sheets("Inspection").Range("B2")

End Sub

 

[jmelinda]

Yes! That worked perfectly.

There's another part which I would like to do if possible too.

On Dailyreport.xls, Column B is a date that the unit in Column A emptied. It could be any date from the past year. Is there a way to copy this data to monthlyreport.xls in Column C, where if the date is greater than or equal to the 1st of the month (3/1/11), then 3/1/11 is put in the column. If it is after 3/1/11, then the date that is listed is put in the column.

So:

A B
101 1/10/11
102 2/28/11
103 3/12/11

Would copy to monthlyreport.xls as:

B C
101 3/1/11
102 3/1/11
103 3/12/11



Thank you!

 

[John Davis]

Yes! That worked perfectly.

There's another part which I would like to do if possible too.

On Dailyreport.xls, Column B is a date that the unit in Column A emptied. It could be any date from the past year. Is there a way to copy this data to monthlyreport.xls in Column C, where if the date is greater than or equal to the 1st of the month (3/1/11), then 3/1/11 is put in the column. If it is after 3/1/11, then the date that is listed is put in the column.

So:

A B
101 1/10/11
102 2/28/11
103 3/12/11

Would copy to monthlyreport.xls as:

B C
101 3/1/11
102 3/1/11
103 3/12/11



Thank you!

Doe this modification help?

Sub jmelinda2()
Dim lr As Long

lr = Workbooks("dailyreport.xls").Sheets("March").Cells(Rows.Count, 1).End(xlUp).Row


Workbooks("dailyreport.xls").Sheets("March").Range("A2:B" & lr).Copy Workbooks("monthlyreport.xls").Sheets("Inspection").Range("B2")

Workbooks("monthlyreport.xls").Activate

Dim lr2 As Long
Dim Xdate As Date
Dim cl As Range

lr2 = Workbooks("monthlyreport.xls").Sheets("Inspection").Cells(Rows.Count, 2).End(xlUp).Row


Xdate = #3/1/2011#

            For Each cl In Workbooks("monthlyreport.xls").Sheets("Inspection").Range("C2:C" & lr2)

                If cl <= Xdate Then cl = Xdate
                    
                If cl > Xdate Then cl = cl
    
           Next

End Sub

 

[jmelinda]

Thank you very much that is exactly what I needed.

Just for future reference if I decide to copy over more data, what would I change to copy over non-adjacent columns? For example, if I wanted to then copy column G in dailyreport.xls to Column D in monthlyreport.xls.

I changed this part:
Sheets.Range("A2:B" & lr2) to Sheets.Range("A2:G" & lr2) but it copied all of the columns to monthlyreport.xls. I only want to copy A, B and G.

Thank you!

 

[John Davis]

Thank you very much that is exactly what I needed.

Just for future reference if I decide to copy over more data, what would I change to copy over non-adjacent columns? For example, if I wanted to then copy column G in dailyreport.xls to Column D in monthlyreport.xls.

I changed this part:
Sheets.Range("A2:B" & lr2) to Sheets.Range("A2:G" & lr2) but it copied all of the columns to monthlyreport.xls. I only want to copy A, B and G.

Thank you!

You're are welcome.

After this line:

Workbooks("dailyreport.xls").Sheets("March").Range("A2:B" & lr).Copy Workbooks("monthlyreport.xls").Sheets("Inspection").Range("B2")

Add this line:

Workbooks("dailyreport.xls").Sheets("March").Range("G2:G" & lr).Copy Workbooks("monthlyreport.xls").Sheets("Inspection").Range("G2")

If you want to change the destination Column for example in the line right above instead of

.Range("G2") you would use .Range("D2") ect.