Macro to copy cells from Worksheet to another Worsheet

slohman · Oct 1, 2012

I need a Macro that will copy Worksheet (Estimate1) I have 3 columns of data in Estimate1 in BY, BZ and CA to copy to Worksheet (Cost) it is to start at C1 then offset (skip) to M1, then skip down to next row. But as you can see it is not working properly.

This is the code I have so far

[pre]

Code:

Dim SheetName As String
SheetName = &#34;Estimate1&#34;
SheetName = InputBox(&#34;enter the name of a sheet to use&#34;, &#34;sheet name&#34;, SheetName)
Dim i As Long
Dim MyCol As Integer
Dim MyRow As Integer
ActiveSheet.Range(&#34;E1:W17&#34;).ClearContents 

LR = Sheets(SheetName).Range(&#34;BY&#34; & Rows.Count).End(xlUp).Row
MyCol = 3
MyRow = 1
For i = 13 To 66
If Sheets(SheetName).Range(&#34;BY&#34; & i).Value &#60;&#62; &#34;&#34; Then
Sheets(&#34;Cost&#34;).Cells(MyRow, MyCol).Value = Sheets(SheetName).Range(&#34;BY&#34; & i).Value
MyCol = 3
MyRow = MyRow + 1
End If
If MyRow = 18 Then
MsgBox &#34;You have ran out of room.  Some entries were not copied&#34;
Exit For
End If
Next i
LR = Sheets(SheetName).Range(&#34;BZ&#34; & Rows.Count).End(xlUp).Row
MyCol = 5
MyRow = 1
For i = 13 To 66
If Sheets(SheetName).Range(&#34;BY&#34; & i).Value &#60;&#62; &#34;&#34; Then
Sheets(&#34;Cost&#34;).Cells(MyRow, MyCol).Value = Sheets(SheetName).Range(&#34;BZ&#34; & i).Value
MyCol = 5
MyRow = MyRow + 1
End If
If MyRow = 18 Then
MsgBox &#34;You have ran out of room.  Some entries were not copied&#34;
Exit For
End If
Next i
LR = Sheets(SheetName).Range(&#34;CA&#34; & Rows.Count).End(xlUp).Row
MyCol = 7
MyRow = 1
For i = 13 To 66
If Sheets(SheetName).Range(&#34;BY&#34; & i).Value &#60;&#62; &#34;&#34; Then
Sheets(&#34;Cost&#34;).Cells(MyRow, MyCol).Value = Sheets(SheetName).Range(&#34;CA&#34; & i).Value
MyCol = 7
MyRow = MyRow + 1
End If
If MyRow = 18 Then
MsgBox &#34;You have ran out of room.  Some entries were not copied&#34;
Exit For
End If
Next i
End Sub

[/pre]

If you like these VB formatting tags please consider sponsoring the author in support

NARAYANK991 · Oct 1, 2012

Hi ,

If we can keep aside the code you have posted , can you clarify what is your exact requirement ?

As I understand it ,

1. You have two worksheets , one labelled Estimate1 , and the other labelled Cost. Estimate1 is the source sheet , and Cost is the destination sheet.

2. The source data is in columns BY , BZ and CA.

3. The destination range starts with C1 , then skips to M1 , and then ? You mention 3 columns for the source , but for the destination you have mentioned only 2 ; what about the third ?

The references to 13 , 66 , 18 etc. in the posted code are not clear ; are these additional requirements which you specified to the author of the code ?

If you can give precise specifications , it will be easier to write the entire code afresh , or at least understand the posted code so that it can be modified.

Narayan

sdsurzh · Oct 1, 2012

Hi,

The following code will copy the entire data in columns BY to CA then it will paste in cost sheet from C to E

Sub copydata()

Worksheets("estimate1").Select

Columns("by:ca").Select

Selection.Copy

Worksheets("cost").Select

Range("c1").Select

Selection.PasteSpecial xlPasteAll

Application.CutCopyMode = False

Range("c1").Select

End Sub

If the above is not perfect can you please upload the file or copy paste the data in estimate1 sheet.

Thanks,

Suresh Kumar S

sdsurzh · Oct 1, 2012

Sorry Excel Ninja NarayanK,

Just now i have noticed your comment.

Thanks,

Suresh Kumar S

slohman · Oct 1, 2012

This is my first post how do I upload a file

sdsurzh · Oct 1, 2012

Hi slohman,

please visit the below link:

http://chandoo.org/forums/topic/posting-a-sample-workbook

Thanks,

Suresh Kumar S

slohman · Oct 1, 2012

http://www.2shared.com/file/hztRN8wj/BLANK_WORKS_COST_-_SJ_11JUN_20.html

sdsurzh · Oct 1, 2012

Hi slohman,

Please download the file from the below link.

http://www.2shared.com/file/xPw8rn7t/BLANK_WORKS_COST_-_SJ_11JUN_20.html

(As per my understanding i have created the file please let me know if i am wrong)

Thanks,

Suresh Kumar S

slohman · Oct 1, 2012

http://www.2shared.com/file/Wo3xRgsQ/BLANK_WORKS_COST_-_SJ_11JUN_20.html

If the macro works properly it should look like this. I have to sets of columns like a table.

Thanks for any help greatly appreciated.

slohman · Oct 2, 2012

Can anyone help with this??

SirJB7 · Oct 2, 2012

Hi, slohman!

In VBA code section of worksheet Cost, CommandButton1_Click event procedure, try this:

- change the original statement

MyCol = 5

by

MyCol = 7

- change the original statement

MyCol = 7

by

MyCol = 9

Regards!

slohman · Oct 2, 2012

Thanks for everyone's help but I must not be explaining myself property I have Estimate1 which has 3 columns BY:CA which has values in it.

I have a worksheet which i have attached called Cost which I would like to copy if Estimate1 has value in BY:CA copy into C1 (Cost) G1 (Cost) I1 (Cost)then move across and keep grabbing next value from BY:CA to M1 (Cost) Q1 (Cost) V1 (Cost) then to skip to next row and continue till all have copied from BY:CA.

The other way was to fill C1:C17, G1:G17, I1:I17 then loop back up to M1, Q1 and V1 etc and start down that column

SirJB7 · Oct 2, 2012

Hi, slohman!

I followed my own instructions and got this file, which it seems to be working. Please download it, run the code associated to blue button "UPDATE SHEET" and check out the results.

https://dl.dropbox.com/u/60558749/Macro%20to%20copy%20cells%20from%20Worksheet%20to%20another%20Worksheet%20-%20BLANK%20WORKS%20COST%20-%20S%26J%2011JUN%202012%20%28for%20slohman%20at%20chandoo.org%29.xlsm

Regards!

slohman · Oct 2, 2012

Ok we are getting somewhere what if C1:C17 is filled with value it doesnt seem to want to go up and start at M1 to continue it just tells me I have run out of room and not copied

If I add to BY:CA and it has more than 17 values I need it to go up to M1 and start again or C1 to M1 then down a row to C2 to M2 and fill G and I and Q and V

SirJB7 · Oct 2, 2012

Hi, slohman!

Are you checking your code with the same sample file you've uploaded? If not or if there are other conditions (number of elements) please upload a new sample file, so as to easily see what your code does. Thank you.

Regards!

slohman · Oct 3, 2012

http://www.2shared.com/file/JgATMvhz/BLANK_WORKS_COST_-_SJ_11JUN_20.html

SirJB7 · Oct 3, 2012

Hi, slohman!

Download the updated file from this link:

https://dl.dropbox.com/u/60558749/Macro%20to%20copy%20cells%20from%20Worksheet%20to%20another%20Worsheet%20-%20BLANK%20WORKS%20COST%20-%20S%26J%2011JUN%202012%20%28for%20slohman%20at%20chandoo.org%29.xlsm

Just advise if any issue.

Regards!

slohman · Oct 3, 2012

You are my new best friend "Genius" thank you so much.

SirJB7 · Oct 3, 2012

Hi, slohman!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

slohman · Oct 3, 2012

Ok I'm back already I had given you a test file but now have tried to use code in real workbook and have a problem I need the values to stop at row 39 as you will see on my download as it is a form that is getting filled out.

http://www.2shared.com/file/CXPJc5eq/BLANK_WORKS_COST_-_SJ_03102012.html

slohman · Oct 3, 2012

I figured it out thanks again for all your help much appreciated.

SirJB7 · Oct 4, 2012

Hi, slohman!

Updated file:

https://dl.dropbox.com/u/60558749/Macro%20to%20copy%20cells%20from%20Worksheet%20to%20another%20Worksheet%20-%20BLANK%20WORKS%20COST%20-%20S%26J%2003102012%20%28for%20slohman%20at%20chandoo.org%29.xlsm

And some humble advises:

a) use name ranges instead do fixed rows and columns

b) you have 2 groups of 3 groups with almost the same code: convert them to loops or subs

I think that this will make easier future maintenance.

Regards!

Macro to copy cells from Worksheet to another Worsheet

Member

Excel Ninja

Member

Member

Member

Member

Member

Member

Member

Member

Excel Rōnin

Member

Excel Rōnin

Member

Excel Rōnin

Member

Excel Rōnin

Member

Excel Rōnin

Member

Member

Excel Rōnin