Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
I need this to look at column "A" Column "B" etc... and copy column "A" just
the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
This line:
Set myRange = Range("B1", Range("B1").End(xlDown)) is refering to the new sheet in the new workbook. range("B1").end(xldown) goes all the way to B65536. .Offset(1) hurts excel's head--it runs out of rows! maybe... Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range dim ActSheet as worksheet dim newSheet as worksheet with actSheet Set myRange = .Range("A1", .Range("A1").End(xlDown)) end with Workbooks.Add set newwks = activeworkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ destination:=newwks.Range("C1") with actsheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) end with myRange.Copy _ destination:=newwks.Range("C65536").End(xlUp).Offs et(1) End Sub jbsand1001 wrote: I need this to look at column "A" Column "B" etc... and copy column "A" just the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
David,
I ran your then information that you sent but It still tells me that my copy and paste area are not the same.... SO I tweaked it a little to the following. Now it seems to want to copy and paste ok but now it wants to copy the following from the new worksheet. "With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With" I think we are close??? Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet With ActSheet Set myRange = Range("A1", Range("A1").End(xlDown)) End With Workbooks.Add Set newwks = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newwks.Range("C1") With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With myRange.Copy Destination = newwks.Range("C65536").End(xlUp).Offset(1) End Sub "jbsand1001" wrote: I need this to look at column "A" Column "B" etc... and copy column "A" just the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
There are a couple of blocks of code like this:
With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With You need to include the dots your "Set" statement. With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With The dots mean that the ranges you're referring to belong to the previous with object (ActSheet) in this case. Without the dots, then With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With Refer to the current activesheet--not the sheet that was active when you started the procedure. jbsand1001 wrote: David, I ran your then information that you sent but It still tells me that my copy and paste area are not the same.... SO I tweaked it a little to the following. Now it seems to want to copy and paste ok but now it wants to copy the following from the new worksheet. "With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With" I think we are close??? Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet With ActSheet Set myRange = Range("A1", Range("A1").End(xlDown)) End With Workbooks.Add Set newwks = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newwks.Range("C1") With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With myRange.Copy Destination = newwks.Range("C65536").End(xlUp).Offset(1) End Sub "jbsand1001" wrote: I need this to look at column "A" Column "B" etc... and copy column "A" just the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
When I run this with the dots in my statement
Set myRange = .Range("A1", .Range("A1").End(xlDown)) I get a runtime error '91' "object variable or with block variable not set" Should I just copy and paste your entire program into my VBA field? Thank You, Judd "Dave Peterson" wrote: There are a couple of blocks of code like this: With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With You need to include the dots your "Set" statement. With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With The dots mean that the ranges you're referring to belong to the previous with object (ActSheet) in this case. Without the dots, then With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With Refer to the current activesheet--not the sheet that was active when you started the procedure. jbsand1001 wrote: David, I ran your then information that you sent but It still tells me that my copy and paste area are not the same.... SO I tweaked it a little to the following. Now it seems to want to copy and paste ok but now it wants to copy the following from the new worksheet. "With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With" I think we are close??? Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet With ActSheet Set myRange = Range("A1", Range("A1").End(xlDown)) End With Workbooks.Add Set newwks = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newwks.Range("C1") With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With myRange.Copy Destination = newwks.Range("C65536").End(xlUp).Offset(1) End Sub "jbsand1001" wrote: I need this to look at column "A" Column "B" etc... and copy column "A" just the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
You can either copy and paste that other version into your module.
Test it and see what happens. But to get any kind of informed guess, I think you'll have to post your existing code. jbsand1001 wrote: When I run this with the dots in my statement Set myRange = .Range("A1", .Range("A1").End(xlDown)) I get a runtime error '91' "object variable or with block variable not set" Should I just copy and paste your entire program into my VBA field? Thank You, Judd "Dave Peterson" wrote: There are a couple of blocks of code like this: With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With You need to include the dots your "Set" statement. With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With The dots mean that the ranges you're referring to belong to the previous with object (ActSheet) in this case. Without the dots, then With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With Refer to the current activesheet--not the sheet that was active when you started the procedure. jbsand1001 wrote: David, I ran your then information that you sent but It still tells me that my copy and paste area are not the same.... SO I tweaked it a little to the following. Now it seems to want to copy and paste ok but now it wants to copy the following from the new worksheet. "With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With" I think we are close??? Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet With ActSheet Set myRange = Range("A1", Range("A1").End(xlDown)) End With Workbooks.Add Set newwks = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newwks.Range("C1") With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With myRange.Copy Destination = newwks.Range("C65536").End(xlUp).Offset(1) End Sub "jbsand1001" wrote: I need this to look at column "A" Column "B" etc... and copy column "A" just the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
Oops. I see that I made a mistake in my post. In fact, I had a few
typos--sorry. I added the "set actsheet = activesheet" and changed newwks to newsheet. Option Explicit Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet Set ActSheet = ActiveSheet With ActSheet Set myRange = .Range("A1", .Range("A1").End(xlDown)) End With Workbooks.Add Set newSheet = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newSheet.Range("C1") With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With myRange.Copy _ Destination:=newSheet.Range("C65536").End(xlUp).Of fset(1) End Sub jbsand1001 wrote: This is the code I am using to get the following error '91' object variable or with block variable not set Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range dim ActSheet as worksheet dim newSheet as worksheet with actSheet Set myRange = .Range("A1", .Range("A1").End(xlDown)) end with Workbooks.Add set newwks = activeworkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ destination:=newwks.Range("C1") with actsheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) end with myRange.Copy _ destination:=newwks.Range("C65536").End(xlUp).Offs et(1) End Sub "Dave Peterson" wrote: You can either copy and paste that other version into your module. Test it and see what happens. But to get any kind of informed guess, I think you'll have to post your existing code. jbsand1001 wrote: When I run this with the dots in my statement Set myRange = .Range("A1", .Range("A1").End(xlDown)) I get a runtime error '91' "object variable or with block variable not set" Should I just copy and paste your entire program into my VBA field? Thank You, Judd "Dave Peterson" wrote: There are a couple of blocks of code like this: With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With You need to include the dots your "Set" statement. With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With The dots mean that the ranges you're referring to belong to the previous with object (ActSheet) in this case. Without the dots, then With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With Refer to the current activesheet--not the sheet that was active when you started the procedure. jbsand1001 wrote: David, I ran your then information that you sent but It still tells me that my copy and paste area are not the same.... SO I tweaked it a little to the following. Now it seems to want to copy and paste ok but now it wants to copy the following from the new worksheet. "With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With" I think we are close??? Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet With ActSheet Set myRange = Range("A1", Range("A1").End(xlDown)) End With Workbooks.Add Set newwks = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newwks.Range("C1") With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With myRange.Copy Destination = newwks.Range("C65536").End(xlUp).Offset(1) End Sub "jbsand1001" wrote: I need this to look at column "A" Column "B" etc... and copy column "A" just the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
Excellent Thanks Dave!!!
"Dave Peterson" wrote: Oops. I see that I made a mistake in my post. In fact, I had a few typos--sorry. I added the "set actsheet = activesheet" and changed newwks to newsheet. Option Explicit Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet Set ActSheet = ActiveSheet With ActSheet Set myRange = .Range("A1", .Range("A1").End(xlDown)) End With Workbooks.Add Set newSheet = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newSheet.Range("C1") With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With myRange.Copy _ Destination:=newSheet.Range("C65536").End(xlUp).Of fset(1) End Sub jbsand1001 wrote: This is the code I am using to get the following error '91' object variable or with block variable not set Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range dim ActSheet as worksheet dim newSheet as worksheet with actSheet Set myRange = .Range("A1", .Range("A1").End(xlDown)) end with Workbooks.Add set newwks = activeworkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ destination:=newwks.Range("C1") with actsheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) end with myRange.Copy _ destination:=newwks.Range("C65536").End(xlUp).Offs et(1) End Sub "Dave Peterson" wrote: You can either copy and paste that other version into your module. Test it and see what happens. But to get any kind of informed guess, I think you'll have to post your existing code. jbsand1001 wrote: When I run this with the dots in my statement Set myRange = .Range("A1", .Range("A1").End(xlDown)) I get a runtime error '91' "object variable or with block variable not set" Should I just copy and paste your entire program into my VBA field? Thank You, Judd "Dave Peterson" wrote: There are a couple of blocks of code like this: With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With You need to include the dots your "Set" statement. With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With The dots mean that the ranges you're referring to belong to the previous with object (ActSheet) in this case. Without the dots, then With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With Refer to the current activesheet--not the sheet that was active when you started the procedure. jbsand1001 wrote: David, I ran your then information that you sent but It still tells me that my copy and paste area are not the same.... SO I tweaked it a little to the following. Now it seems to want to copy and paste ok but now it wants to copy the following from the new worksheet. "With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With" I think we are close??? Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet With ActSheet Set myRange = Range("A1", Range("A1").End(xlDown)) End With Workbooks.Add Set newwks = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newwks.Range("C1") With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With myRange.Copy Destination = newwks.Range("C65536").End(xlUp).Offset(1) End Sub "jbsand1001" wrote: I need this to look at column "A" Column "B" etc... and copy column "A" just the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Synchronize workbooks
Glad it's working--and sorry about that original post with the errors!
jbsand1001 wrote: Excellent Thanks Dave!!! "Dave Peterson" wrote: Oops. I see that I made a mistake in my post. In fact, I had a few typos--sorry. I added the "set actsheet = activesheet" and changed newwks to newsheet. Option Explicit Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet Set ActSheet = ActiveSheet With ActSheet Set myRange = .Range("A1", .Range("A1").End(xlDown)) End With Workbooks.Add Set newSheet = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newSheet.Range("C1") With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With myRange.Copy _ Destination:=newSheet.Range("C65536").End(xlUp).Of fset(1) End Sub jbsand1001 wrote: This is the code I am using to get the following error '91' object variable or with block variable not set Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range dim ActSheet as worksheet dim newSheet as worksheet with actSheet Set myRange = .Range("A1", .Range("A1").End(xlDown)) end with Workbooks.Add set newwks = activeworkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ destination:=newwks.Range("C1") with actsheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) end with myRange.Copy _ destination:=newwks.Range("C65536").End(xlUp).Offs et(1) End Sub "Dave Peterson" wrote: You can either copy and paste that other version into your module. Test it and see what happens. But to get any kind of informed guess, I think you'll have to post your existing code. jbsand1001 wrote: When I run this with the dots in my statement Set myRange = .Range("A1", .Range("A1").End(xlDown)) I get a runtime error '91' "object variable or with block variable not set" Should I just copy and paste your entire program into my VBA field? Thank You, Judd "Dave Peterson" wrote: There are a couple of blocks of code like this: With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With You need to include the dots your "Set" statement. With ActSheet Set myRange = .Range("B1", .Range("B1").End(xlDown)) End With The dots mean that the ranges you're referring to belong to the previous with object (ActSheet) in this case. Without the dots, then With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With Refer to the current activesheet--not the sheet that was active when you started the procedure. jbsand1001 wrote: David, I ran your then information that you sent but It still tells me that my copy and paste area are not the same.... SO I tweaked it a little to the following. Now it seems to want to copy and paste ok but now it wants to copy the following from the new worksheet. "With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With" I think we are close??? Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Dim ActSheet As Worksheet Dim newSheet As Worksheet With ActSheet Set myRange = Range("A1", Range("A1").End(xlDown)) End With Workbooks.Add Set newwks = ActiveWorkbook.Sheets("Sheet1") '<-- in the new workbook myRange.Copy _ Destination:=newwks.Range("C1") With ActSheet Set myRange = Range("B1", Range("B1").End(xlDown)) End With myRange.Copy Destination = newwks.Range("C65536").End(xlUp).Offset(1) End Sub "jbsand1001" wrote: I need this to look at column "A" Column "B" etc... and copy column "A" just the data that is in column "A" and paste to Column "C" and do the same for column "B" . This data will be added and subtracted to daily so it must know where the last item in column "A" is and add it to column "C" on the new workbook then copy the data in column "B" and add to the new workbook where the data from column "A" left off. Ok... This is what I have come up with. Sub Macro1() 'If each column has a heading, change the 1's to 2's Dim myRange As Range Set myRange = Range("A1", Range("A1").End(xlDown)) Workbooks.Add Sheets("Sheet1").Activate myRange.Copy Range("C1") Set myRange = Range("B1", Range("B1").End(xlDown)) myRange.Copy Range("C65536").End(xlUp).Offset(1) End Sub This so far is doing what I need it to do by copying column "A" and opening a new workbook and adding to colum "C" in this new workbook, and I think it is trying to copy "B" but I keep receiving the following message "Run-time error 1004 the information cannot be pasted because the copy and paster area are not the same size and shape" I think this is close??? Judd -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
synchronize between sheets | Excel Worksheet Functions | |||
synchronize multiple worksheets | Excel Worksheet Functions | |||
Macro to Synchronize data frm svrl workbooks & columns to 1 workbo | Excel Discussion (Misc queries) | |||
Synchronize pivot tables | Excel Programming | |||
synchronize worksheet in a workbook | Excel Programming |