Hi All
I am trying to write a Macro that will allow me to copy all data in Sheet 2,
in the range A6:F6 down to the last line of data, which changes in length
each day.
I then want to clear all cells in range B12:G12 down to the last row, which
changes each day, on Sheet1 and paste the data from Sheet2.
Any help suggestions would be greatly appreciated.
Thanks in Advance
Michael M

Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
lrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set destrng = Sheet1.Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Sheet1.Range("B12")
End Sub

Mike F
"Michael M" wrote in message
...
Hi All
I am trying to write a Macro that will allow me to copy all data in Sheet
2,
in the range A6:F6 down to the last line of data, which changes in length
each day.
I then want to clear all cells in range B12:G12 down to the last row,
which
changes each day, on Sheet1 and paste the data from Sheet2.
Any help suggestions would be greatly appreciated.
Thanks in Advance
Michael M

Mike
Thanks for a quick and simple response.
I will give it a try ASAP

Regards
Michael M

"Mike Fogleman" wrote:

Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
lrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set destrng = Sheet1.Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Sheet1.Range("B12")
End Sub

Mike F
"Michael M" wrote in message
...
Hi All
I am trying to write a Macro that will allow me to copy all data in Sheet
2,
in the range A6:F6 down to the last line of data, which changes in length
each day.
I then want to clear all cells in range B12:G12 down to the last row,
which
changes each day, on Sheet1 and paste the data from Sheet2.
Any help suggestions would be greatly appreciated.
Thanks in Advance
Michael M

Hi Mike
Finally got time to apply the code, but unfortunately it didn't work.
Sheet1 ends up Blank except for row 17 which has 4 cells of data which have
no connection with sheet 2 whatsoever.
I will try and "understand" the code a little better to see what it is
doing, but any help would be appreciated.

Regards
Michael M

"Mike Fogleman" wrote:

Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
lrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set destrng = Sheet1.Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Sheet1.Range("B12")
End Sub

Mike F
"Michael M" wrote in message
...
Hi All
I am trying to write a Macro that will allow me to copy all data in Sheet
2,
in the range A6:F6 down to the last line of data, which changes in length
each day.
I then want to clear all cells in range B12:G12 down to the last row,
which
changes each day, on Sheet1 and paste the data from Sheet2.
Any help suggestions would be greatly appreciated.
Thanks in Advance
Michael M

In the VB Editor, look at the list of sheets in the left pane that are in
your workbook. You will see sheets with a number and the tab name in
parentheses:
Sheet1(Sheet 1)
Sheet2(Sheet 2)
Sheet3(Sheet 3's Tab Name)

My code is using the left name as the sheets to copy from and paste to. If
Sheet1 & 2 do not match the tab names of the sheets you are wanting to use
(tab name), then change them in my code to match or substitute with the
sheets' tab name in the code.

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
would become:
lrow = Worksheets("Data Source").Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Worksheets("Data Source").Range("A6:F" & lrow)
Etc for sheet1.

Mike F
"Michael M" wrote in message
...
Hi Mike
Finally got time to apply the code, but unfortunately it didn't work.
Sheet1 ends up Blank except for row 17 which has 4 cells of data which
have
no connection with sheet 2 whatsoever.
I will try and "understand" the code a little better to see what it is
doing, but any help would be appreciated.

Regards
Michael M

"Mike Fogleman" wrote:

Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
lrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set destrng = Sheet1.Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Sheet1.Range("B12")
End Sub

Mike F
"Michael M" wrote in message
...
Hi All
I am trying to write a Macro that will allow me to copy all data in
Sheet
2,
in the range A6:F6 down to the last line of data, which changes in
length
each day.
I then want to clear all cells in range B12:G12 down to the last row,
which
changes each day, on Sheet1 and paste the data from Sheet2.
Any help suggestions would be greatly appreciated.
Thanks in Advance
Michael M

Thanks again Mike
I did as you said.....I had left the brackets off !!!
But it still doesn't work. I'm going to put a couple of MsgBoxes after each
line to track down my error. The error might lie in my description of the
request !!
I'll keep you posted.

Regards
Michael M

"Mike Fogleman" wrote:

In the VB Editor, look at the list of sheets in the left pane that are in
your workbook. You will see sheets with a number and the tab name in
parentheses:
Sheet1(Sheet 1)
Sheet2(Sheet 2)
Sheet3(Sheet 3's Tab Name)

My code is using the left name as the sheets to copy from and paste to. If
Sheet1 & 2 do not match the tab names of the sheets you are wanting to use
(tab name), then change them in my code to match or substitute with the
sheets' tab name in the code.

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
would become:
lrow = Worksheets("Data Source").Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Worksheets("Data Source").Range("A6:F" & lrow)
Etc for sheet1.

Mike F
"Michael M" wrote in message
...
Hi Mike
Finally got time to apply the code, but unfortunately it didn't work.
Sheet1 ends up Blank except for row 17 which has 4 cells of data which
have
no connection with sheet 2 whatsoever.
I will try and "understand" the code a little better to see what it is
doing, but any help would be appreciated.

Regards
Michael M

"Mike Fogleman" wrote:

Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
lrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set destrng = Sheet1.Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Sheet1.Range("B12")
End Sub

Mike F
"Michael M" wrote in message
...
Hi All
I am trying to write a Macro that will allow me to copy all data in
Sheet
2,
in the range A6:F6 down to the last line of data, which changes in
length
each day.
I then want to clear all cells in range B12:G12 down to the last row,
which
changes each day, on Sheet1 and paste the data from Sheet2.
Any help suggestions would be greatly appreciated.
Thanks in Advance
Michael M

Hi again Mike
I don't know what caused the problem, but I narrowed it down to the last
line !!
I changed all of the Sheet names to their respective "proper
names".......and all is well in the world.
Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Worksheets("PLMS").Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Worksheets("PLMS").Range("A6:F" & lrow)
lrow = Worksheets("PRICE CULVERT OPTION").Cells(Rows.Count, "B").End(xlUp).Row
Set destrng = Worksheets("PRICE CULVERT OPTION").Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Worksheets("PRICE CULVERT OPTION").Range("B12")
End Sub

Thank you again for your input and assistance.
Regards
Michael M

"Mike Fogleman" wrote:

In the VB Editor, look at the list of sheets in the left pane that are in
your workbook. You will see sheets with a number and the tab name in
parentheses:
Sheet1(Sheet 1)
Sheet2(Sheet 2)
Sheet3(Sheet 3's Tab Name)

My code is using the left name as the sheets to copy from and paste to. If
Sheet1 & 2 do not match the tab names of the sheets you are wanting to use
(tab name), then change them in my code to match or substitute with the
sheets' tab name in the code.

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
would become:
lrow = Worksheets("Data Source").Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Worksheets("Data Source").Range("A6:F" & lrow)
Etc for sheet1.

Mike F
"Michael M" wrote in message
...
Hi Mike
Finally got time to apply the code, but unfortunately it didn't work.
Sheet1 ends up Blank except for row 17 which has 4 cells of data which
have
no connection with sheet 2 whatsoever.
I will try and "understand" the code a little better to see what it is
doing, but any help would be appreciated.

Regards
Michael M

"Mike Fogleman" wrote:

Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
lrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set destrng = Sheet1.Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Sheet1.Range("B12")
End Sub

Mike F
"Michael M" wrote in message
...
Hi All
I am trying to write a Macro that will allow me to copy all data in
Sheet
2,
in the range A6:F6 down to the last line of data, which changes in
length
each day.
I then want to clear all cells in range B12:G12 down to the last row,
which
changes each day, on Sheet1 and paste the data from Sheet2.
Any help suggestions would be greatly appreciated.
Thanks in Advance
Michael M

Glad to hear you got it working. I've been pulling double shifts 3 times a
week so I haven't checked in here very much.
Good job on working through your issue.
Mike F
"Michael M" wrote in message
...
Hi again Mike
I don't know what caused the problem, but I narrowed it down to the last
line !!
I changed all of the Sheet names to their respective "proper
names".......and all is well in the world.
Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Worksheets("PLMS").Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Worksheets("PLMS").Range("A6:F" & lrow)
lrow = Worksheets("PRICE CULVERT OPTION").Cells(Rows.Count,
"B").End(xlUp).Row
Set destrng = Worksheets("PRICE CULVERT OPTION").Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Worksheets("PRICE CULVERT OPTION").Range("B12")
End Sub

Thank you again for your input and assistance.
Regards
Michael M

"Mike Fogleman" wrote:

In the VB Editor, look at the list of sheets in the left pane that are in
your workbook. You will see sheets with a number and the tab name in
parentheses:
Sheet1(Sheet 1)
Sheet2(Sheet 2)
Sheet3(Sheet 3's Tab Name)

My code is using the left name as the sheets to copy from and paste to.
If
Sheet1 & 2 do not match the tab names of the sheets you are wanting to
use
(tab name), then change them in my code to match or substitute with the
sheets' tab name in the code.

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
would become:
lrow = Worksheets("Data Source").Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Worksheets("Data Source").Range("A6:F" & lrow)
Etc for sheet1.

Mike F
"Michael M" wrote in message
...
Hi Mike
Finally got time to apply the code, but unfortunately it didn't work.
Sheet1 ends up Blank except for row 17 which has 4 cells of data which
have
no connection with sheet 2 whatsoever.
I will try and "understand" the code a little better to see what it is
doing, but any help would be appreciated.

Regards
Michael M

"Mike Fogleman" wrote:

Sub ClearCopy()
Dim srcrng As Range, destrng As Range
Dim lrow As Long

lrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Set srcrng = Sheet2.Range("A6:F" & lrow)
lrow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
Set destrng = Sheet1.Range("B12:G" & lrow)
destrng.Clear
srcrng.Copy Sheet1.Range("B12")
End Sub

Mike F
"Michael M" wrote in message
...
Hi All
I am trying to write a Macro that will allow me to copy all data in
Sheet
2,
in the range A6:F6 down to the last line of data, which changes in
length
each day.
I then want to clear all cells in range B12:G12 down to the last
row,
which
changes each day, on Sheet1 and paste the data from Sheet2.
Any help suggestions would be greatly appreciated.
Thanks in Advance
Michael M